Select Combo Box to Open Reports

Question

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

geeFlo geeFlo · Accepted Answer · 2017-04-19T18:38:25

What are the values in your cmb_InventorySort combo box? Better method than the case might be to use a lookup table to return the report name you want to open.

i.e. ReportList

ID  |  ReportName
Inv |  Inventory
Provider ID | Inventory - Provider Number

Your combo box would use the above table as it's RowSource.

Then after they've selected an item from the combobox (or after clicking a button) then you can lookup the report they've selected, and you won't need to hardcode the case statement. i.e. you can change/add/remove report names from the table as you wish.

StrReport = dlookup("ReportName","ReportList", "ID = '" & me!cmb_InventorySort & "'"

docmd.openreport strReport, acViewPreview

Select Combo Box to Open Reports

2 Answers