Daily Access Tips

Here are three questions that have been asked of me regarding how to pass a value from one place to another in MS Access:

1. I have multiple queries and need a form for a date to be entered once. Rather than have to enter the same dates for 16 queries. I would like to set them up with a temporary variable somehow so that each query could read the date and the user would only have to enter the date range once.
2. I have a query and want to establish criteria based on a combo box on a Microsoft Access form. When I leave the form’s combo box blank I want to include everything in the query output otherwise just the item in selected in the combo box.
3. What is the best way for me to get the value of a variable from a subroutine into another subroutine or a query?

I commonly get asked how to pass a value from one location in Microsoft Access to another. In VBA, Visual Basic for Applications, code a value that is passed or referenced later in code is called a variable. If it is referenced later out side of the form or code module were it was first given a value, then we need the variable value to be visible broadly throughout the application. This public variable (called “strFilter ” below), then needs to be placed in a code module that you create in Access. A simple function is then needed to retrieve the value of your public variable that you have declared in that code module you created. At the end of this article you will find a video tutorial demonstrating how to do this. Note that versions of Access 2007 or later have something called “tempvars” which serve this purpose with some additional benefits. You can find an article on this at http://www.vb123.com.au/toolbox/09_access/needforspeed.htm.

Create a new code module where you will declare your public variables and write a function to get them when they are needed.

Declare the public variable in a code module using this single line of code in the declaration area at the top of the code module you just created:

Public qrytesting as Variant

Note that the word “Public” allows this value to be visible from anywhere in the Microsoft Access programming.

Now create the public function in this code module to get the value of the public variable:

‘This function requires the name of the public variable it is to retrieve, in this case “strFilter”

Public Function GetPublicVariable(VariableName As String) As Variant
Select Case VariableName
Case “strFilter”
GetPublicVariable = strFilter
Case Else
MsgBox “You need Variable Name on GetPublicVariable function”
End Select
End Function

You set the public variable like this:
strFilter = [NameOfFormControl]
or
strFilter = some value

or

strFilter = another variable

This code is usually placed in the after update event of a form control.

You will retrieve the value of the set public variable with this line of code:

GetPublicVariable(“strFilter”)

GetPublicVariable(“strFilter”) can be used as the query criteria of the query above called “qrytesting”.

Regarding: “When I leave the form’s combo box blank” you could use this combination in the
Access query grid’s criteria cell:

Like GetPublicVariable(“strFilter”) & “*”
The Like operator will give you anything starting with the value that is currently being held
in the public variable call “strFilter”, however if nothing is stored there, a null value, then Microsoft Access wild card value, the *
asterisk, would be used for retrieving all values.

A simpler function to retrieve the public variable could have been written as seen below, but
it would not have been reusable for other public variables.

Public Function GetStrFilter() as Variant
GetStrFilter = strFilter
End Function

from youtube

I hope this has been useful.  Let me know.

Bob Heifler