Visual Basic Global Variables as Query Parameters

This Access download will provide you
with all the forms, modules, queries and VBA code to get you started.
Here is the SQL query code used in the example:
SELECT M_Student_Courses.Student_ID, L_Students.Student, L_Courses.Course
FROM (M_Student_Courses INNER JOIN L_Students ON M_Student_Courses.Student_ID =
L_Students.Student_ID) INNER JOIN L_Courses ON M_Student_Courses.Course_ID =
L_Courses.Course_ID
WHERE (((M_Student_Courses.Student_ID)=get_global('gbl_student_id')))
ORDER BY L_Students.Student, L_Courses.Course;

In the code above, get_global is a custom query function that passes back the
value of any global variable you have defined. In this case we are
retrieving the GBL_Student_ID which was previously selected as a parameter in
the form below.

Note that you can create and use custom VBA functions in queries just as you use
custom public functions in regular VBA code. These custom function can
perform complex recordset operations that are not normally available in access
queries. Complex scalar functions can be implemented with simple VBA
custom functions. The get_global function is just a simple example of
these query functions.
There are other methods for returning global variables, such as the Eval
function, however we find this custom method more reliable and flexible.