using a form to supply parameters to a querry/report

[DrDoIT]

Hello Forum,

I have been trying for several days to develope a form to supply parameters to a query and a report in my database.

the sample code in the form's On open Event procedure as as below :

Option Compare Database
Private Sub Ok_Click()
Me.Visible = False
DoCmd.OpenQuery "711ARTData Query", acViewNormal, acEdit
DoCmd.Close acForm, "Parameter Form"
End Sub
Private Sub Form_Open(Cancel As Integer)
End Sub
​

The form is named "Parameter Form" and the query "711ARTData Query" and this portion works well.

I would very much like for the form to pass the same user parameters (Facility Name, Starting Month, Ending Month, Starting Year and Ending Year) to the report developed from the query but i have not been successful.

My VBA knowledge is quite limited.

Kindly assist,


DrDoIT.

 

[pbaldy]

You would just open the report instead of the query. You should also leave the form open (it can be hidden).

 

[nanscombe]

As pbaldy said, you could try ...

Parameter Form Code

Private Sub Ok_Click()
  Me.Visible = False
  DoCmd.OpenReport "yourReportName", acViewPreview
End Sub

yourReportName Code

Private Sub Report_Close()
  Forms("Parameter Form").visible = TRUE
End Sub

 

[DrDoIT]

thanks Paul and Nigel,

your suggestions were very helpful, the form works much better.

one problem though, a 'Enter Parameter Value' Dialog box appears first with the following display text :

Lookup_FacilityName.FacilityName

when i enter (or dont enter) any value and click OK, the report displays correctly.
the field "FacilityName" in the query has the criteria :

[Forms]![Parameter Form]![Combo2]

1. How do i make this message disappear?

2. since the parameters that the users supply are basically the same (Facility name, and reproting periods) is it possible to use the same parameter form to supply user inputs to the other queries and reports?

Thank you,

DrDoIT

 

[pbaldy]

If the query runs without the prompt, that value has been entered somewhere in the report. Could be in a textbox or in Sorting and Grouping among other places.

I typically use a single form for any number of queries/reports.

 

[DrDoIT]

Hello Paul, Hello Forum,

Sadly i have little progress to report on my wish for one (or just a handful) of parameter forms. some of the reports i have are named as follows :
1. HTC SOC - Quality Assurance
2. 711TB Standard Report
3. 731PMTCT Standard Report
etc.

how do i use one Parameter form to open all these and any other reports? Do I make a Combo box with a list of all the reports in my database and how do i reference the specific report i would like the parameter form to open amongst a list of several reports?

Kind regards,

DrDoIT

 

[pbaldy]

That sort of boils down to the look and feel you want the db to have. You can use a combo to select the report, or buttons, etc. It/they can be on the parameter form or not. Typically mine aren't, and I use OpenArgs to pass the name of the report to be opened to the parameter form.

 

[DrDoIT]

thanks,

i think i will try the command buttons.

DrDoIT

 

[pbaldy]

No problem; post back if you get stuck.