Link to home
Create AccountLog in
Avatar of HOTWATT
HOTWATTFlag for United States of America

asked on

Using Access multi Listbox to pass criteria to a query

I don't have much experience with using list boxes. I created a multi list box for a query. I can't seem to figure out how you go about passing criteria from that list box to the query.

I have a field called IM_PROD_CODE in my query. We have different product codes for example AC, AH, AP and so on. I created a list box with all the product codes in them. I would like someone to be able to select multiple product codes and have the report pull up only the product codes they selected.

Any help would be much appreciated!
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

you need to use vba to alter the query definition of the query, based on the item selected from the listbox

how is your vba coding?

post the SQL statement of your query
Avatar of HOTWATT


I do not have much VBA experience.
upload a copy of the db..
Avatar of HOTWATT


This is the report.
Avatar of HOTWATT


Sorry forgot to hit upload. It should be there now if you refresh.
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of HOTWATT


Works perfectly! Thanks so much I really appreciate the help!
Avatar of Norie

Perhaps you could use the WhereCondition argument of DoCmd.OpenReport.
Private Sub Command12_Click()
    On Error GoTo Err_VIEW_Click

    Dim stDocName As String
    Dim arrProducts()
    Dim I As Long

    With List16
        ReDim arrProducts(.ItemsSelected.Count - 1)
        For I = 0 To .ItemsSelected.Count - 1
            arrProducts(I) = .ItemData(I)
        Next I
    End With

    stDocName = "rptDetailByProductCode"
    DoCmd.OpenReport stDocName, acPreview, , "[IM_PRODUCT_CODE] In ('" & Join(arrProducts, "','") & "')"

    Exit Sub

    MsgBox Err.Description
    Resume Exit_VIEW_Click

End Sub

Open in new window