By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,122 Members | 927 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,122 IT Pros & Developers. It's quick & easy.

Macro Opens Multiple Parameter queries requiring same answer

P: 11
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.
Feb 25 '09 #1
Share this Question
Share on Google+
4 Replies


NeoPa
Expert Mod 15k+
P: 31,419
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!
Feb 25 '09 #2

NeoPa
Expert Mod 15k+
P: 31,419
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.
Feb 25 '09 #3

P: 11
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.
Feb 27 '09 #4

NeoPa
Expert Mod 15k+
P: 31,419
No worries David. We're here if you need more help.
Feb 27 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.