Filter on one or more fields using a dialog box

[jparker1954]

I have a report I want to be able to filter according to to the complete report or by location or or season or by location AND season. I don't want to have to set up the same report using 4 different queries. I'd like to do it by a dialog box, but for some reason, I can't get it to work right. Any help out there? I'm desperate

[pbaldy]

This would be one way, and probably the way I'd go with only 2 fields:

http://access.mvps.org/access/queries/qry0001.htm

[jparker1954]

Thank you, I got the code to work for one field name; however, I have two fields that I want to select or not select. I'm working with a PrintReports box and two combo boxes inside that box to make selections. I'm not getting the instructions in VB correct for that setup however. Any suggestions on what I'm doing wrong?

[pbaldy]

What VB instructions are you using? The technique I posted does not use any VB; it's all in the query.

[jparker1954]

OK, I understand that it's in the query, but what do I need to add to the report so that the form pops up to be filled in before the report is viewed?

[pbaldy]

I usually do the reverse. I first open the form for the user to enter any criteria, then open the report from there. That lets me validate the user input prior to running the report. I have seen methods that open the form from the open event of the report. As long as you open the form in dialog mode, the report will wait for you to close/hide it before it finishes opening.

[jparker1954]

I'd like to to the method from the open event if possible, because I think I could use the same Reports Dialog box with several different reports. Any suggestions on that method. Thanks for you patience and help.

[pbaldy]

Either method can be used for multiple reports. I pass the name of the report to be opened to the criteria form in OpenArgs. Like I said, the key to opening the form from the report is to open it with the acDialog option so the report waits for it. If the report will get the criteria from that form, you have to hide it when done entering criteria (setting Visible to False).

[jparker1954]

I guess I'm just not understanding the procedure. I have a form called a ReportsDialog. It has a combo box to select for Season and a combo box to select for Location - all on one form. It has an OK button and a cancel button. If I click the OK button I want it to apply the filters selected to my HarvestSummary Report, which is run by a HarvestLogQuery with the [Forms]![ReportsDialog]![Season] OR [Forms]![ReportsDialog]![Season]IsNull criteria for the Season field and similar language for the Location field. What I can't get is the exact instructions to link the ReportsDialog form to the HarvestSummary Report so that the filters apply to the report before the report is displayed. Where can I connect the Form to the Report in the Properties? That's what's not happening. Is there any visual example out there that would help me?

[pbaldy]

In the open event of the report:

DoCmd.OpenForm "ReportsDialog", , , , , acDialog

Behind your OK button

Me.Visible = False

You'll have to trap for the error that will happen if you click the cancel button.

[jparker1954]

OK, I'm beginning to think that what I want to do isn't possible. I'm trying to work with the old Access 2003 "Sales Reports Dialog box" in the Northwind sample database. I want to print the Report with the popup form out of that box. Unfortunately, when I put that report name into the "Sales Reports Dialog box" I can call up the right report, but after I select the filters and press "Preview", I get the message "To use this form, you must preview or print the HarvestingSummary report from the Database Window or Design View." Is it not possible to get it to work from the Print reports dialog?

[pbaldy]

I haven't looked that closely at Northwind. Can you post your db?

[jparker1954]

Unfortunately no. Actually I am unable to view my report even if I click on it directly. I get that message "To use this form, you must preview or print the HarvestingSummary report from the Database Window or Design View." So I don't think the Northwind dialog is the problem. I'm still doing something wrong with the report itself

[jparker1954]

OK, when I click on the On Open Button for my HarvestingSummary Report, this is all it says:

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

End Sub

When I open the Form and look at the VB, it says:

Option Compare Database ' Use database order for string comparisons.
Option Explicit ' Requires variables to be declared before they are used.

Private Sub Cancel_Click()
' This code created by Command Button Wizard.
On Error GoTo Err_Cancel_Click
' Close form.
DoCmd.Close
Exit_Cancel_Click:
Exit Sub
Err_Cancel_Click:
MsgBox Err.Description
Resume Exit_Cancel_Click

End Sub

Private Sub OK_Click()
On Error GoTo Err_OK_Click
Dim strMsg As String, strTitle As String
Dim intStyle As Integer

' If Sales by Year report is not being opened for previewing or printing,
' cause an error. (blnOpening variable is true only when report's Open event
' is being executed.)
If Not Reports![HarvestingSummary].blnOpening Then Err.Raise 0

' Hide form.
Me.Visible = False
Exit_OK_Click:
Exit Sub
Err_OK_Click:
strMsg = "To use this form, you must preview or print the HarvestingSummary report from the Database window or Design view."
intStyle = vbOKOnly
strTitle = "Open from Report"
MsgBox strMsg, intStyle, strTitle
Resume Exit_OK_Click
End Sub

[pbaldy]

What is all the code in the open event of the report? Or did they use a macro? You're trying to open the report right, not the form? The form is meant to be opened by the report, and it sounds like you're opening it directly.