ACC2000: How to Implement Query-By-Form in a Microsoft Access Project

• Microsoft Access 2000

SUMMARY

This article shows you how to use a form to specify the criteria for a query in a Microsoft Access project.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

http://www.microsoft.com/support/supportnet/overview/overview.asp

Creating the Stored Procedure

1. Open the sample Access project NorthwindCS.adp, which by default is located in the \Program Files\Microsoft Office\Office\Samples folder.



2. In NorthwindCS.adp, create a new stored procedure and type the following TSQL statements in the Stored Procedure Designer:




CREATE Procedure "QBFProc"
@CustomerID varchar(10), @EmployeeId int, @OrderDate datetime
As
Declare @SQLString  varchar(1000)
Declare @SelectList varchar(100)

SET NOCOUNT ON

SELECT @SelectList = 'SELECT * FROM Orders'

--Check to see if CustomerID search criteria is NULL.
--If it contains a value, Begin to construct a WHERE clause.
IF @CustomerId Is NOT NULL  
	BEGIN
		SELECT @SQLString = 'WHERE CustomerID = ''' + @CustomerId + ''''
	END

--Check to see if EmployeeID  search criteria is NULL. 
--If it contains a value, add additional information to
--the WHERE clause.  
	
IF @EmployeeID Is NOT NULL
	BEGIN
	          IF @SQLSTRING Is NOT NULL 
		  BEGIN
			SELECT @SQLSTRING = @SQLSTRING + 
                           ' AND EmployeeID = ' + Convert(varchar(100), @EmployeeID) 
		  END
	           ELSE 
		  BEGIN
			SELECT @SQLSTRING = 'WHERE EmployeeID = ' +
                           Convert(varchar(100), @EmployeeID) 

		  END
	END

--Check to see if OrderDate search criteria is NULL. 
--If it contains a value, add additional information to
--the WHERE clause.  

IF @OrderDate Is NOT NULL
	BEGIN
 		IF @SQLSTRING Is NOT NULL 
		  BEGIN
			SELECT @SQLSTRING = @SQLSTRING +
                           ' AND OrderDate = ''' + Convert(varchar(20), @OrderDate) + ''''
		  END
	           ELSE 
		  BEGIN
			 SELECT @SQLSTRING = 'WHERE OrderDate = ''' +
                            Convert(varchar(20), @OrderDate) + ''''
		  END
	END

--Concantinate the SELECT list and WHERE clause together.

SELECT @SelectList = @SelectList + ' ' +  @SQLString

--Execute the result 

EXECUTE(@SELECTLIST) 

3. Save the stored procedure and close it.

Creating the Forms

1. In the Stored Procedures list of NorthwindCS.adp, click to select QBFProc.



2. On the Insert menu, click AutoForm. When prompted to enter a parameter, click Cancel.



3. Save the form that the AutoForm wizard created as frmFinal, and then close it.



4. Create another new form named QBF_Form not based on any table or query.



5. Make sure that the Control Wizards button is not pressed in in the tool box, and then add the following controls to the form:




  ComboBox
  ------------------------------------------------------------
  Name: cboCustomerID
  Row Source Type: Table/View/StoredProc
  Row Source: SELECT "Customers"."CustomerID" FROM "Customers"


  ComboBox
  ---------------------------------------------------------------------
  Name: cboEmployeeID
  Row Source Type: Table/View/StoredProc
  Row Source: SELECT "Customers"."CustomerID", "Customers"."EmployeeID"  
              FROM "Customers"
  Column Count: 2
  Column Widths: 0;1
  Bound Column: 2


  ComboBox
  --------------------------------------------------------------------
  Name: cboOrderDate
  Row Source Type: Table/View/StoredProc
  Row Source: SELECT "Customers"."CustomerID", "Customers"."OrderDate"                 
              FROM "Customers"
  Column Count: 2
  Column Widths: 0;1
  Bound Column: 2 

6. In the toolbox, click Control Wizards so that the button appears pressed in to turn on the Control Wizards.



7. Add a command button to the form, and then follow the Command Button Control Wizard steps to have the command button open the frmFinal form when clicked.



8. Save the form and close it.

Modifying the frmFinal Form

1. Open the frmFinal form in Design view.



2. Set the Input Parameters property to the following value:




@CustomerID varchar(10)=forms!QBF_Form!cboCustomerID, @EmployeeID
varchar(15) = forms!QBF_Form!cboEmployeeID,
@OrderDate varchar(20) = forms!QBF_Form!cboOrderDate

3. Set the DefaultView and ViewsAllowed property of the frmFinal form to Datasheet



4. Save and close the frmFinal form.

REFERENCES

For more information about the InputParameters property, click Microsoft Access Help on the Help menu, type New Properties in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about Transact-SQL (TSQL) and creating stored procedures with input parameters, refer to SQL Server 7.0 Books Online, which is available for download from the following Microsoft Web site:

http://support.microsoft.com/download/support/mslfiles/sqlbol.exe

Additional query words:

Keywords          : kbdta AccCon AccessCS 
Version           : WINDOWS:2000
Platform          : WINDOWS 
Issue type        : kbhowto 

Last Reviewed: August 9, 1999