multiple date entries in report

[Guest]

I am building a report that has several SUB reports in it. These Sub reports
are based on queries in which I am prompted to enter the date of the month
that I wish to view. I end up having to enter the same date about five
times. Is there a way to enter date ONE time and have it apply to all of the
prompts for the different reports?

 

[fredg]

I am building a report that has several SUB reports in it. These Sub reports
are based on queries in which I am prompted to enter the date of the month
that I wish to view. I end up having to enter the same date about five
times. Is there a way to enter date ONE time and have it apply to all of the
prompts for the different reports?

Create an unbound form.
Also add an unbound text control.
Set it's format to a valid date format.
Name "ForDate"

Add a Command Button to the form.
Code the button's click event:

Me.Visible = False

Name this form 'ParamForm'.

As criteria in the main query date field write:
forms!Paramform!ForDate

For each additional query that serves as a SubReport's RecordSource,
enter the same criteria:

forms!Paramform!ForDate

Next, code the Main report's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog

Code the Main report's Close event:
DoCmd.Close acForm, "ParamForm"

When ready to run the report, open the report.
The form will open and wait for the entry of the date wanted.
Click the command button and then report will run.
When the report closes, it will close the form.

All the SubReports will use the same parameter.

 

[Guest]

I set up the Form as you stated, but when I open the Report, I get the
following message:

"Microsoft Office Access can't find the macro 'DoCmd.'
The macro (or its macro group0 doesn't exist, or the macro is new but hasn't
been saved. Note that when you enter a macrogroupname.macroname syntax in an
argument, you must specify the name the macro's macro group was last saved
under."

Can you please let me know where i have gone wrong?

 

[fredg]

I set up the Form as you stated, but when I open the Report, I get the
following message:

"Microsoft Office Access can't find the macro 'DoCmd.'
The macro (or its macro group0 doesn't exist, or the macro is new but hasn't
been saved. Note that when you enter a macrogroupname.macroname syntax in an
argument, you must specify the name the macro's macro group was last saved
under."

Can you please let me know where i have gone wrong?

You place the code in the wrong place.

Here is how to write code.

Open the Form in Design View.
Select the Command Button.
Display the Command Button's Property sheet.
Click on the Event line.
On the line that says On Click, write:
[Event Procedure]
Then click on the little button with the 3 dots that appears on that
line.
When the code window opens, the cursor will be flashing between 2
already existing lines of code.
Between those 2 lines, write:

Me.Visible = False

Save the changes.

Then Open the Report in Design View.
Follow the same procedure for the Report's Open event and the Report's
Close event (placing the appropriate code in the proper event).

Save the changes and open the report.

 

[Guest]

Excellent! Thank you very much. But one solution leads to other questions.
Is it possible to open TWO sequential forms? You see I am comparing
previous year sales with currect year sales for specific months. With the
different queries I have built, you have to enter the current date and then
enter the previous year date. How can I do this?

I set up the Form as you stated, but when I open the Report, I get the
following message:

"Microsoft Office Access can't find the macro 'DoCmd.'
The macro (or its macro group0 doesn't exist, or the macro is new but hasn't
been saved. Note that when you enter a macrogroupname.macroname syntax in an
argument, you must specify the name the macro's macro group was last saved
under."

Can you please let me know where i have gone wrong?

You place the code in the wrong place.

Here is how to write code.

Open the Form in Design View.
Select the Command Button.
Display the Command Button's Property sheet.
Click on the Event line.
On the line that says On Click, write:
[Event Procedure]
Then click on the little button with the 3 dots that appears on that
line.
When the code window opens, the cursor will be flashing between 2
already existing lines of code.
Between those 2 lines, write:

Me.Visible = False

Save the changes.

Then Open the Report in Design View.
Follow the same procedure for the Report's Open event and the Report's
Close event (placing the appropriate code in the proper event).

Save the changes and open the report.

 

[fredg]

Excellent! Thank you very much. But one solution leads to other questions.
Is it possible to open TWO sequential forms? You see I am comparing
previous year sales with currect year sales for specific months. With the
different queries I have built, you have to enter the current date and then
enter the previous year date. How can I do this?

I set up the Form as you stated, but when I open the Report, I get the
following message:

"Microsoft Office Access can't find the macro 'DoCmd.'
The macro (or its macro group0 doesn't exist, or the macro is new but hasn't
been saved. Note that when you enter a macrogroupname.macroname syntax in an
argument, you must specify the name the macro's macro group was last saved
under."

Can you please let me know where i have gone wrong?
:

On Wed, 18 Apr 2007 13:38:02 -0700, Steve Y wrote:

I am building a report that has several SUB reports in it. These Sub reports
are based on queries in which I am prompted to enter the date of the month
that I wish to view. I end up having to enter the same date about five
times. Is there a way to enter date ONE time and have it apply to all of the
prompts for the different reports?

Create an unbound form.
Also add an unbound text control.
Set it's format to a valid date format.
Name "ForDate"

Add a Command Button to the form.
Code the button's click event:

Me.Visible = False

Name this form 'ParamForm'.

As criteria in the main query date field write:
forms!Paramform!ForDate

For each additional query that serves as a SubReport's RecordSource,
enter the same criteria:

forms!Paramform!ForDate

Next, code the Main report's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog

Code the Main report's Close event:
DoCmd.Close acForm, "ParamForm"

When ready to run the report, open the report.
The form will open and wait for the entry of the date wanted.
Click the command button and then report will run.
When the report closes, it will close the form.

All the SubReports will use the same parameter.

You place the code in the wrong place.

Here is how to write code.

Open the Form in Design View.
Select the Command Button.
Display the Command Button's Property sheet.
Click on the Event line.
On the line that says On Click, write:
[Event Procedure]
Then click on the little button with the 3 dots that appears on that
line.
When the code window opens, the cursor will be flashing between 2
already existing lines of code.
Between those 2 lines, write:

Me.Visible = False

Save the changes.

Then Open the Report in Design View.
Follow the same procedure for the Report's Open event and the Report's
Close event (placing the appropriate code in the proper event).

Save the changes and open the report.

Well, yes you can, using the same method described previously, but
why?
Simply add 2 more unbound controls to this form, name them
differently, (StartDateThisYear, StartDateLastYear, EndDateThisYear,
EndDateLastYear) and use the same syntax in the query. Just refer to
the appropriate controls.