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

ID: Q235359

The information in this article applies to:

Microsoft Access 2000

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access project (.adp).

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/

For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

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

In an Access database (.mdb), you can use the query-by-form technique to create a "query form" in which you can enter query criteria. The query form contains blank text boxes or combo boxes, each representing a field in your Access table that you want to query. You make entries in only the boxes for which you want to specify search criteria.

You can also implement the query-by-form technique in an Access project. In an Access project, you would use a stored procedure to find the records that you wanted to view, and then you would create an additional form to display the output of the stored procedure.

Creating the Stored Procedure

You'll use the following stored procedure to return data to one of your forms.

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

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)

Save the stored procedure and close it.

Creating the Forms

Follow these steps to create two forms in NorthwindCS.adp. You'll use the QBF_Form to select the search criteria that will be used by the stored procedure (QBFProc) that you created earlier. You'll use the frmFinal form to display the results returned by the QBFProc stored procedure.

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

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

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

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

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

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

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.

Save the form and close it.

Modifying the frmFinal Form

Follow these steps to modify the Input Parameters property of the frmFinal form. By setting the Input Parameters property, you can provide information to input parameters in a stored procedure automatically.

Open the frmFinal form in Design view.

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

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

Save and close the frmFinal form.

To use the sample that you have just created, open the QBF_Form form and select values in any or all of the combo boxes. Click the command button to open the frmFinal form. Note that the form displays any matching records that meet the criteria that you have selected in the QBF_Form 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: filter

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