CODEWITHSUNDEEP
sqlvbams-access
Alex Gordon
Alex Gordon

Reputation: 60731

access: passing variables from vba to sql

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?

Upvotes: 0

Views: 1472

Answers (1)

Fionnuala
Fionnuala

Reputation: 91356

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

Upvotes: 1

Related Questions