How to Query by Form in Microsoft Access 2007

Source data by using an access query.

Microsoft Access is a relational database management system designed as part of the Microsoft application suite. It was created to work seamlessly with Microsoft software. It can also link information directly from SharePoint, Excel, Lotus 1-2-3, HTML, Outlook, PowerPoint and XML. You can utilize Microsoft Access to develop application software and it is supported by Microsoft Visual Basic for applications. Furthermore, you can connect your queries, forms and reports in Access with macros. Querying by form in Microsoft Access requires a few steps to configure and format.

Instructions

1. Open your Access application and then the sample database "Northwind.mdb." Make a new form not based on any type of query and save it as "QBF_Form."

2. Open your new form. Compose two text boxes and one command button by entering the following information:

   "Text box 1

   ------------------

   Name: WhatCustomer

   Text box 2

   ------------------

   Name: WhatEmployee

   Command button 1:

   ------------------

   Caption: Search

   OnClick: QBF_Macro."

   Click on "Save."

3. Type in this as a new macro:

   "Text box 1

   ------------------

   Name: WhatCustomer

   Text box 2

   ------------------

   Name: WhatEmployee

   Command button 1:

   ------------------

   Caption: Search

   OnClick: QBF_Macro."

   Save it as "QBF_Macro."

4. Construct a new query based on the orders query. Enter this information:

   "Field: CustomerID

   Sort: Ascending

   Criteria: Forms![QBF_Form]![WhatCustomer] Or Forms![QBF_Form]! [WhatCustomer] Is Null

   Field: EmployeeID

   Sort: Ascending

   Criteria: Forms![QBF_Form]![WhatEmployee] Or Forms![QBF_Form]![WhatEmployee] Is Null

   NOTE: When you type the criteria, make sure that you

   type the entire criteria in a single Criteria field;

   do not split the criteria by placing the 'Or' section

   on a separate row.

   Field: OrderID

   Field: OrderDate."

   Save it as "QBF_Query."

5. Open the "QBF_Form" within the "Form" view. Type in these combinations of criteria:

   "Customer ID Employee ID Result

   -------------------------------------------------------

   <blank> <blank> All 830 orders

   AROUT <blank> 13 orders

   AROUT 4 4 AROUT orders for employee 4

   <blank> 4 156 orders for employee 4"

   Hit "Search" after each combination. Close the spreadsheet window after you view the results for each query.

References

• Microsoft Support: How to Use the Query by Form (QBF) Technique in Microsoft Access