Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

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 valueeither the
randomly chosen cutoff or the user-specified cutoffto 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:

1. 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.

2. 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

3. 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

4. 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 modulea 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.