Using VBA to run multiple queries

[wptaylor4]

Hey folks, new to the forum so bear with me.



I am using VBA to run multiple queries in access. So far I have the docmd.openquery "various queries" thing set up fine, but naturally there is a catch.

The queries I am setting up are append queries to take a big file and disperse it over multiple (13) tables. This requires me typing in a 4 digit YYMM to append them and it happens for each query. Therefore even though I am running them all in a row I still need to type in "1306" 13 times to key the june 2013 entries. I was wondering if there was anyway for vba to automatically key this or if there is anyway for me to adjust my vba or queries so that I only have to key it one time. I have included my code for what I have now.

Public Sub QueryRunner()


DoCmd.OpenQuery ("Query1")
DoCmd.OpenQuery ("Query2")
DoCmd.OpenQuery ("Query3")
' and onward to query 13


End Sub

Any help would be greatly appreciated! Thanks!

[pbaldy]

I would run the sub from a form where you enter the criteria in a textbox, and have all the queries get the value from there.

[wptaylor4]

Thanks for the quick response. Unfortunately I am relatively new to access and have little idea on how to do that. Could you clarify?

[pbaldy]

The criteria would look like

Forms!FormName.TextboxName

instead of

[Enter year]

[wptaylor4]

Thanks so much. This is as good as solved for me

[pbaldy]

Happy to help!