Macro Variables

[Ann Needs help?]

Help? Currently I have a macro that runs about 10 to 15 queries. These
queries are updates, inserts, and outputs to a spread sheet. They all prompt
for a variable called - ID. I don't want to hardcode the variable because it
changes alot. Short of rewritting all of the queries in VB, is there a way
to have the macro only prompt me once for the ID variable. Sort of like a
global variable? Currently I am using access 2003. Thank you, Ann

 

[Dorian]

You could display a form to collect the value for ID, then store that value
in a global variable.
Then all your queries could refer to the global variable via a function call
whose only purpose is to return the ID.
Public Function GetID() As String
GetID = gblID
End Function
SELECT...FROM...WHERE... = GetID()
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[John W. Vinson]

Help? Currently I have a macro that runs about 10 to 15 queries. These
queries are updates, inserts, and outputs to a spread sheet. They all prompt
for a variable called - ID. I don't want to hardcode the variable because it
changes alot. Short of rewritting all of the queries in VB, is there a way
to have the macro only prompt me once for the ID variable. Sort of like a
global variable? Currently I am using access 2003. Thank you, Ann

Create a little unbound form (frmCrit let's call it) with one control
(textbox, combo box, whatever is appropriate) for the ID, and put a button on
the form to launch your macro.

Change the criteria to

=Forms![frmCrit]![txtID]

in all the queries, rather than having a prompt.

 

[Ann Needs help?]

Thank you - It worked!!!! Thanks alot.

Help? Currently I have a macro that runs about 10 to 15 queries. These
queries are updates, inserts, and outputs to a spread sheet. They all prompt
for a variable called - ID. I don't want to hardcode the variable because it
changes alot. Short of rewritting all of the queries in VB, is there a way
to have the macro only prompt me once for the ID variable. Sort of like a
global variable? Currently I am using access 2003. Thank you, Ann

Create a little unbound form (frmCrit let's call it) with one control
(textbox, combo box, whatever is appropriate) for the ID, and put a button on
the form to launch your macro.

Change the criteria to

=Forms![frmCrit]![txtID]

in all the queries, rather than having a prompt.

 

[Guest]

You could display a form to collect the value for ID, then store that
value
in a global variable.
Then all your queries could refer to the global variable via a function
call
whose only purpose is to return the ID.
Public Function GetID() As String
GetID = gblID
End Function
SELECT...FROM...WHERE... = GetID()
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and
they
eat for a lifetime".