How do I pass parameters into modules? 1

[KHerm]

I have set up a macro that runs quite a few queries and functions. The functions also call queries. Many of the queries need a user to enter the start date and end date. What I want to do is be able to let the user enter the start and end dates once, walk away, and find the finished process after it does it's 4 hour magic.

I've been searching the help file, but Access is definately not my environment (although it's been getting better) and I need to get this figured out today.

Thanks in advance!

Ken

 

[LarryDeLaruelle]

Ken:

The answer is Filter by Form. Set up an unbound form (frmRptMaster or whatever) with two unbound text boxes -- txtStart and txtEnd.

In your queries that need to filter by the dates, reference the controls on the form:

>= Forms!frmRptMaster.txtStart and <= Forms!frmRptMaster.txtEnd

Add a command button to kick off the report.


Let me know if you have any questions on implementation.

Larry De Laruelle
larry1de@yahoo.com

 

[ElGuapo]

If you are not experienced in VBA then the easiest way to do what you would like to do is to create a table where the data neccesary to run the queries will be stored. Then create a Form, where the user will input them and then change the queries that they will not ask for input from the user, but will lookup the data in the table.

However this is not a very good solution, because it is bad to have such a table in the db and also you would have to limit the table to only one record which takes a bit of programming.

A much better way would be to write a Module, which would prompt the user to enter the variables and then store them into variables or into a txt file and later input them into the queries. However thid takes quite a bit of programming.

Anyway in my experience it takes quite some time to get such a proccess to work and eats quite a lot of nerves to.
I suggest avoiding the use of many queries and writing as much code as possible in VBA because it is much easier to maintain and contorl.

ElGuapo

 

[KHerm]

Thank you, Larry! That was exactly what I needed. I don't have the luxury of redoing all my query work and I just did a quick short test that ran flawlessly, and in 4 hours I'll know if I found all the parameters in my queries I needed to get input from the form.

If I now use the Access macro-to-module converter, will I still be able use a form to fire up the module?

Thanks again.

Ken

Oh, how would I have found that information in the Access help? I do like to find answers on my own when I can.

 

[LarryDeLaruelle]

Ken:

I haven't used the macro to module conversion utility so I'm not sure how that would work. My guess is that any criteria specified in a macro would be carried into the new module.

Not sure where in Access help this would be -- I learned how to do it from a friend so I'm just passing it on. I do use this extensively in the user interface for setting filter criteria.

Glad I was able to help.
Larry De Laruelle
larry1de@yahoo.com