How to pass a VBA Variable Value to a query

[Anthony]

Is it possible to pass a VBA Variable Value into the criteria section of a
field in a query? (In design mode)

 

[Marshall Barton]

Is it possible to pass a VBA Variable Value into the criteria section of a
field in a query? (In design mode)

No.

If you explained what you are trying to accomplish (instead
of how you thought you would do it), maybe someone will be
able to suggest an alternate approach.

 

[Guest]

Hi Anthony,

Like Marshall said, it sort of depends on what you want to do. But, check
out this thread:

http://www.tek-tips.com/viewthread.cfm?qid=1325875&page=1

There's code in there that lets you, in a flexible way, 'assemble' a 'where"
statement to use in a DoCmd.OpenReport, , , strWhere

I assume it works with DoCmd.OpenForm as well.

Good luck,
CW

 

[Allen Browne]

Just to be clear, in Access 2007 you can have variables that are available
in queries or in the control source of a text box.

TempVars are new. I haven't actually used them yet (beyond a quick test),
but they look promising.

 

[Guest]

Hi Anthony

In your VBA code create a function like the following one. VariableName is
your VBA variable.

Function FunctionName()
FunctionName = VariableName
End Function

In design mode of your query, right mouse click in the criteria box you wish
to use your function variable in. Select 'Build' option in the popup. Click
on 'Functions' to expand the list of functions. Select the VBA module your
function resides in. Your function should be listed in the right most
column. Double click it and OK. Bubda bing budda boom, your variable is the
criteria for your query.

For kicks click on the SQL view mode for your query (instead of Design View)
to see what the SQL code looks like.

Hope this helps
Norm

 

[Anthony]

that's dyna-mite!

thanks!

Hi Anthony

In your VBA code create a function like the following one. VariableName
is
your VBA variable.

Function FunctionName()
FunctionName = VariableName
End Function

In design mode of your query, right mouse click in the criteria box you
wish
to use your function variable in. Select 'Build' option in the popup.
Click
on 'Functions' to expand the list of functions. Select the VBA module
your
function resides in. Your function should be listed in the right most
column. Double click it and OK. Bubda bing budda boom, your variable is
the
criteria for your query.

For kicks click on the SQL view mode for your query (instead of Design
View)
to see what the SQL code looks like.

Hope this helps
Norm