Microsoft KB Archive/235359
Article ID: 235359
Article Last Modified on 8/9/2004
- Microsoft Access 2000 Standard Edition
This article was previously published under Q235359
Advanced: Requires expert coding, interoperability, and multiuser skills.
This article applies only to a Microsoft Access project (.adp).
For a Microsoft Access 2002 version of this article, see 286828.
This article shows you how to use a form to specify the criteria for a query in a Microsoft Access project.
In an Access database (.MDB), you can use the query-by-form technique (QBF) to create a "query form" in which you enter query criteria. The query form contains blank text or combo boxes, each representing a field in your Access table that you want to query. You make entries only in the boxes for which you want to specify search criteria.
For additional information about query by form in an Access database, click the following article number to view the article in the Microsoft Knowledge Base:
209645 How to use the Query-by-form (QBF) technique
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 a Stored Procedure:
Follow these steps to create a stored procedure that you will use to return data to one of your forms:
- Open the sample Access project NorthwindCS.ADP, found 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 then close it.
Follow these steps to create two forms in NorthwindCS.adp: You will use QBF_Form to choose search criteria that will be used by the stored procedure (QBFProc) that you saved earlier. frmFinal will display the results returned by QBFProc.
- In the Stored Procedures list of NorthwindCS.adp, select QBFProc.
- On the Insert menu, click AutoForm. When prompted to enter a parameter, click Cancel.
- Save the form that you just created as frmFinal, and then close it.
- Create another new form named QBF_Form. This form should not be 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 CustomerID FROM Customers Combo Box: Name: cboEmployeeID Row Source Type: Table/View/StoredProc Row Source: SELECT EmployeeID, LastName FROM Employees Column Count: 2 Column Widths: 0;1 Bound Column: 1 Combo Box: Name: cboOrderDate Row Source Type: Table/View/StoredProc Row Source: SELECT OrderID, OrderDate FROM Orders Column Count: 2 Column Widths: 0;1 Bound Column: 2
- In the Toolbox, click Control Wizards to turn Control Wizards on.
- Add a command button to the form, and follow the Control Wizard steps so that the button will open frmFinal when the button is clicked.
- Save the form, and then close it.
Follow these steps to modify the Input Parameters property of frmFinal. Using Input Parameters allows you to provide information to input parameters in a stored procedure automatically.
- Open frmFinal in Design view.
Set the Input Parameters property to:
@CustomerID varchar(10)=forms!QBF_Form!cboCustomerID, @EmployeeID int = forms!QBF_Form!cboEmployeeID, @OrderDate datetime = forms!QBF_Form!cboOrderDate
- Set the Default View and Views Allowed property of frmFinal to Datasheet.
- Save and close frmFinal.
To use the sample that you have just created, open QBF_Form and select values in any or all of the combo boxes. Click the command button to open frmFinal, which will display any matching records that meet the criteria that you have selected in QBF_Form.
For more information about the Input Parameters property, click Microsoft Access Help on the Help menu, type "New Properties" in the Office Assistant or the Answer Wizard, click Search and select InputParameters in the New Properties (Alphabetical List) page.
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:
Keywords: kbhowto kbdownload KB235359