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!

Microsoft Access

Last Comment

Norie

8/22/2022 - Mon

Rey Obrero (Capricorn1)

4/14/2017

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

HOTWATT

4/14/2017

ASKER

I do not have much VBA experience.

Rey Obrero (Capricorn1)

4/14/2017

upload a copy of the db..

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!

Walt Forbes

HOTWATT

4/14/2017

ASKER

This is the report.
OpenOrder.accdb

Rey Obrero (Capricorn1)

4/14/2017

where is it?,

HOTWATT

4/14/2017

ASKER

Sorry forgot to hit upload. It should be there now if you refresh.

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

4/14/2017

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

HOTWATT

4/14/2017

ASKER

Works perfectly! Thanks so much I really appreciate the help!

Norie

4/14/2017

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_VIEW_Click:
    Exit Sub

Err_VIEW_Click:
    MsgBox Err.Description
    Resume Exit_VIEW_Click

End Sub

Open in new window