Export (0) Print
Expand All

Using Variables in VBA to Filter Access 2007 Queries

Office 2007

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Summary: Learn how to filter the results of a Microsoft Office Access 2007 query by using a fixed expression, a built-in VBA function, or a VBA variable of a custom function. (3 printed pages)

Office Visual How To

Applies to: 2007 Microsoft Office System, Microsoft Office Access 2007

Adapted from Access Cookbook, 2nd Edition by Ken Getz, Paul Litwin and Andy Baron Copyright (c) 2004, O'Reilly Media, Inc. All rights reserved. Used with permission.

Ken Getz, MCW Technologies, LLC

April 2009

Overview

Sometimes when you run a query, you only want to return those rows that have values greater than a value that you specify in a variable. If you try to use that variable in the query design grid, Access interprets the variable as a literal value. To finesse a query to correctly interpret a VBA variable, you can use a VBA function that returns the value of the variable.

Code It

Download the sample database

To use a VBA variable in a query, you must write a VBA function that returns the value of the variable. Then, you must reference the function as part of a calculation, or reference it in the criteria of a field. In other words, the only way to work with VBA in a query is to call a function.

In the sample database, UseAVBAFunctionToFilterAQuery.accdb, you'll find a table of names and test scores in tblScores. The sample makes it easy for you to specify a cutoff value and list everyone whose scores exceed that value.

To begin, open frmScores, a form that gives you a choice between a randomly selected cutoff value and a user-specified cutoff value. (If you choose the user-specified cutoff value, the form displays a text box that you can use to type the cutoff value.) After you make your choice and click the Show the results button, an event procedure runs that saves the cutoff value to a private variable and then runs the qryScores query.

The qryScores query references the private variable by using the GetCutoff function, and then returns the rows in tblScores that contain scores greater than the cutoff value.

Figure 1. The sample form, frmScores, and its output, qryScores

The query returns the scores above a cutoff value

Follow these steps to use a VBA variable in a query:

  1. In a new select query, specify the tables and fields that you want to include. For example, the sample query, qryScores, is based on the tblScores table and contains two fields, Name and Score.

  2. Create a VBA function or subroutine that returns the value of the variable that you want to use in the query from Step 1. The following event procedure is attached to the cmdRunQuery command button in the form from the sample database, frmScores.

    Private Sub cmdRunQuery_Click()
    
        Dim intCutoff As Integer
        
        If Me.grpCriteria = 1 Then
            ' Use a random cutoff.
            ' You generate a random number between x and y
            ' by using the formula Int((y-x+1)*Rnd+x).
            ' This example generates a number between 0 and 100.
            Randomize
            intCutoff = Int(101 * Rnd)
            MsgBox "The random cutoff value is " & intCutoff, _
             vbOKOnly + vbInformation, "Random Cutoff"
            Me.txtCutOff = intCutoff
        End If
        SetCutoff Me.txtCutOff
        DoCmd.OpenQuery "qryScores"
    End Sub
    

    Depending on which option the user chooses in the grpCriteria option group, the procedure generates a random cutoff value or grabs the value that the user specifies in the txtCutoff text box. Once the value is determined, the event procedure calls the public subroutine SetCutoff, which stores the value in a private variable. The following code shows the SetCutoff procedure and the variable declaration.

    Private intCutoff As Integer
    
    Public Sub SetCutoff(Value As Integer)
       ' Set the module variable to be
       ' the value passed in from externally.
       intCutoff = Value
    End Sub
    
  3. Now reference the module-global variable intCutOff by using a wrapper function that returns the value currently stored in the variable. In the sample query qryScores, type “>GetCutoff()” in the Score field.

    Figure 2. The sample query, qryScores, in design view.

    The sample query in design view

    The following code shows the GetCutoff function.

    Public Function GetCutoff()
       ' Return the value of the module variable.
       GetCutoff = intCutoff
    End Function
    
  4. Now execute the VBA event procedure from Step 2 to set the variable and run the query. When the query executes, it references a function that returns the value stored in the VBA variable.

Read It

A query cannot directly reference a VBA variable. It can, however, call a VBA function that returns the value stored in the VBA variable. That is, you must write a VBA wrapper function for each variable that you want to pass to a query. Because functions on form and report modules are typically (but not necessarily) local to that form or report, you will typically call a function stored in a global module.

In the example, you used a form to collect the values to pass to the VBA variable, intCutoff. Alternatively, you could use a parameter query that directly references the text box on frmScores. The example form frmScoresTextbox combined with qryScoresTextbox illustrate this approach.

Using a form to input values into a query might not always be convenient. You might need to use a variable for which you do not have a corresponding form. For example, you might use global variables to store settings such as the complete user name, user address, and other options in an options table that you read on application startup. To minimize the number of times that you have to reread those values from the options table, you might decide to store the values in a set of global variables. If you do, you would not store those variables in a form. Thus, you’ll find it easiest to use a VBA function that exposes the value of each variable for which you need to filter a query. (If you still prefer to use the form-based solution, you can create a hidden form that exposes VBA variable values, although it is easier to simply create a VBA function that returns the value of the variable.)

See It

Using a VBA Variable to Filter a Query in Access

Watch the Video

Video Length: 00:08:47

File Size: 8.38 MB WMV

Explore It
Show:
© 2014 Microsoft