access: passing variables from vba to sql

Question

i am clicking a button on a form in access:

Private Sub Command29_Click()
some_variable = 2
stDocName = "test"
DoCmd.OpenQuery stDocName
End Sub

my query looks like this:

SELECT *
FROM [some_table]
WHERE [Occurrence Number]=some_variable;

is this possible to do?

Answer 1

AFAIK, the only ways to pass a parameter to a select query and to view the results are:

1. Update the SQL of the query to include the parameter
2. Use a form to supply the parameter

In general, option 2 is preferred.

With action queries, you have a lot more options.

EDIT re Comment

SELECT *
FROM [some_table]
WHERE [Occurrence Number]=Forms!SomeOpenForm!SomeControl