Access Cookbook 2nd Edition-Access Cookbook 2nd Edition

Recipe 1.7 Use a VBA Variable to Filter a Query

1.7.1 Problem

You'd like to be able to return rows in a query that have a test score greater than a specified value, which is stored in a VBA variable. When you try to use the variable in the query design grid, Access thinks it's a literal value. Is there some way to get queries to understand VBA variables?

1.7.2 Solution

To use a VBA variable in a query, you need to write a VBA function that returns the value of the variable as its return value and then reference the VBA function either as part of a calculation or in the criteria of a field. The only way to work with VBA in queries is to call a function. This solution shows you how to do that.

In the sample database 01-07.MDB you'll find tblScores, a table of names and test scores. The goal of the sample is to allow you to specify a cutoff value and list everyone whose scores are greater than that value.

Open the frmScores form. This form allows you to choose between a randomly selected cutoff value and a user-specified cutoff value. If you choose the user-specified cutoff value, a text box is made visible to allow you to enter the cutoff value. When you click on the "Show the results" command button, an event procedure runs that saves the cutoff value—either the randomly chosen cutoff or the user-specified cutoff—to a private variable and then runs the qryScores query.

The qryScores query references the private variable using the GetCutoff function and then returns the rows in tblScores in which the score is greater than the cutoff value (see Figure 1-19).

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

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

Create a select query, adding the tables and fields you wish to include in the query. The sample query, qryScores, is based on the tblScores table and contains two fields, Name and Score.

Create a VBA function or subroutine for which you wish to pass a variable to the query from Step 1. The sample database includes the frmScores form.

The following event procedure is attached to the cmdRunQuery command button:

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

Based on the user choice made using the grpCriteria option group, the procedure will either generate its own randomly chosen cutoff or grab the cutoff value from the txtCutoff text box. Once the value is generated, the event procedure calls the public subroutine SetCutoff, which stores the value in a private variable. The SetCutoff procedure and the variable declaration are shown here:

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

Reference the module-global variable intCutOff using a wrapper function that returns the value currently stored in the variable. For the sample query qryScores, enter the following criteria for the Score field:
```
>GetCutoff( )
```
The design view for this query is shown in Figure 1-20. The code for the GetCutoff function is:
```
Public Function GetCutoff( )
   ' Return the value of the module variable.
   GetCutoff = intCutoff
End Function
```

Figure 1-20. The sample query, qryScores, in design view

Execute the VBA procedure from Step 2. This causes the variable to be set, and the query then runs. When the query is executed, it references a function that returns the value stored in the VBA variable.

1.7.3 Discussion

A query cannot directly reference a VBA variable. It can, however, call a VBA function that returns the value stored in the VBA variable. To do this, you write a VBA wrapper function for each variable you wish to pass to a query. Because functions on form and report modules are normally local to that form or report (although you can make these functions public), you'll usually want to call a function stored in a global module—a module you can see in the database container.

In the example, we used a form to collect the values to pass to the VBA variable, intCutoff. Another way to solve this problem would be to use a parameter query that directly references the text box on frmScores. The example form frmScoresTextbox combined with qryScoresTextbox show this approach in action.

Using a form to feed the values to a query will not, however, always be so convenient. There will be times where you need to use a variable without a form. For example, you might use global variables to store settings that are read from an options table upon application startup. This options table might store, for example, the complete name of the user, her address, and other preferences. You may decide to store these values in a set of global variables to minimize the number of times you have to reread the values from the options table. In this case, these variables will not be stored on any form. As another example, you may need to base the query on some value obtained from another application using Automation. Even in those cases, however, you can always use a hidden form if you prefer that approach.

You can use a variation on this technique to reference combo box columns in a query. The query grid won't recognize Forms!MyForm!MyCombo.Column(2), but you can use a function that grabs the value in the desired column and delivers it to your query.

1.7.4 See Also

For more information on declaring variables and creating modules, see How Do I Create a New Module? in the Preface.

[ Team LiB ]