Combo box to filter results of query?

[Etoimos]

I'm trying to creat a report that the user has to select a value from a combo box to generate the data for the report.



I have created a simple form with just a single combo box that pulls a list of Events from the TestEvents table. I then created a query that pulls data from my RunResultData table. On of the fields from that table, is Event. For that field, I gave it the Criteria of [Forms]![Rpt Event Lookup]![Combo0]. That is the table and combo box name I created in the simple form.

Whenever I run the query, instead of getting a combo box to select the event from, I get a popup box wanting me to type something in.

What have I messed up?

[pbaldy]

The query won't open the form, it has to be open already. Typically the report would be triggered from that form.

[Etoimos]

The query won't open the form, it has to be open already. Typically the report would be triggered from that form.

How do I trigger the report from a form?

[pbaldy]

Look at OpenReport, either in a macro or in VBA code (my preference).

[ArviLaanemets]

An example of command button event from one of my apps

Private Sub comRepGroup1_Click()
On Error GoTo Err_comRepGroup1_Click

    Dim stDocName As String

    Select Case Me.cbbRepGroup1Rep ' a report is selected based on value of combo in form
    Case "Date-Contract-OutlayCode"
        stDocName = "repLeasePaymentsOutlayPer"
    Case "Date-OutlayCode-Contract"
        stDocName = "repLeasePaymentsContractPer"
    Case "LeasedDevices"
        stDocName = "repLeasedDevices"
    End Select
    
    Select Case Me.cbbRepGroup1Dest ' Report output type is selected based on value of combo in form
    Case "Preview"
        DoCmd.OpenReport stDocName, acPreview
    Case "Print"
        DoCmd.OpenReport stDocName, acNormal
    Case "Print to file"
        DoCmd.OutputTo acReport, stDocName
    Case "*.xls"
        Select Case stDocName
        Case "repLeasePaymentsOutlayPer"
            DoCmd.TransferSpreadsheet acExport, 8, "qLeasePaymPer", "LeasePayments.xls", True, ""
        Case "repLeasePaymentsContractPer"
            DoCmd.TransferSpreadsheet acExport, 8, "qLeasePaymPer", "LeasePayments.xls", True, ""
        Case "repLeasedDevices"
            DoCmd.TransferSpreadsheet acExport, 8, "qLeasedDevices", "LeasedDevices.xls", True, ""
        End Select
    End Select

Exit_comRepGroup1_Click:
    Exit Sub

Err_comRepGroup1_Click:
    MsgBox Err.Description
    Resume Exit_comRepGroup1_Click
    
End Sub

Then one where querystring is composed accordingly filter conditions determined through form controls

Private Sub comRepGroup2_Click()
On Error GoTo Err_comRepGroup2_Click


    Dim stDocName As String


    Select Case Me.cbbRepGroup2Rep
    Case "OutlayCode-Department-Group-User"
        stDocName = "repUserDevices"
    End Select
    
    Select Case Me.cbbFilterField
...
    Case "OutlayCode"
        Me.Application.CurrentDb.QueryDefs("qUserDevices").SQL = _
            "SELECT [qUserDevices1].[QueryDate], [qUserDevices1].[DeviceID], ... FROM qUserDevices1 WHERE ((([qUserDevices1].[OutlayCode])= " & Chr$(39) & Me.cbbFilterValue & Chr$(39) & "));"
...
    End Select
    
    Select Case Me.cbbRepGroup2Dest
    Case "Preview"
        DoCmd.OpenReport stDocName, acPreview
    Case "Print"
        DoCmd.OpenReport stDocName, acNormal
    Case "Print to file"
        DoCmd.OutputTo acReport, stDocName
    Case "*.xls"
        Select Case stDocName
        Case "repUserDevices"
            DoCmd.TransferSpreadsheet acExport, 8, "qUserDevices", "UserDevices.xls", True, ""
        End Select
    End Select


Exit_comRepGroup2_Click:
    Exit Sub


Err_comRepGroup2_Click:
    MsgBox Err.Description
    Resume Exit_comRepGroup2_Click


End Sub

And one (another app), where filter condition is set using WHERE clause of report (info about current record of active form is reported)

Private Sub cmdViewChemCard_Click()
On Error GoTo Err_cmdViewChemCard_Click


    Dim stDocName As String
    Dim sWhereCondition As String


    sWhereCondition = "ChemID = " & Me.ChemID
    stDocName = "repChemCard"
    DoCmd.OpenReport stDocName, acPreview, , sWhereCondition


Exit_cmdViewChemCard_Click:
    Exit Sub


Err_cmdViewChemCard_Click:
    MsgBox Err.Description
    Resume Exit_cmdViewChemCard_Click
    
End Sub

[Etoimos]

Thanks for pointing me in the right direction guys. I was able to get it to work by assigning the code below to the button on my form:

'User must select a value from the combo box
   If IsNull(cboSelectEvent) = True Then
      MsgBox "You must select an Event."
   'Open report called EventSystemPerformance_rpt where the Event field
   ' contains the value in the cboSelectEvent combo box
   Else
      DoCmd.OpenReport "EventSystemPerformance_rpt", acViewPreview, , "Event like '*" & cboSelectEvent & "*'"
   End If

Is there a way to have the report displayed already zoomed in or so that it fills the whole right side of the screen where the different tabbed tables/forms are displayed? Perhaps acViewPreview is not the correct choice for that?

[Etoimos]

Is there a way to have the report displayed already zoomed in or so that it fills the whole right side of the screen where the different tabbed tables/forms are displayed? Perhaps acViewPreview is not the correct choice for that?

I managed to figure out how to make the report results to appear as Full Screen which is what I was after for the most part. To do this, open the report in Design View. On the form's Property Sheet, select the Other tab and set Pop Up to Yes. Now, on the Event Tab, in the On Open property add the following VBA code:

DoCmd.Maximize

That should make the report open in a full screen view whenever it is opened. Maybe this will help someone in the future.

[pbaldy]

Sorry, I didn't realize that's what you were asking for. I've got that line right after OpenReport most of the time. I don't think I've ever bothered with the popup setting for a report.

[Etoimos]

Sorry, I didn't realize that's what you were asking for. I've got that line right after OpenReport most of the time. I don't think I've ever bothered with the popup setting for a report.

No worries at all. I'll play around with the Popup and see what it does or does not do for me.