Microsoft KB Archive/235359

From BetaArchive Wiki

Article ID: 235359

Article Last Modified on 8/9/2004



APPLIES TO

  • 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.


SUMMARY

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

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:

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

Keywords: kbhowto kbdownload KB235359