Report skips through form with parameters

[Basenji]

I am using Access 2003. A form, frmLocationDates, has been created with a
combo box, Location, with 4 locations and a text box for a start date,
StartDate, and a text box for an end date, EndDate. This is the code for the
event procedure: Private
Sub Report_Close()
DoCmd.Close acForm, "frmLocationDates"
End Sub

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "frmLocationDates"
End Sub

When I click to open the report in preview mode, it opens the form for the
parameters and then immediately opens the report in preview mode not leaving
any time to enter the parameters and thus the report comes back with errors.
What am I missing? When I run the query by itself it returns the correct data.

Thank you.

 

[fredg]

I am using Access 2003. A form, frmLocationDates, has been created with a
combo box, Location, with 4 locations and a text box for a start date,
StartDate, and a text box for an end date, EndDate. This is the code for the
event procedure: Private
Sub Report_Close()
DoCmd.Close acForm, "frmLocationDates"
End Sub

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "frmLocationDates"
End Sub

When I click to open the report in preview mode, it opens the form for the
parameters and then immediately opens the report in preview mode not leaving
any time to enter the parameters and thus the report comes back with errors.
What am I missing? When I run the query by itself it returns the correct data.

Thank you.

You need to open the form in acDialog.
DoCmd.OpenForm "frmLocationDates", , , , , acDialog

Have a command button on the form to click after the parameter values
have been entered. Code it's click event:
Me.Visible = False

When the form is opened, report processing will wait for the entry of
the parameters and the clicking of the form command button. Then when
the report closes, your close event code will also close the form.

 

[Basenji]

I like your approach, but I am missing something. I have the form with the
criteria, location combo box and text boxes for start and end dates. I added
a command button to preview the report after the criteria is entered. The
report returns error messages. Am I missing something in regards to the
underlying query of the report?

Thank you.

 

[Basenji]

I have struck out on this approach as well. I added the acDialog so the form
opens. I added a command button using the wizard to add a command button to
print preview the report. Where does the Me.Visible = false go in this code?
I also put Me.Visible = false in the click event line in the properties and
then it wants a macro. So I am getting error messages on the report.

Thank you for your assistance.

 

[Duane Hookom]

You didn't explain "report returns error messages" very well. Did you remove
the code from the report? It shouldn't be needed if the form is already open.

 

[John Spencer]

On your parameter form add a button that sets that form's visible property to
NO (False). This will hide the form, but keep it open

VBA code for the button's click event would look like.
Me.Visible = False

Then in your report's close event you can use code to close the parameter form.

DoCmd.Close AcForm, "WhateverYouHaveNamedTheParameterForm"



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Basenji]

I cleared the code out. Now the end user of the database will be able to
click on a command button for a specific report, enter the criteria and the
report appears in print preview. This way seems much easier than dealing with
code.

Thank you for your assistance.

 

[fredg]

I have struck out on this approach as well. I added the acDialog so the form
opens. I added a command button using the wizard to add a command button to
print preview the report. Where does the Me.Visible = false go in this code?
I also put Me.Visible = false in the click event line in the properties and
then it wants a macro. So I am getting error messages on the report.

Thank you for your assistance.

You have miss-understood the procedure involved.
No need to use the wizard when adding the command button.

Open the Form in design view. Click on View + Code.
Delete everything between the Private Sub etc...()
and End Sub.
Then write:

Me.Visible = False

between thiose 2 lines. Nothing else

When done the event procedure should look just like this:

Private Sub Commandname_Click()
Me.Visible = False
End Sub

ComamndName should be whatever the actual command button name is.

The report is opened first. It's Open event opens the form in dialog.
After you enter the parameter data into the form the command button is
clicked. It makes the form not visible. The report query recordsource
then processes the form parameters and the report displays. When the
Report is closed the Report's Close event closes the form.