Passing Public varialble to a query

[Guest]

Hello,

I've got a button that when pushed will declare my VarCurrQry variable as a
parameter that I then want to pass to a query.

From reading posts it looks like I need to set up a module but I'm getting
confused about where to declare VarCurrQry.
In my form Objects I have the following:

Option Compare Database
Option Explicit
Public VarCurrQry As Variant
____________________________
Private Sub CmdViewOpen_Click()
VarCurrQry = "OPEN ITEM"
DoCmd.OpenQuery "Qry-RESEARCH", acViewNormal, acReadOnly
End Sub

But the Module recognizes (ie auto caps) the var VarCurrQry but gives me an
error that it's undefined:
Module1:

Option Compare Database
Option Explicit
____________________
Function QryParamater()
QryParameter = VarCurrQry
End Function

Any suggestions?
Thank you!

 

[Ken Snell \(MVP\)]

You don't need a module for the query to be opened. Instead, your query
needs to use the public function to get the value of the variable. For
example:

SELECT * FROM TableName
WHERE Field1 = QryParamater();

 

[Guest]

The Variable need to be defined in the module declaration for the function to
recognize it,

Global VarCurrQry As Variant
=======================
The function need to be declared in the Module for the query to recognize it

=======================
Then you can use the function as Ken explained

SELECT * FROM TableName
WHERE Field1 = QryParamater();

 

[Ken Snell \(MVP\)]

The Variable need to be defined in the module declaration for the function
to
recognize it,

Global VarCurrQry As Variant

Thanks, Ofer... I had missed that part of his post.

 

[Guest]

It looked more like I had to declare the QryParam var in the function and I'm
getting the contents on both vars.
But the query comes up blank. Do we need to get quotes arounf the functiomn
results in the query or something?

SELECT [WORKING TABLE].STATUS, [WORKING TABLE].*
FROM [WORKING TABLE]
WHERE ((([WORKING TABLE].STATUS)=QryParamater()));

Thanks again for your help

 

[Guest]

If this variable VarCurrQry is also declared in the form the function wont
recognize the value assign to it.
This variable, VarCurrQry , has to be declared under the module (not in the
function), and only there.

If it's still in the form, remove it.

--
Good Luck
BS"D

It looked more like I had to declare the QryParam var in the function and I'm
getting the contents on both vars.
But the query comes up blank. Do we need to get quotes arounf the functiomn
results in the query or something?

SELECT [WORKING TABLE].STATUS, [WORKING TABLE].*
FROM [WORKING TABLE]
WHERE ((([WORKING TABLE].STATUS)=QryParamater()));

Thanks again for your help

The Variable need to be defined in the module declaration for the function to
recognize it,

Global VarCurrQry As Variant
=======================
The function need to be declared in the Module for the query to recognize it

=======================
Then you can use the function as Ken explained

SELECT * FROM TableName
WHERE Field1 = QryParamater();