Select Combo Box to Open Reports

[VBABeginner3]

I can't get a select combo box to open reports upon the click of a button. Here's what I have. What do you suggest? It's opening the report Inventory only when nothing is clicked but I can't get it to open the other reports.

Private Sub Run_Inventory_Report_Macro_Click()
Dim strSortBy As String
strSortBy = Nz(Me.cmb_InventorySort.Value, "")
Select Case strSortBy
Case ""
DoCmd.OpenReport "Inventory", acViewPreview
Case "Provider ID"
DoCmd.OpenReport "Inventory-Provider Number", acViewPreview
Case "Provider Last Name"
DoCmd.OpenReport "Inventory-Provider Last Name", acViewPreview
Case "Inventory Type"
DoCmd.OpenReport "Inventory-Inventory Type", acViewPreview
Case "Corporate Receipt Date"
DoCmd.OpenReport "Inventory-Corporate Receipt Date", acViewPreview
Case "PODM Receipt Date"
DoCmd.OpenReport "Inventory-PODM Receipt Date", acViewPreview
End Select
End Sub

 

[Minty]

Add a debug statement and open in the debugging window - press Ctrl+g to get the immediate window up, what is in your string?

Private Sub Run_Inventory_Report_Macro_Click()

    Dim strSortBy        As String
    
    strSortBy = Nz(Me.cmb_InventorySort, "")
    Debug.Print "Your String " & strSortBy & " ;"
    Select Case strSortBy
    Case ""
        DoCmd.OpenReport "Inventory", acViewPreview
    Case "Provider ID"
        DoCmd.OpenReport "Inventory-Provider Number", acViewPreview
    Case "Provider Last Name"
        DoCmd.OpenReport "Inventory-Provider Last Name", acViewPreview
    Case "Inventory Type"
        DoCmd.OpenReport "Inventory-Inventory Type", acViewPreview
    Case "Corporate Receipt Date"
        DoCmd.OpenReport "Inventory-Corporate Receipt Date", acViewPreview
    Case "PODM Receipt Date"
        DoCmd.OpenReport "Inventory-PODM Receipt Date", acViewPreview
    End Select
    
End Sub

 

[pbaldy]

Are those all basically the same report but with different sorts? If so I'd have a single report and change the sorting as it opens.

 

[VBABeginner3]

Minty,
It says, "your String Provider Last Name." It's also highlighted on DoCmd.OpenReport "Inventory-Provider Last Name", acViewPreview. On the front it keeps asking me for a field parameter. It shouldn't be doing that.

pbaldy,
My counterparts would like it automated.

 

[pbaldy]

Oh I meant automated. Here's a simple example, in the open event of a report. It changes the sort from a numeric driver number field to the name field:

  If Forms!frmReportCriteria.fraSort = 1 Then
    Me.GroupLevel(0).ControlSource = "Driver"
  Else
    Me.GroupLevel(0).ControlSource = "FullName"
  End If

 

[VBABeginner3]

pbaldy,
I am new to VBA. Does the sub go before If and does end sub go after end if? I assume GroupLevel is the name of the report and frmReportCriteria is the name of the button. Is that correct?

Thanks

 

[pbaldy]

The code would go in the open event of the report:

http://www.baldyweb.com/FirstVBA.htm

frmReportCriteria is the name of my form, fraSort the name of the control being tested. In my case it's an option group, in yours it would be the name of your combo. GroupLevel is a built-in object of the report.

 

[VBABeginner3]

Ok, thanks pbaldy.

The issue was in the report layout. I changed a field so I had to go into the report and add the new field name.

 

[pbaldy]

Glad you got it working.