Code for Parameter Form

[Huddle]

I currently have a parameter form named "Fm_Commander_Parameter" and a report named "Rpt_Bldgs_by_Commander".



On the parameter form I have a unbound drop down. On the After Update have I this code that opens the report.

Private Sub Cmb_Commander_AfterUpdate()
DoCmd.OpenReport "Rpt_Bldgs_by_Commander", acViewPreview
DoCmd.Close acForm, "Fm_Commander_Parameter"
End Sub

In the Query for the report for the Commander field I have the parameter as [Forms]![Fm_Commander_Parameter]![Cmb_Commander]

I do not know how to write code very well and am unsure this can be done, but what I would like to do is remove the 'After Update' on the form and on 'Open' of the report I would like to write a code to go to the form, allow them to select the commander, then go back to the report query with the selection. This way I could use the Parameter Form with other reports. Below is the code I've come up with that will be place in 'Open' on the report.

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "Fm_Commander_Parameter", acNormal
(I don't know what to enter to keep the form open to enter the data. Without the below command it will run the report but the parameter form stays open.)
DoCmd.Close acForm, "Fm_Commander_Parameter"
End Sub

[pbaldy]

You can actually use the same form doing it your original way. I often use one, using OpenArgs to pass the report to open:


DoCmd.OpenForm "frmReportCriteria", , , , , , "rptCharterActivity"

then in the code of that form:

DoCmd.OpenReport OpenArgs, acViewPreview

You can do it the way you are trying to. Look at help for OpenForm; you want the acDialog argument.

[Huddle]

I am getting this error

Run-time error '2497: the action or method requires a Report Name argument.

This is what I placed in Open of the Rpt_Bldgs_by_Commander

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "Fm_Commander_Parameter", , , , , , "Rpt_Bldgs_by_Commander"
End Sub


This is what I place in After Update of the Fm_Commander_Parameter

Private Sub Cmb_Commander_AfterUpdate()
DoCmd.OpenReport OpenArgs, acViewPreview
End Sub

[pbaldy]

You have it reversed. Let's start with which method you want to use; form opens report, or report opens form?

[Huddle]

I want to open the report, have it open the form, put the data in the form, then show the results of the report after the query runs.

I don't guess it would have to open the report first if I can use the form for multiple reports. Maybe I'm not getting the logic here.

[pbaldy]

For your way the report's open event has:

DoCmd.OpenForm "Fm_Commander_Parameter", , , , , acDialog

The acDialog makes the report wait for the form to be closed or hidden before it continues. Since your query is looking for the form, you want to hide it when the user is done entering criteria:

Me.Visible = False

You can either leave it hidden until you need it again or close it in the report's close event.

[Huddle]

So how do I tell the form to open whichever report I'm working with.

When I enter the below code in the parameter form it tells me I need a report name but that takes me back to square one where I have to have a parameter form for each report.

Private Sub Cmb_Commander_AfterUpdate()
DoCmd.OpenReport OpenArgs, acViewPreview
End Sub

[pbaldy]

OpenArgs was for the other method, where you open the form first, using OpenArgs to tell the form what report you want it to open. In the method you requested, you're already opening the report, and the report is opening the form. The form doesn't need to know what report opened it. Your button on the form would just hide it, which would let the report finish opening.

[Huddle]

Oh I got it. The Me.Visible = False goes on the form not the report.....Thanks

[Huddle]

I've come across another problem. When the parameter form comes up it contains the last selection made. I've tried a requery in the form on load and before update. I've also tried to requery on the report exiting. It causes other problems.

If the previous entry is "Beach" and I want to select Beach again I have to do a drop down selection again. It doesn't read the previous selection even though that it what is in the field. I want to have the form blank when it opens.

[pbaldy]

Is it bound to a table? Mine generally aren't.

[Huddle]

The combo box is connected to a look up table.

[pbaldy]

But does it have anything in the Control Source property? If not, closing/opening the form should blank the combo. If you're only hiding it, you'd need to blank it yourself, probably in the activate event.

[Huddle]

I didn't have a closing code. Fixed that. So below is what I've got.

In the parameter form:
Private Sub Cmb_Commander_AfterUpdate()
Me.Visible = False
End Sub


In the Report:
Private Sub Report_Close()
DoCmd.Close acForm, "Fm_Commander_Parameter", acSaveNo
End Sub
Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "Fm_Commander_Parameter", , , , , acDialog
End Sub

If I want the report to open in a print preview default how do I add that?

[pbaldy]

The default view is a property of the report, but typically in the code that opens the report you specify the desired view. It's one of the arguments of OpenReport.