Module Help needed for access.

[davin2929]

I need to run a bunch of macros in access that in turn run queries. Unfortunately I have to enter the week number of the year for the queries. So I have to type the week number about 30 times while the macros are running. Is there a way to input the week number once and have all the macros refer to that week number. I think this would have to be done in a module but I'm not sure how to get the macros/queries to refer to the stored number. Any help would be greatly appreciated. Thanks.

 

[CT Witter]

What I would suggest:

Create a Form to enter the data: Have a textbox for the data and a command button(more on this later)

In all your queries, everywhere you have the date criteria, you want to reference your form textbox..ie [Forms]![FormName]![TexboxName]

the command button on the form will run your macro, which will run your queries.
You could shorten this by writing code in the command button that will run the queries(we can cover this if you would like).

To run your queries,
1) Open the form
2) enter the date
3) click the command button to trigger the macro to trigger the query.

All queries run and you only have to enter the date once!

HTH,
CT

 

[mdmilner]

Ok, not picking at wording specifically but I think what you meant to say is you execute a macro and while the 30 QUERIES are running you have to type in variable values each time.

Have you thought about rewriting all this as VBA instead?
Gives you a lot more flexibility on things including just defining a variable as your date once and then having the value dynamically added to the queries within the code.

strVal = InputBox("Which week would you like?", "Week Chooser")

strSQL = "DELETE * FROM tblName WHERE fld1='" & strVal & "'"
DoCmd.RunSQL strSQL

This would perform an action query (deletion) on each record where the week happened to be the one you chose. I, um, would choose a different action query to test with, though.

Mike