How can I carry a variable across several queries?

[Lee Buehler]

I have created a macro to open several queries in order to calculate a
complex set of operations.

The first macro command is to open a select query which selects all seller
records for a particular event. This query then sums the quantity for each
product sold. It then updates the Seller record with the total products
sold to the "Total Units" field in the seller table.

The second macro command is to open a Make Table query which uses the same
dynaset to compile the total unit and count of sellers info to update the
Event table.

My problem is that in the first query, I make the dynaset selection with a
"[Enter Event Code]" criteria for the event code field in the seller table.
To get the same dynaset for the second query, I did the same thing again.

This causes the operator to have to enter the "Event code" when the macro
first begins, and then she is required to re-enter the same event code for
each successive query within the macro.

Is there a way to carry this variable, once entered, through the macro and
each of the succeeding queries?

Lee

 

[Steve Schapel]

Lee,

Set up a form with unbound textboxes for the entry of your query
criteria, and have this form open at the time that you run your macro.
The user can enter the Event Code into the applicable textbox, and then
in the query criteria, instead of your parameter prompt, you reference
the textbox using syntax such as...
[Forms]![NameOfForm]![NameOfTextbox]

By the way, I can't comment in detail without knowing more, but your
mention of "open a select query" in the macro seems strange. I can't
imagine a scenario when you would need to do this, and I suspect this
action is unnecessary.

 

[Lee Buehler]

Thanks for the help... the form was the secret I was looking for...

As for the "open a select query" ... I misspoke... The first macro command
was to open an update query which selects the records for a particular event
and then sums the quantity for each product sold. It then updates the
Seller record with the total products sold to the "Total Units" field in the
seller table.

Thanks again,

Lee