Display query parameter in message box

[ArviLaanemets]

The use of a command button is not practical in this case. There are some 30odd reports in the combo boxes. Event though only about 1/3rd of them require date criteria, I don't want a form loaded with command buttons to collect this information.

This is why I adviced to use global variable to store the name of last report user selected from any of report combos. There will be a single command button, which will run the report which name was stored in global variable.

Additionally (for case user forgot which report was selected), the AfterUpdate event of report combo may (along with storing report name in global variable, and setting proper filtering controls visible, and all others invisible) update the Caption property of Command Button to something like "Run Report SelectedReport".

[sheusz]

Hi ArviL

OK, now I understand.

I've started working on the form idea, and have it mostly working, however I've hit a hurdle.

The query underlying the reports has a Yes/No field called "Resovled".

On the report selection form I have a combo box where the user can choose to display ALL records, RESOLVED records or UNRESOLVED records.

When they select RESOLVED a variable is set to "True"

When they select UNRESOLVED a variable is set to "False"

When they select ALL a variable is set with no value - meaning that all records should be displayed.

For testing purposes I have a text box on the form that shows the selection result variable (True, False or nothing). I thought that I could place the value of the text box in the query to display the matching records, but I'm encountering an error.

This is what I have placed in the query

[Forms]![frmPhoneSupportReportData]![txtFilter]

Is this correct?

[ArviLaanemets]

Simplest way:
In AfterUpdate event of Command Button, call your report as

SELECT CASE Me.cbbFilterField
     CASE "All"
          DoCmd.OpenReport "YourReportName" acNormal
     CASE "True"
          DoCmd.OpenReport "YourReportName" acNormal, "Resolved = True"
     CASE "False"
          DoCmd.OpenReport "YourReportName" acNormal, "Resolved = False"
END SELECT

In case you need to set condition for report based on several filtering controls, it is better you save the filter string to string variable. Like

Dim strRepFilter As string
...
SELECT CASE Me.cbbFilterField
     CASE "All"
          strRepFilter = ""
     CASE "True"
          strRepFilter = "Resolved = True"
     CASE "False"
          strRepFilter = "Resolved = False"
END SELECT
SELECT CASE Me.cbbnextFilterField
     ...
END SELECT
 
SELECT CASE strRepFilter
     CASE ""
          DoCmd.OpenReport "YourReportName" acNormal
     CASE OTHER
          DoCmd.OpenReport "YourReportName" acNormal, strRepFilter
END SELECT

[Micron]

I don't think you are getting it. One combo, one command button. Date controls only appear IF the report requires them. One if only one date (e.g. a start date) two if an end and start date and none if none. In fact, you can hide the command button if you want the combo to simply open a report that does not required date inputs. You can also eliminate the command button entirely in cases where only the combo selection can control what happens, but you don't have that situation because some reports require one or more dates.

[davegri]

Here's an example. The Start Date and End Date textboxes will appear/disappear depending on the report selected in the listbox. Reports can have just Start Date, or Start Date and End Date, or No Dates required.
Reports-sheusz-davegri-v01.zip

[sheusz]

Hi Arvil, Micron and Davegri

Thank you all for your replies and patience. I've got it working the way I wanted using this code;

Select Case Me.cmbFilter
     Case "All"
          DoCmd.OpenReport "PhoneCallsSpecifyData", acPreview
     Case "Resolved"
          DoCmd.OpenReport "PhoneCallsSpecifyData", acPreview, , "Resolved = True"
     Case "Unresolved"
          DoCmd.OpenReport "PhoneCallsSpecifyData", acPreview, , "Resolved = False"
End Select

So, after all of that I can now display an error message that means something in the report's NoData event !

Private Sub Report_NoData(Cancel As Integer)
 
    Dim message As String
    message = "Sorry, there is no data to report for the period " & _
    Forms!frmPhoneSupportReportData!txtStartDate & " and " & Forms!frmPhoneSupportReportData!txtEndDate & " for " & Forms!frmPhoneSupportReportData!cmbFilter & " records."
        
    MsgBox message, vbInformation, "No Data"
    Cancel = True
    
End Sub

A huge thank you to Micron and Davegri for your suggestions and samples. As a result, I'm going to totally re-work the whole Reports section of the DB, which will take some doing, but will give me a really nice result.

[sheusz]

Hi Micron

Thank you for all of your help. I did understand what you meant, I was just aiming to solve small problems before I tackled the redesign I'm going to perform based on your suggestions. That's a project for when I return from holidays.!

[sheusz]

Hi Davegri

This is awesome and has given me some great ideas. Thanks for going to the trouble of creating and sharing the code.

The Print Both Sides checkbox has me intrigued!

I'll still use the combo box method for report selection to keep the report selection form tidy.

As I said to Micron, a project for when I return from holidays...

[isladogs]

Perhaps have a look at my two example apps as well. They may help ...
Multiple Group & Filter (isladogs.co.uk)

[sheusz]

Most certainly will.

I'm always amazed by the generosity of people on this forum.

[Micron]

Glad we could help in whatever way we can.

[isladogs]

You're welcome