Form: SQL-Query and global module variable

[jokobe]

Hi newsgroup,

in my database a form is filled via SQL with this
query (pseudo code):
Select * from .... where id = popup_form.id

The field popup_form.id is a field within a popup, the
popup is closed after invoking the next form.

Now I want to use instead of a popup field a global
VBA variable called global_id. This variable is declared
as public in a module called tools. Is there a chance to
use this variable in SQL Queries? I want to use the
the SQL code in a normal form, without coding any VBA?

Thanks in advance

Jokobe

 

[Allen Browne]

You can only retrieve the value of a variable with a function call.

Put this into a module:
Function GetMyId() As Long
GetPopupId = global_id
End Function

Then in your query:
Select * from .... where id = GetMyId();

 

[Guest]

Hi Allen,
thanks for your fast answer. I tried your solution, but
the SQL query isn't returning any values - although it
should. (Somehow) the SQL is accepting the function call,
there isn't an syntax error, but no values are returned

jokobe

 

[Allen Browne]

Press Ctrl+G to open the Immediate window. Enter:
? GetMyId()

Does this give the desired result?

The function just returns the value of the variable:

Function GetMyId() As Long
GetMyId = global_id
End Function

Note that if you reset your project, the variable will lose its value.