Microsoft KB Archive/235359

From BetaArchive Wiki

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:

  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
    Declare @SQLString  varchar(1000)
    Declare @SelectList varchar(100)
    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  
            SELECT @SQLString = 'WHERE CustomerID = ''' + @CustomerId + ''''
    --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
                  IF @SQLSTRING Is NOT NULL 
                SELECT @SQLSTRING = @SQLSTRING + 
                               ' AND EmployeeID = ' + Convert(varchar(100), @EmployeeID) 
                SELECT @SQLSTRING = 'WHERE EmployeeID = ' +
                               Convert(varchar(100), @EmployeeID) 
    --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
            IF @SQLSTRING Is NOT NULL 
                SELECT @SQLSTRING = @SQLSTRING +
                               ' AND OrderDate = ''' + Convert(varchar(20), @OrderDate) + ''''
                 SELECT @SQLSTRING = 'WHERE OrderDate = ''' +
                                Convert(varchar(20), @OrderDate) + ''''
    --Concantinate the SELECT list and WHERE clause together.
    SELECT @SelectList = @SelectList + ' ' +  @SQLString
    --Execute the result 
  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:

      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:

Keywords: kbhowto kbdownload KB235359