Access 2000?
I have three different reports which all work off a user specified date range. I have created a macro to run them and as long as I specify "Between And" conditions in the queries behind them the macro works fine, but you have to enter the date range three different times.

Is there a way to specify the date range once in the macro so that all the reports run for that date range???

Hope someone will help!!! :confused:

Use a FORM for input and then refer to THAT for the values.

Used the form and still requires to enter the date each time for each report. On the form I have a start and end date, then in the individual queries I used a "between [start date] and [end date]" expression which refers to the form fields respectively.

Why still requiring to enter dates multiple times??

Read this Post (http://www.access-programmers.co.uk/forums/showthread.php?t=167718) it will give you all the information you need to accomplish what you want.

Thanks, but I am by no means a programer!! I follow what to enter into the individual queries, but am lost about creating the startup module and creating the new functions??

Can you guide me through this process?

Why still requiring to enter dates multiple times??

Because you need to use the FORM referencing instead of just [Enter Start], etc.

Form referencing is like:

[Forms]![YourFormNameHere]![YourControlNameHere]

Here is the command I tried.

Forms![Date Range Form]![Start Date] and Forms![Date Range Form]![End Date]

I placed this in the individual queries in the "Date" field criteria. When I execute the macro it steps through requiring to enter the dates 3 consecutive times to get the reports.

On the form I have two elements that are unbound; one is the start date and the other is the end date. When entering the end date it goes back to the start date. Not sure why it is not proceeding to the rest of the macro???

Is the issue in the Form and / or the macro??

If you have this as criteria in your query and it still pops up the parameter dialog, then you have something spelled wrong or are referencing an object that is not open.

Perhaps you can post your database here and we can take a quick look.

I didn't understand this
Not sure why it is not proceeding to the rest of the macro???

Are you running the macro from a command button on the form? That is the easy way to do this
I presume that you meant

Between Forms![Date Range Form]![Start Date] and Forms![Date Range Form]![End Date]

SOS is correct in that you should check the spelling and also ensure that the form remains open, using a command button ensures that of course. :D

Brian

Okay ... how do I post htis for you to look at??

Okay ... how do I post htis for you to look at??

See here (http://www.access-programmers.co.uk/forums/showthread.php?t=140587) for how to post a database to the forum.

Okay ... here it is.

I did not see any error but another set of eyes are appreciated. Don't laugh to hard ...just learning.

Okay, which queries are popping up the box?

Are these the ones?
q_Perioperative_Data_Query
q_DVT_Data Query
q_Complete_Advanced_Directive Query

What is happening is that the form is being opened and the queries /reports run.
The switchboard needs to open the Form, and after the dates have been entered a command button on the Form fires the macro to run the queries/reports, the macro could then close the Form.

Brian

The three queries that need to run to populate the reports are:

q_Advanced_Directive All Query
q_Advanced_Directive PAT Query
q_Advanced_Directive Phone Query

The others can run independently for Data Editing purposes from their associated Edit Forms.

okay, so Brian gave the answer then.

Ok .... then how do you make the command button trigger the macro? And how does that solve having to enter the dates several times??

:mad:I figured out the command button and told it to run the macro, however visually nothing happens. The reports if you click on the macro itself are there but do not open in print preview as designed. I have an "open report" after the run command but that makes no difference and finally it does not close the form at the end.:mad:

Finally I figured out the issue with the reports .... and now they work!!!!!

The only piece not working is the closing the form .... I get an error "This action can not be carried out while processing a form or report event". I click OK and the following Action Failed dialog box appears:
Macro Name = Identifies my macro
Condition = true
Actiong Name = Close
Arguments = Form, Data Range Form, Prompt

How can this be fixed??

Thanks for all your help.

Its years since I used macros and it is possible that I suggested doing something that is not allowed. :( I'll have to try and find time to design a test. It maybe that you will just have to close the form manually after you have run the reports.

Brian

Its years since I used macros ...I heard in 2010 all the templates would be programmed using Macros only, no vba coding.

OK I have had lunch and run a test, it all works for me. The report opens and the frmDates closes.
Can you post your DB again and I will take a look.

BTW it is best to avoid spaces in names as they lead to typos and increase the need to watch syntax.

Brian

I heard in 2010 all the templates would be programmed using Macros only, no vba coding.

As its 4 years since I retired and I only use ACCESS on the forum it is unlikely that I will ever upgrade, probably just as well from what I read of post 2003 releases. I'm getting too old to cope with all this change on my own. :eek:

Brian

As its 4 years since I retired and I only use ACCESS on the forum it is unlikely that I will ever upgrade, probably just as well from what I read of post 2003 releases.Still got the knowledge though Brian:eek: How do you manage to remember all these things?

I'm getting too old to cope with all this change on my own. :eek:Hehe! This is where your children (or maybe grandchildren?) come in.;) I'm sure they will be more than happy to assist.

Still got the knowledge though Brian:eek: How do you manage to remember all these things? .

I cheat, I look things up. ;)

Hehe! This is where your children (or maybe grandchildren?) come in.;) I'm sure they will be more than happy to assist .

Chance would be a fine thing.
Daughter no1 left school early but is now a senior analyst/programmer but does not use MS products.
Daughter no2 got a 1st in Computing but doesn't work in IT, she is a senior manager in the NHS.

Its quiet today, I'll have to go and watch the rugby.

Okay Brian here it is with the changes I have made.

Nothing there try again

Forgot to Zip It :D:D

I cheat, I look things up. ;)Haha! I see. But I bet everything you lookup is work you've done already. How come you don't have a website?


Chance would be a fine thing.
Daughter no1 left school early but is now a senior analyst/programmer but does not use MS products.
Daughter no2 got a 1st in Computing but doesn't work in IT, she is a senior manager in the NHS.Sounds like transfer of knowledge there Brian. It certainly cascaded down. At least they are both doing what they enjoy which is great.


Its quiet today, I'll have to go and watch the rugby.Have a good one.

Well that was a struggle I'd forgotten what little I knew about switchboards, still education is good for the soul.
After chasing my tail for ages I finally cracked it.
Remove the entry
Open Advance Directive Reports
from the On Entry property of the GO command button.

Sorry I cannot give you an explanation, it didn't look right and it does now work, but it was intuition rather than knowledge.

Brian

Thanks for the help ... it works!!! :D