How to implement Query by Form (QBF) in an Access project

Article translations Article translations
Article ID: 286828 - View products that this article applies to.
This article was previously published under Q286828
Advanced: Requires expert coding, interoperability, and multiuser skills.

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

For a Microsoft Access 2000 version of this article, see 235359.
Expand all | Collapse all

SUMMARY

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

MORE INFORMATION

In an Access database (.mdb) or in a Microsoft Office Access 2007 database (.accdb), you can use the query by form (QBF) technique to create a "query form" in which you enter 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 only in the boxes for which you want to specify search criteria.

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

Creating a Stored Procedure

Follow these steps to create a stored procedure that you will use to return data to a form:
  1. Open the sample Access project NorthwindCS.adp. By default, this file is installed in the <Drive>:\<Microsoft Access samples folder>.
  2. In Microsoft Office Access 2003 or in Microsoft Access 2002, click Query on the Insert menu.

    In Microsoft Office Access 2007, click the Create tab.
  3. In Access 2003 or in Access 2002, click Create Text Stored Procedure in the New Query dialog box, and then click OK.

    In Access 2007, click Stored Procedure in the Other group.
  4. Type the following Transact-SQL statements in the Stored Procedure window:
    Create Procedure "QBFProc"
        @CustomerID varchar(10), @EmployeeID int, @OrderDate datetime
        As
        Declare @SQLSTRING  varchar(1000)
        Declare @SelectList varchar(1000)
    
        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
    
        --Concatenate the SELECT and the WHERE clause together
        --unless all three parameters are null in which case return
        --@SelectList by itself without criteria.
        IF @CustomerID IS NULL AND @EmployeeID IS NULL AND @OrderDate IS NULL
    	BEGIN
    	    SELECT @SelectList = @SelectList
    	END
        ELSE
    	BEGIN
    	    SELECT @SelectList = @SelectList + ' ' +  @SQLSTRING
    	END
    
        --Execute the SQL statement.
        EXECUTE(@SELECTLIST)
    					
  5. Save the stored procedure with the default name of QBFProc, and then close the procedure.

Creating the Query By Form

Follow these steps to create the QBF_Form form, which you will use to choose search criteria used by the stored procedure.
  1. Create a new form that is not based on any table or query, and name it QBF_Form.
  2. In Access 2003 or Access 2002, make sure that the Control Wizards button is not pressed in in the toolbox. In Access 2007, make sure that the Use Control Wizards button is not pressed in the Controls group. Then, add three combo boxes to the form with the following property assignments:
       Combo Box:
       Name: cboCusID
       Row Source Type: Table/View/StoredProc
       Row Source: SELECT CustomerID FROM Customers
    
       Combo Box:
       Name: cboEmpID
       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: cboOrder
       Row Source Type: Table/View/StoredProc
       Row Source: SELECT OrderDate FROM Orders
    					
  3. Add a command button to the form with the following property assignments:
       Name: cmdOpenFinal
       Caption: Open Form
       On Click: [Event Procedure]
    					
  4. Click the Build (...) button to the right of [Event Procedure], and then set up the procedure as follows:
    Private Sub cmdOpenFinal_Click()
        DoCmd.OpenForm "frmFinal", acFormDS
    End Sub
    					
  5. Save and then close the form, making sure to save the form as QBF_Form.

Creating the Form to Display the Results

Follow these steps to create the frmFinal form, which will display the results of the stored procedure based on the criteria that you enter in QBF_Form.
  1. In Access 2003 or in Access 2002, click Queries under Objects in the Database window, and then click the QBFProc stored procedure so that it has the focus.

    In Access 2007, click the Forms group in the navigation pane, and then click the QBFProc stored procedure so that it has the focus.
  2. In Access 2003 or in Access 2002, click AutoForm on the Insert menu.

    In Access 2007, click the Create tab, and then click Form in the Forms group.
  3. When you are prompted to enter a parameter, click Cancel.
  4. Save the form that you just created as frmFinal.
  5. Set the InputParameters property of this form to:
    @CustomerID varchar(10) = Forms!QBF_Form!cboCusID, @EmployeeID int = 
    Forms!QBF_Form!cboEmpID, @OrderDate datetime = Forms!QBF_Form!cboOrder
    					
  6. Save and then close the frmFinal form.
To use the sample that you 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, which will display any matching records that meet the criteria that you selected in the QBF_Form form.

REFERENCES

For additional information about QBF in an Access database, click the following article number to view the article in the Microsoft Knowledge Base:
209645 ACC2000: How to Use the Query-by-Form (QBF)Technique
For additional information about Transact-SQL (TSQL) and creating stored procedures with input parameters, visit the following Microsoft Web site:
http://technet.microsoft.com/en-us/sqlserver/bb331756.aspx

Properties

Article ID: 286828 - Last Review: November 14, 2007 - Revision: 5.2
APPLIES TO
  • Microsoft Office Access 2007
  • Microsoft Office Access 2003
  • Microsoft Access 2002 Standard Edition
Keywords: 
kbadp kbprogramming kbdesign kbdatabase kbdta kbhowto KB286828

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com