ACC2000: Implementing Query-by-Form in an Access Project (235359)

MORE INFORMATION

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: 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:

1. Open the sample Access project NorthwindCS.ADP, found 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 then close it.

Creating Forms

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.

1. In the Stored Procedures list of NorthwindCS.adp, select QBFProc.
2. On the Insert menu, click AutoForm. When prompted to enter a parameter, click Cancel.
3. Save the form that you just created as frmFinal, and then close it.
4. Create another new form named QBF_Form. This form should not be 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 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
   					

6. In the Toolbox, click Control Wizards to turn Control Wizards on.
7. 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.
8. Save the form, and then close it.

Modifying frmFinal

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.

1. Open frmFinal in Design view.
2. 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
   					

3. Set the Default View and Views Allowed property of frmFinal to Datasheet.
4. 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: