Open a Form From the Report That Accepts Dates?

[Robert T]

I have forms that accept a starting/ending date and then previews a report
filtered by those dates. However, I want to set it up so that whenever a user
runs a specific report, the dialog form pops up first, the user enters both
dates, and then the report is previewed, filtered by those two dates.

I've used a nifty program called Alpha Five for many years and we can do the
above with relatively little coding. Is there way to do that in Access 2003?

Thanks,
Robert

 

[fredg]

I have forms that accept a starting/ending date and then previews a report
filtered by those dates. However, I want to set it up so that whenever a user
runs a specific report, the dialog form pops up first, the user enters both
dates, and then the report is previewed, filtered by those two dates.

I've used a nifty program called Alpha Five for many years and we can do the
above with relatively little coding. Is there way to do that in Access 2003?

Thanks,
Robert

Create an unbound form. Add 2 Text Controls.
Name one StartDate and the other EndDate.

Add a command button.
Code the button's click event:

Me.Visible = False

Name this form 'ParamForm'.

In the query, in it's Date field's criteria line, write:
Between forms!ParamForm!StartDate AND forms!ParamForm!EndDate

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

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

When ready to run the report, open the report. The form will display
and wait for the entry of the dates. Click the command button and the
report will run without need for any further parameter entries. When
the report closes, it will close the form.

 

[Robert T]

Fred:

That sounds perfect, exactly what I was looking for.

Thanks so much, I'll let you know if I come across any problems.

Robert

:

 

[BillH]

Fred:

That sounds perfect, exactly what I was looking for.

Thanks so much, I'll let you know if I come across any problems.

Robert



:- Hide quoted text -

- Show quoted text -

This worked terrific - many thanks. I need to run several Reports in 1
macro. Without ahving to enter the StartDate and EndDate for each
Report, can you suggest how to bundle this so that I enter the date
parameters once?

Thanks,

Bill

 

[fredg]

This worked terrific - many thanks. I need to run several Reports in 1
macro. Without ahving to enter the StartDate and EndDate for each
Report, can you suggest how to bundle this so that I enter the date
parameters once?

Thanks,

Bill

As long as the ParamForm remains open, any query that refers to it
will take it's parameters from it.

Just open the ParamForm from the 1st report's Open event, as indicated
in my previous reply. Code the final report's Close event to close the
ParamForm, also as shown previously.
In each of the report's record source query, enter the same criteria,
i.e. Between forms!ParamForm!StartDate and forms!ParamForm!EndDate.

Then, when you run the reports, the first report will open the form.
Enter the dates, hit the command button, and the first report will
print and close (but the form is still open). The next reports will
use the same parameter data. Finally, when the last report runs and
closes, it closes the ParamForm.

 

[BillH]

As long as the ParamForm remains open, any query that refers to it
will take it's parameters from it.

Just open the ParamForm from the 1st report's Open event, as indicated
in my previous reply. Code the final report's Close event to close the
ParamForm, also as shown previously.
In each of the report's record source query, enter the same criteria,
i.e. Between forms!ParamForm!StartDate and forms!ParamForm!EndDate.

Then, when you run the reports, the first report will open the form.
Enter the dates, hit the command button, and the first report will
print and close (but the form is still open). The next reports will
use the same parameter data. Finally, when the last report runs and
closes, it closes the ParamForm.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail- Hide quoted text -

- Show quoted text -

I tried as suggested but each Report prompts me for Start and End
Dates. Just so I am clear, I tried entering =PARAMFORM to On Open for
the 1st report and =PARAMFORM to On Close for the last Report. I
also tried =PARAMFORM entered in both On Open and On Close for each
Report.
In my trial, I limited myself to only 2 Reports but again was prompted
for each Report for Start and End Dates.

Thanks for your help,

Bill

 

[fredg]

I tried as suggested but each Report prompts me for Start and End
Dates. Just so I am clear, I tried entering =PARAMFORM to On Open for
the 1st report and =PARAMFORM to On Close for the last Report. I
also tried =PARAMFORM entered in both On Open and On Close for each
Report.
In my trial, I limited myself to only 2 Reports but again was prompted
for each Report for Start and End Dates.

Thanks for your help,

Bill

No, you are not clear.
Where in any of my previous replies in this thread have I written
anything like "=ParamForm"?

Let's begin all over again.

Create an unbound form.
Add 2 unbound text controls.
Set their format to a valid date format.
Name them "StartDate" and "EndDate"

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

Me.Visible = False

Name this form 'ParamForm'.
Close and save the form.

In each Query that is a Report's Record Source, as criteria in the
query date field write:

Between forms!Paramform!StartDate and forms!ParamForm!EndDate

Next, click on the event tab of the first Report's property sheet.
On the Open event line, 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 written lines of code.
Between those 2 lines, write:

DoCmd.OpenForm "ParamForm", , , , , acDialog

Exit the code window and save the report changes.

Open the last report to be run in design view.

Code this report's Close event (using the same method as you did in
the first report's Open event):

DoCmd.Close acForm, "ParamForm"

Close and save this report.

When ready to run the reports, open the first report.
The form will open and wait for the entry of the starting and ending
dates wanted.
Click the command button and the report will print.
Run each additional report.

When the last report closes, it will close the form.

i hope this helps.

 

[BillH]

I tried as suggested but each Report prompts me for Start and End
Dates. Just so I am clear, I tried entering =PARAMFORM to On Open for
the 1st report and  =PARAMFORM  to On Close for the last Report. I
also tried =PARAMFORM entered in both On Open and On Close  for each
Report.
In my trial, I limited myself to only 2 Reports but again was prompted
for each Report for Start and End Dates.

Thanks for your help,

Bill

No, you are not clear.
Where in any of my previous replies in this thread have I written
anything like "=ParamForm"?

Let's begin all over again.

Create an unbound form.
Add 2 unbound text controls.
Set their format to a valid date format.
Name them "StartDate" and "EndDate"

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

Me.Visible = False

Name this form 'ParamForm'.
Close and save the form.

In each Query that is a Report's Record Source, as criteria in the
query date field write:

Between forms!Paramform!StartDate and forms!ParamForm!EndDate

Next, click on the event tab of the first Report's property sheet.
On the Open event line, 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 written lines of code.
Between those 2 lines, write:

DoCmd.OpenForm "ParamForm", , , , , acDialog

Exit the code window and save the report changes.

Open the last report to be run in design view.

Code this report's Close event (using the same method as you did in
the first report's Open event):

DoCmd.Close acForm, "ParamForm"

Close and save this report.

When ready to run the reports, open the first report.
The form will open and wait for the entry of the starting and ending
dates wanted.
Click the command button and the report will print.
Run each additional report.

When the last report closes, it will close the form.

i hope this helps.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail- Hide quoted text -

- Show quoted text -

Not trying to be 'thick' but the same problem. The 1st Report is
preceeded by the Start and End Date prompts but the problem is that
the subsequent Reports are prompted for date entries as well.

Bill

 

[BillH]

No, you are not clear.
Where in any of my previous replies in this thread have I written
anything like "=ParamForm"?

Let's begin all over again.

Create an unbound form.
Add 2 unbound text controls.
Set their format to a valid date format.
Name them "StartDate" and "EndDate"

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

Me.Visible = False

Name this form 'ParamForm'.
Close and save the form.

In each Query that is a Report's Record Source, as criteria in the
query date field write:

Between forms!Paramform!StartDate and forms!ParamForm!EndDate

Next, click on the event tab of the first Report's property sheet.
On the Open event line, 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 written lines of code.
Between those 2 lines, write:

DoCmd.OpenForm "ParamForm", , , , , acDialog

Exit the code window and save the report changes.

Open the last report to be run in design view.

Code this report's Close event (using the same method as you did in
the first report's Open event):

DoCmd.Close acForm, "ParamForm"

Close and save this report.

When ready to run the reports, open the first report.
The form will open and wait for the entry of the starting and ending
dates wanted.
Click the command button and the report will print.
Run each additional report.

When the last report closes, it will close the form.

i hope this helps.

- Show quoted text -

Not trying to be 'thick' but the same problem. The 1st Report is
preceeded by the Start and End Date prompts but the problem is that
the subsequent Reports are prompted for date entries as well.

Bill- Hide quoted text -

- Show quoted text -

I found the problem. The lack of clarity was in the use of the Form's
On Click command. With the instruction: "Add a Command Button to the
form. Code the button's click event:Me.Visible = False", I placed this
command in the Event On Click line. Instead, on this line, an [Event
Procedure] should have been used to add the above Code.

Thank you,

Bill

 

[BillH]

37:30 -0800 (PST), BillH wrote:
On Jan 15, 12:51 pm, Robert T <[email protected]>
wrote:
Fred:
That sounds perfect, exactly what I was looking for.
Thanks so much, I'll let you know if I come across any problems.
Robert
:- Hide quoted text -
- Show quoted text -
This worked terrific - many thanks. I need to run several Reports in 1
macro. Without ahving to enter the StartDate and EndDate for each
Report, can you suggest how to bundle this so that I enter the date
parameters once?
Thanks,
Bill
As long as the ParamForm remains open, any query that refers to it
will take it's parameters from it.
Just open the ParamForm from the 1st report's Open event, as indicated
in my previous reply. Code the final report's Close event to close the
ParamForm, also as shown previously.
In each of the report's record source query, enter the same criteria,
i.e. Between forms!ParamForm!StartDate and forms!ParamForm!EndDate.
Then, when you run the reports, the first report will open the form..
Enter the dates, hit the command button, and the first report will
print and close (but the form is still open). The next reports will
use the same parameter data. Finally, when the last report runs and
closes, it closes the ParamForm.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail- Hide quoted text -
- Show quoted text -
I tried as suggested but each Report prompts me for Start and End
Dates. Just so I am clear, I tried entering =PARAMFORM to On Open for
the 1st report and  =PARAMFORM  to On Close for the last Report. I
also tried =PARAMFORM entered in both On Open and On Close  for each
Report.
In my trial, I limited myself to only 2 Reports but again was prompted
for each Report for Start and End Dates.
Thanks for your help,
Bill
No, you are not clear.
Where in any of my previous replies in this thread have I written
anything like "=ParamForm"?
Let's begin all over again.
Create an unbound form.
Add 2 unbound text controls.
Set their format to a valid date format.
Name them "StartDate" and "EndDate"
Add a Command Button to the form.
Code the button's click event:
Me.Visible = False
Name this form 'ParamForm'.
Close and save the form.
In each Query that is a Report's Record Source, as criteria in the
query date field write:
Between forms!Paramform!StartDate and forms!ParamForm!EndDate
Next, click on the event tab of the first Report's property sheet.
On the Open event line, 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 written lines of code.
Between those 2 lines, write:
DoCmd.OpenForm "ParamForm", , , , , acDialog
Exit the code window and save the report changes.
Open the last report to be run in design view.
Code this report's Close event (using the same method as you did in
the first report's Open event):
DoCmd.Close acForm, "ParamForm"
Close and save this report.
When ready to run the reports, open the first report.
The form will open and wait for the entry of the starting and ending
dates wanted.
Click the command button and the report will print.
Run each additional report.
When the last report closes, it will close the form.
i hope this helps.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail- Hide quoted text -
- Show quoted text -

Not trying to be 'thick' but the same problem. The 1st Report is
preceeded by the Start and End Date prompts but the problem is that
the subsequent Reports are prompted for date entries as well.

Bill- Hide quoted text -

- Show quoted text -

I found the problem. The lack of clarity was in the use of the Form's
On Click command. With the instruction: "Add a Command Button to the
form. Code the button's click event:Me.Visible = False", I placed this
command in the Event On Click line. Instead, on this line, an [Event
Procedure] should have been used to add the above Code.

Thank you,

Bill- Hide quoted text -

- Show quoted text -

The directions worked perfectly. Often though, I need to enter
'relative' Start and End dates using an expression Between Date()-10
and Date()-1. Your help will be very much appreciated. (I tried
entering these 2 variables into the StartDate and EndDate
respectively, but received an error message indicating that the
formula was too complex).

Thank you,

BillH

 

[BillH]

As long as the ParamForm remains open, any query that refers to it
will take it's parameters from it.

Just open the ParamForm from the 1st report's Open event, as indicated
in my previous reply. Code the final report's Close event to close the
ParamForm, also as shown previously.
In each of the report's record source query, enter the same criteria,
i.e. Between forms!ParamForm!StartDate and forms!ParamForm!EndDate.

Then, when you run the reports, the first report will open the form.
Enter the dates, hit the command button, and the first report will
print and close (but the form is still open). The next reports will
use the same parameter data. Finally, when the last report runs and
closes, it closes the ParamForm.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail- Hide quoted text -

- Show quoted text -

Is it possible to substitute the specific dates with a relative date
grouping for Starting and Ending using for example, the expression
Between Date() and Date() -'x'.

Thanks - WHoward