How to Create a Pass Parameter Query Access in VBA

Creating parameter queries with Microsoft Access VBA (Visual Basic for Applications) involves using Access's predefined objects, including the QueryDef and Database objects. The query string used with these objects is the same used when creating Access queries without VBA (e.g., "SELECT * from mytable WHERE myfield = [user_entered_parameter]").

Things You'll Need

• Microsoft Access

Instructions

1. Open Access and create a table of sample data using the following rows. Enter the first row's text as the table's field names.

   book,datesold,netsale
   furthering words,12/1/2009,$5.03
   furthering words,12/3/2009,$4.97
   der meisterstringer,12/5/2009,$1.97
   der meisterstringer,12/6/2009,$0.97

2. Save the table as "books," then enter the Visual Basic IDE (Integrated Development Environment) by pressing "Alt-F11."

3. Click "Insert," followed by "Module" on the IDE's toolbar, then paste the following code into the code window:

   Public Sub param_q_select()
   Dim db As DAO.Database
   Dim qd As DAO.QueryDef
   Dim sqry As String
   Set db = CurrentDb
   sqry = "select * FROM books WHERE book like [Enter book title]"
   Set qd = db.CreateQueryDef("qpSelect", sqry)
   End Sub

4. Run the macro by placing the cursor anywhere inside this subroutine and pressing "F5."

5. Return to Access and close the navigation window at left if it's open. Re-open the window and notice the new, stored query ("qpSelect"). Double-click this query to run it, and type "*fur*" at the prompt. Notice the query results: only the rows with "furthering words" are shown.

6. Return to the IDE by pressing "Alt-F11," and run the macro using Step 4's instructions. Notice the error that appears this time ("Object 'qpSelect' already exists").

7. Revise the code to prevent the error by replacing it with this code:

   Public Sub param_q_select()
   Dim db As DAO.Database
   Dim qd As DAO.QueryDef
   Dim sqry As String
   Set db = CurrentDb
   On Error GoTo skip_delete
   db.QueryDefs.Delete "qpSelect"
   skip_delete:
   sqry = "select * FROM books WHERE book like [Enter book title]"
   Set qd = db.CreateQueryDef("qpSelect", sqry)
   End Sub

8. Re-run the code using Step 4's instructions. Notice that there is no error this time.

9. Revise the macro so that your program's user can choose which field to create a parameter query from: copy the full param_q_select subroutine and paste it in a blank area of the code window. Rename this duplicate as param_q_choose_field

10. Replace the existing program code of param_q_choose_field, between the "on error ..." and "End Sub" statements. Type or paste this code instead:

    Dim sf
    sf = InputBox("Enter field name")
    sqry = "select * FROM books WHERE " & sf & " like [Enter " & sf & " ]"
    On Error GoTo skip_delete
    db.QueryDefs.Delete "qpSelect"
    skip_delete:
    Set qd = db.CreateQueryDef("qpSelect", sqry)
    End Sub

11. Re-run the subroutine using Step 4's instructions, and type "netsale" when the "Enter field name" prompt appears.

12. Run the query created by the subroutine using Step 5's instructions. However, enter "*0*" when prompted for the netsale parameter. Press "Enter" and notice that only rows whose "netsale" field contains a "0" were returned.

References

• Microsoft: Build SQL Statements that Include Variables and Controls