We help IT Professionals succeed at work.

New podcast episode! Our very own Community Manager, Rob Jurd, gives his insight on the value of an online community. Listen Now!

x

Using Access multi Listbox to pass criteria to a query

HOTWATT
HOTWATT asked
on
528 Views
Last Modified: 2017-04-14
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!
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016

Commented:
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

Author

Commented:
I do not have much VBA experience.
CERTIFIED EXPERT
Top Expert 2016

Commented:
upload a copy of the db..

Author

Commented:
This is the report.
OpenOrder.accdb
CERTIFIED EXPERT
Top Expert 2016

Commented:
where is it?,

Author

Commented:
Sorry forgot to hit upload. It should be there now if you refresh.
CERTIFIED EXPERT
Top Expert 2016
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Works perfectly! Thanks so much I really appreciate the help!
NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
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

Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.