Report Menu

[deejay_totoro]

Hello all,

I have a question concerning the preview of reports - based on a user selection.

So far, this is what I have done.

I created a blank form. On that form I created a drop-down combo box. This combo references a table that contains the exact names of the reports I have already created.

To preview the report, the user clicks on a "Preview Report" button. The combo selection is passed to the button code as follows:

Option Compare Database

Private Sub buttonPreviewReport_Click()
On Error GoTo Err_buttonPreviewReport_Click

Dim stDocName As String

'ReportName is received from the combo selection
stDocName = ReportName

DoCmd.OpenReport stDocName, acPreview

Exit_buttonPreviewReport_Click:
Exit Sub

Err_buttonPreviewReport_Click:
MsgBox Err.Description
Resume Exit_buttonPreviewReport_Click

End Sub

This appears to work fine. The problem now comes when, for example, I want the underlying query for each report to receive some user parameters, for example, datestart, dateend, or a report based on a particular user name.

So how do I go about this? How do I send information based on a combo to the underlying query before it is made?

Thank you very much!

dj_T

 

[dcx693]

If you want some parameters to be passed to the query, this is no different than basing the query on some parameters from the form. You can still reference controls on the form from the query using criteria in the form of: Forms!myform!mycontrol.

If you want to have all the parameters in the same combo box as the report names, then you can include those in separate hidden or visible columns of the combo box and use the .Column(x) property of the combo box to get at them directly.

You can also use the "filter" and the "where" parameters of the DoCmd.OpenReport method to specify conditions. And if you're using Access 2002+, you can use the OpenArgs parameter.

 

[deejay_totoro]

All

Ah yes - I see.

I have been able to reference a control from my query (using [Forms]![fromName]![ControlName]) and this works fine.

But, if the user doesnt select any item from the menu (or an item such as ALL) how can I show all the records?

I tried leaving the control blank - but the report only produces a blank report.

How can I make the report include all records?

thank you!

dj_T

 

[dcx693]

No prob, use this as the criteria:
[Forms]![fromName]![ControlName] Or Like [Forms]![fromName]![ControlName] Is Null to return all records if the user does not choose a value from the control.

 

[deejay_totoro]

Great!

Thats really super - it works like a treat!

One other minor question - how might I allow all the records to appear if the user selects an entry such as "All Records"?

Thank you very much!

dj_T