Report Date Form -- Multiple reports

  • Thread starter Thread starter jwr
  • Start date Start date
J

jwr

I have a report date form (created when I used Access template for Order
Entry Database). This form appears as a pop-up form on my sales by
customer.

How do I use the same form to work the same way with another report?

Thanks
 
I have a report date form (created when I used Access template for Order
Entry Database). This form appears as a pop-up form on my sales by
customer.

How do I use the same form to work the same way with another report?

Thanks
Click to expand...

You can incorporate your existing date Form. Just don't forget to
change the code below to that of your existing form name and it's date
entry controls.

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

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

Me.Visible = False

Name this form 'ParamForm'.

In each query that is the record source for a report, in it's Date
field's criteria line, write:
Between forms!ParamForm!StartDate AND forms!ParamForm!EndDate

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

Code the each 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.
 
Fred --

Thanks for your quick reply. I attempted what you suggested.

I created an unbound form with 2 fields, Start Date and End Date.

When I added a command button, I was uncertain as to what to select.

I did add Me.Visible=False for the click event property. and named the form
ParamForm.

I changed a query with the DoCmd statements.

I got the following error --

Cannot find macro or macro group DoCmd. It is or was not saved.

What do I need to do now? Maybe I misunderstood you. I do not know how to
edit the command button if I need to change something.

Thank you,
Joy
 
On Mon, 8 Aug 2005 16:20:58 -0500, jwr wrote:

Read my comments in line...
Fred --

Thanks for your quick reply. I attempted what you suggested.

I created an unbound form with 2 fields, Start Date and End Date.

When I added a command button, I was uncertain as to what to select.

I did add Me.Visible=False for the click event property. and named the form
ParamForm.
Click to expand...

No, This is not where it goes.

** To write the code, here is what you do.

On the Command button's Click 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 existing lines of code.
Between those two lines, write:
Me.Visible = False

Exit the code window. Save the changes.
I changed a query with the DoCmd statements.

I got the following error --

Cannot find macro or macro group DoCmd. It is or was not saved.
Click to expand...

I have no idea what you are doing here.
What is the record source of your report?
1) It is probably a query.
What you are supposed to do is simply open that query in design view.
On the main database folder, click on Queries.
Then select your query and open it in Design View.

Or ...
2) It may be that the report record source is an SQL statement.
Open the report in design view. Display it's property sheet.
Click on the Data tab.
The Record source is the first property item.
If it says something like
"Select etc .....
Click on the button with the 3 dots on that line.
Then...

Or...
3) If the record source is a table, then create a new query with all
the fields you want to show in the report.
Then ...

Then.... on the Date Fields criteria line, write:

Between forms!ParamForm!StartDate AND forms!ParamForm!EndDate
What do I need to do now? Maybe I misunderstood you. I do not know how to
edit the command button if I need to change something.
Click to expand...

When you then code the report's Open and Close events as indicated in
the other post, you use the same method as above in the command button
click event. Write [Event Procedure] on the Open line, click the 3 dot
button, write the code, and exit.
Do the same on the Close event line.
 
I changed the info.

My report is based on a query. I added the query info -- Between
forms!ParamForm!StartDate AND forms!ParamForm!EndDate
-- under payment date column for criteria.

On the ParamForm the command button between the 2 lines of code in event
procedure, I entered
Me.Visible = False

The report has the correct info under open and close -- event procedure
-DoCmd.OpenForm "ParamForm"
-DoCmd.Close acForm, "ParamForm"

When I run the report, the ParamForm pops up and I enter the dates. I do
not get an error message; however, I do not get any information. When I run
the query, I get the correct information.

Please advise. Sorry, I am not very knowledgeable in Access.

Thanks,
fredg said:
On Mon, 8 Aug 2005 16:20:58 -0500, jwr wrote:

Read my comments in line...
Fred --

Thanks for your quick reply. I attempted what you suggested.

I created an unbound form with 2 fields, Start Date and End Date.

When I added a command button, I was uncertain as to what to select.

I did add Me.Visible=False for the click event property. and named the form
ParamForm.
Click to expand...

No, This is not where it goes.

** To write the code, here is what you do.

On the Command button's Click 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 existing lines of code.
Between those two lines, write:
Me.Visible = False

Exit the code window. Save the changes.
I changed a query with the DoCmd statements.

I got the following error --

Cannot find macro or macro group DoCmd. It is or was not saved.
Click to expand...

I have no idea what you are doing here.
What is the record source of your report?
1) It is probably a query.
What you are supposed to do is simply open that query in design view.
On the main database folder, click on Queries.
Then select your query and open it in Design View.

Or ...
2) It may be that the report record source is an SQL statement.
Open the report in design view. Display it's property sheet.
Click on the Data tab.
The Record source is the first property item.
If it says something like
"Select etc .....
Click on the button with the 3 dots on that line.
Then...

Or...
3) If the record source is a table, then create a new query with all
the fields you want to show in the report.
Then ...

Then.... on the Date Fields criteria line, write:

Between forms!ParamForm!StartDate AND forms!ParamForm!EndDate
What do I need to do now? Maybe I misunderstood you. I do not know how to
edit the command button if I need to change something.
Click to expand...

When you then code the report's Open and Close events as indicated in
the other post, you use the same method as above in the command button
click event. Write [Event Procedure] on the Open line, click the 3 dot
button, write the code, and exit.
Do the same on the Close event line.
Thank you,
Joy
Click to expand...
Click to expand...
 
I changed the info.

My report is based on a query. I added the query info -- Between
forms!ParamForm!StartDate AND forms!ParamForm!EndDate
-- under payment date column for criteria.

On the ParamForm the command button between the 2 lines of code in event
procedure, I entered
Me.Visible = False

The report has the correct info under open and close -- event procedure
-DoCmd.OpenForm "ParamForm"
-DoCmd.Close acForm, "ParamForm"

When I run the report, the ParamForm pops up and I enter the dates. I do
not get an error message; however, I do not get any information. When I run
the query, I get the correct information.

Please advise. Sorry, I am not very knowledgeable in Access.

Thanks,
fredg said:
On Mon, 8 Aug 2005 16:20:58 -0500, jwr wrote:

Read my comments in line...
Fred --

Thanks for your quick reply. I attempted what you suggested.

I created an unbound form with 2 fields, Start Date and End Date.

When I added a command button, I was uncertain as to what to select.

I did add Me.Visible=False for the click event property. and named the form
ParamForm.
Click to expand...

No, This is not where it goes.

** To write the code, here is what you do.

On the Command button's Click 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 existing lines of code.
Between those two lines, write:
Me.Visible = False

Exit the code window. Save the changes.
I changed a query with the DoCmd statements.

I got the following error --

Cannot find macro or macro group DoCmd. It is or was not saved.
Click to expand...

I have no idea what you are doing here.
What is the record source of your report?
1) It is probably a query.
What you are supposed to do is simply open that query in design view.
On the main database folder, click on Queries.
Then select your query and open it in Design View.

Or ...
2) It may be that the report record source is an SQL statement.
Open the report in design view. Display it's property sheet.
Click on the Data tab.
The Record source is the first property item.
If it says something like
"Select etc .....
Click on the button with the 3 dots on that line.
Then...

Or...
3) If the record source is a table, then create a new query with all
the fields you want to show in the report.
Then ...

Then.... on the Date Fields criteria line, write:

Between forms!ParamForm!StartDate AND forms!ParamForm!EndDate
What do I need to do now? Maybe I misunderstood you. I do not know how to
edit the command button if I need to change something.
Click to expand...

When you then code the report's Open and Close events as indicated in
the other post, you use the same method as above in the command button
click event. Write [Event Procedure] on the Open line, click the 3 dot
button, write the code, and exit.
Do the same on the Close event line.
Thank you,
Joy
On Mon, 8 Aug 2005 12:07:23 -0500, jwr wrote:

I have a report date form (created when I used Access template for Order
Entry Database). This form appears as a pop-up form on my sales by
customer.

How do I use the same form to work the same way with another report?

Thanks

You can incorporate your existing date Form. Just don't forget to
change the code below to that of your existing form name and it's date
entry controls.

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

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

Me.Visible = False

Name this form 'ParamForm'.

In each query that is the record source for a report, in it's Date
field's criteria line, write:
Between forms!ParamForm!StartDate AND forms!ParamForm!EndDate

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

Code the each 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.
Click to expand...
Click to expand...

What's happening is that the form is opening but processing isn't
waiting. The report runs without waiting for the query.

You left out part of the code in the Report's Open event.

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

The acDialog part stops everything until you enter the dates and click
the command button. Make sure you enter the 5 commas, as above.
Click to expand...
 
This is the information on the report event procedures:

Private Sub Report_Close()
DoCmd.Close acForm, "ParamForm"
End Sub

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "ParamForm", , , , , acDialog
End Sub

Joy

fredg said:
I changed the info.

My report is based on a query. I added the query info -- Between
forms!ParamForm!StartDate AND forms!ParamForm!EndDate
-- under payment date column for criteria.

On the ParamForm the command button between the 2 lines of code in event
procedure, I entered
Me.Visible = False

The report has the correct info under open and close -- event procedure
-DoCmd.OpenForm "ParamForm"
-DoCmd.Close acForm, "ParamForm"

When I run the report, the ParamForm pops up and I enter the dates. I do
not get an error message; however, I do not get any information. When I run
the query, I get the correct information.

Please advise. Sorry, I am not very knowledgeable in Access.

Thanks,
fredg said:
On Mon, 8 Aug 2005 16:20:58 -0500, jwr wrote:

Read my comments in line...
Fred --

Thanks for your quick reply. I attempted what you suggested.

I created an unbound form with 2 fields, Start Date and End Date.

When I added a command button, I was uncertain as to what to select.

I did add Me.Visible=False for the click event property. and named the form
ParamForm.

No, This is not where it goes.

** To write the code, here is what you do.

On the Command button's Click 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 existing lines of code.
Between those two lines, write:
Me.Visible = False

Exit the code window. Save the changes.

I changed a query with the DoCmd statements.

I got the following error --

Cannot find macro or macro group DoCmd. It is or was not saved.

I have no idea what you are doing here.
What is the record source of your report?
1) It is probably a query.
What you are supposed to do is simply open that query in design view.
On the main database folder, click on Queries.
Then select your query and open it in Design View.

Or ...
2) It may be that the report record source is an SQL statement.
Open the report in design view. Display it's property sheet.
Click on the Data tab.
The Record source is the first property item.
If it says something like
"Select etc .....
Click on the button with the 3 dots on that line.
Then...

Or...
3) If the record source is a table, then create a new query with all
the fields you want to show in the report.
Then ...

Then.... on the Date Fields criteria line, write:

Between forms!ParamForm!StartDate AND forms!ParamForm!EndDate

What do I need to do now? Maybe I misunderstood you. I do not know
Click to expand...
how
to
edit the command button if I need to change something.

When you then code the report's Open and Close events as indicated in
the other post, you use the same method as above in the command button
click event. Write [Event Procedure] on the Open line, click the 3 dot
button, write the code, and exit.
Do the same on the Close event line.


Thank you,
Joy
On Mon, 8 Aug 2005 12:07:23 -0500, jwr wrote:

I have a report date form (created when I used Access template for Order
Entry Database). This form appears as a pop-up form on my sales by
customer.

How do I use the same form to work the same way with another report?

Thanks

You can incorporate your existing date Form. Just don't forget to
change the code below to that of your existing form name and it's date
entry controls.

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

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

Me.Visible = False

Name this form 'ParamForm'.

In each query that is the record source for a report, in it's Date
field's criteria line, write:
Between forms!ParamForm!StartDate AND forms!ParamForm!EndDate

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

Code the each 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.
Click to expand...
Click to expand...

What's happening is that the form is opening but processing isn't
waiting. The report runs without waiting for the query.

You left out part of the code in the Report's Open event.

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

The acDialog part stops everything until you enter the dates and click
the command button. Make sure you enter the 5 commas, as above.
Click to expand...
 
One other thought --

When I created the ParamForm, and added a command button -- I had to say
what that button was to do. I selected "Report" "Preview Report" and was
asked to select which report the form was previewing; i.e. customer report.

Is this the problem?

If this is correct, how do I use the same form for a different report? Or
do I need to create a new form for each report?
Thanks in advance
fredg said:
I changed the info.

My report is based on a query. I added the query info -- Between
forms!ParamForm!StartDate AND forms!ParamForm!EndDate
-- under payment date column for criteria.

On the ParamForm the command button between the 2 lines of code in event
procedure, I entered
Me.Visible = False

The report has the correct info under open and close -- event procedure
-DoCmd.OpenForm "ParamForm"
-DoCmd.Close acForm, "ParamForm"

When I run the report, the ParamForm pops up and I enter the dates. I do
not get an error message; however, I do not get any information. When I run
the query, I get the correct information.

Please advise. Sorry, I am not very knowledgeable in Access.

Thanks,
fredg said:
On Mon, 8 Aug 2005 16:20:58 -0500, jwr wrote:

Read my comments in line...
Fred --

Thanks for your quick reply. I attempted what you suggested.

I created an unbound form with 2 fields, Start Date and End Date.

When I added a command button, I was uncertain as to what to select.

I did add Me.Visible=False for the click event property. and named the form
ParamForm.

No, This is not where it goes.

** To write the code, here is what you do.

On the Command button's Click 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 existing lines of code.
Between those two lines, write:
Me.Visible = False

Exit the code window. Save the changes.

I changed a query with the DoCmd statements.

I got the following error --

Cannot find macro or macro group DoCmd. It is or was not saved.

I have no idea what you are doing here.
What is the record source of your report?
1) It is probably a query.
What you are supposed to do is simply open that query in design view.
On the main database folder, click on Queries.
Then select your query and open it in Design View.

Or ...
2) It may be that the report record source is an SQL statement.
Open the report in design view. Display it's property sheet.
Click on the Data tab.
The Record source is the first property item.
If it says something like
"Select etc .....
Click on the button with the 3 dots on that line.
Then...

Or...
3) If the record source is a table, then create a new query with all
the fields you want to show in the report.
Then ...

Then.... on the Date Fields criteria line, write:

Between forms!ParamForm!StartDate AND forms!ParamForm!EndDate

What do I need to do now? Maybe I misunderstood you. I do not know
Click to expand...
how
to
edit the command button if I need to change something.

When you then code the report's Open and Close events as indicated in
the other post, you use the same method as above in the command button
click event. Write [Event Procedure] on the Open line, click the 3 dot
button, write the code, and exit.
Do the same on the Close event line.


Thank you,
Joy
On Mon, 8 Aug 2005 12:07:23 -0500, jwr wrote:

I have a report date form (created when I used Access template for Order
Entry Database). This form appears as a pop-up form on my sales by
customer.

How do I use the same form to work the same way with another report?

Thanks

You can incorporate your existing date Form. Just don't forget to
change the code below to that of your existing form name and it's date
entry controls.

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

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

Me.Visible = False

Name this form 'ParamForm'.

In each query that is the record source for a report, in it's Date
field's criteria line, write:
Between forms!ParamForm!StartDate AND forms!ParamForm!EndDate

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

Code the each 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.
Click to expand...
Click to expand...

What's happening is that the form is opening but processing isn't
waiting. The report runs without waiting for the query.

You left out part of the code in the Report's Open event.

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

The acDialog part stops everything until you enter the dates and click
the command button. Make sure you enter the 5 commas, as above.
Click to expand...
 
One other thought --

When I created the ParamForm, and added a command button -- I had to say
what that button was to do. I selected "Report" "Preview Report" and was
asked to select which report the form was previewing; i.e. customer report.

Is this the problem?

If this is correct, how do I use the same form for a different report? Or
do I need to create a new form for each report?
Thanks in advance
Click to expand...
*** snipped ***


You do NOT use the form to run the report.
You 'had to say' that because you used the command button wizard.
The next time you want to place your own code, first disable the
command button wizard (by clicking on the wizard's wand (in the
toolbox) so that it is not depressed). Then drag the command button
onto the form.

Delete all of that un-needed code in the command button click event.

The code behind that command button is just this (and nothing else):

Me.Visible = False


You open the Report. The report opens the form.
When you click the form's command button, the form is hidden and the
report displays it's data. When the report closes, it closes the form.
 
Thank you for your assistance and patience. One problem still remains. I
had sent a reply prior to this one. The query displays info correctly, but
report has no data still. I will list my code below:

This is the information on the report event procedures:

Private Sub Report_Close()
DoCmd.Close acForm, "ParamForm"
End Sub

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "ParamForm", , , , , acDialog
End Sub
 
Fred - This was overlooked in your replies. Can you look at the code and
see if I have something incorrect.

Thank you.
Joy
jwr said:
Thank you for your assistance and patience. One problem still remains. I
had sent a reply prior to this one. The query displays info correctly, but
report has no data still. I will list my code below:

This is the information on the report event procedures:

Private Sub Report_Close()
DoCmd.Close acForm, "ParamForm"
End Sub

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "ParamForm", , , , , acDialog
End Sub
Click to expand...
 
Back
Top