In a module I have a public variable. In a procedure within that module I define that variable. Later on in that same procedure I call a query (through a report). How do I use that public variable as a parameter in my query? Thanks.
In a module I have a public variable. In a procedure within that module I define that variable. Later on in that same procedure I call a query (through a report). How do I use that public variable as a parameter in my query? Thanks.
You can't use it directly in a saved query, if that's what you mean. You can create a public function that does nothing but return the value of the variable and use that.
WHERE FieldName = FunctionName()
I tried that, and it seems to call my function three times. Here's my query:
and here's my function:Code:SELECT Table1.* FROM Table1 WHERE ID = Test_ID();
The input box comes up three times. Public_ID is my attempt to capture the value input. Any idea why my query calls my function three times?Code:Public Function Test_ID() As Integer Test_ID = InputBox("Enter the Test ID") Public_ID = Test_ID End Function
The function is calling itself, which is generally not good. I would expect somewhere else in your code:
Public_ID = InputBox("Enter the Test ID")
And then your function is one line:
Code:Public Function Test_ID() As Integer Test_ID = Public_ID End Function
That worked, thanks.
Last question (and maybe this belongs in the Reports section): now that I have this public variable, is there any way I can reference this in the header of a report?
Same way, with the function. The variable itself can only be accessed directly from within VBA. So, in a textbox:
=Test_ID()
Perfect, thanks very much.
No problemo.