Access Cookbook - Ken Getz [20]
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.
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.
TIP
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.
See Also
For more information on declaring