Macro Opens Multiple Parameter queries requiring same answer

[David Wright]

Hello Folks

I have a Macro which consecutively opens seven ‘Make Table Query’s’. Each query requests two parameter inputs i.e. ‘Please Enter Department Code’ and ‘Please Enter Year’. It is arduous answering each parameter request for the seven queries’s as the answer to each department code will be the same department code and the answer to every year will be the same year. (I may choose to enter a different department or a different year but I would have to enter exactly the same information repeatedly as each query opens for that department and for that year). Is there a global parameter into which I can enter the year just once and the department just once?

The Macro is like this:
Setwarnings No
OpenQuery
OpenQuery
OpenQuery etc X7 querys
Msgbox “Action completed successfully”

Additional information: The data entered into the seven new tables resulting from the Make Table query’s, is linked to an excel worksheet. I simply update the worksheet to obtain the data I need about that department during that year. My problem is answering the 14 parameter questions in order to make the seven tables.

I would be very pleased to hear from anyone who knows how to resolve this. I am sure there must be some provision made, some tool which I am simply not aware of.

Thank you all for reading this and for any reply’s offered.

[NeoPa]

Personally, I'd junk the macro and code it up behind a form.

The form could have the two values on it (as unbound controls) and all the queries could either refer to the form controls in their design or, my personal preference, would be executed as SQL built up by the form's code using the SQL from the queries and the values on the form.

Welcome to Bytes!

[NeoPa]

If you really don't want to junk the macro, you can still use the form and design the queries to get their info from there.

[David Wright]

Hello NeoPa
Thank you for your reply. I do not know SQL although I do use a few little bits of code like msgbox "----- "and Docmd.Maximize. I will research on the net about how to make a query pick up readings from a form (Unbound control would be a Text Box i expect). Thank you for the suggestion. I look forward to researching how to implement this. If I really cannot resolve it after a few weeks I will return to this forum.
Many thanks for your reply and guidance.

[NeoPa]

No worries David. We're here if you need more help.