Access Form - run query against table based on listbox selection

I have a table called HRData that includes a field called MRC

I have a form that has a listbox that sources the unique values for MRC an allows for one selection. If I want to use VBA to execute a query against the entire table and filter on MRC I am running into issues trying to reference the selection the value from the Listbox.

so base query would be

SELECT * FROM HRData
WHERE [MRC] = 'The selection in the list box'

How would this need to be adjusted if:

1. The listbox was muliti select and I need an IN statement instead.

SELECT * FROM HRData
WHERE [MRC] In ('Selecton 1' , 'Selection 2'... etc.

2. I wanted to filter against more than just MRC but also other fields like "Country" or "Job Function" that also have list box choices.

Any guidance would be greatly appreciated.
Adam EhrenworthLead Technology AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nick67Commented:
Listboxes are a little funny.
When a choice is selected, you have a .ItemsSelected collection of Variants
(even when you are dealing with a single-select listbox)
So, with a single select listbox, the picked items is
Me.TheNameOfYourListBox.ItemsSelected(0)

The displayed data in the box is gotten after with
Me.TheNameOfYourListBox.Column(TheColumnNumber,TheRowNumber)

So, something like this gets it done

Dim db As Database
Dim rs  As Recordset
Dim strRecordSource As String
Dim MyItem As Variant
Set db = CurrentDb

For Each MyItem In Me.TheNameOfYourListBox.ItemsSelected
    strRecordSource = "SELECT * FROM HRData  WHERE [MRC] = " & Me.TheNameOfYourListBox.Column(1, MyItem)
    Set rs = db.OpenRecordset(strRecordSource, dbOpenDynaset, dbSeeChanges)
    If rs.RecordCount = 0 Then
        MsgBox Me.TheNameOfYourListBox.Column(1, MyItem) & " failed to return a useful filter!"
    End If
continue:
Next MyItem

Open in new window


The columns and rows number from zero.
I assumed you had a hidden column in column zero of nice things you don't want users to see and went with column 1.

You use the same idea for columns 2,3,4...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nick67Commented:
At the end of the day, you are building a SQL string in VBA.
To use it as a filter for a form or report, it is the WHERE clause without the WHERE.

Generically
Me.Filter = "SomeNumberField" = Me.SomeSyntaxThatYieldsANumber
Me.Filter  = Me.Filter & " AND SomeStringField = "  & chr(34) & Me.SomeSyntaxThatYieldsAString & Chr(34)
Me.Filter  = Me.Filter & " AND SomeDateField = #"   & Me.SomeSyntaxThatYieldsADateTime & "#"
0
PatHartmanCommented:
1. I've attached a sample that should help.  The third item on the menu is the sample you want.

2. Since you are already building the SQL with VBA, you might as well include the other search fields also.

Where FirstField In(1,2,6) AND SecondField In("a", "b", "c") AND ThirdField Between #1/1/16# AND #1/31/16#;

Here's a code sample from a form that contains multiple selection criteria 
Public Sub BuildSQL()
Dim strSQL As String
Dim strSelect As String
Dim strSelectForExport As String
Dim strWHERE As String
Dim strCondition As String
Dim strOrderBy As String

    strSelect = "Select * from qClientList "
    strSelectForExport = "Select * from qClientListExport "
    strOrderBy = " ORDER BY FullName"
    strWHERE = ""
    
    If Me.txtFirstName & "" = "" Then
    Else
        strWHERE = "FirstName Like " & QUOTE & Me.txtFirstName & "*" & QUOTE
    End If
    If Me.txtLastName & "" = "" Then
    Else
        strCondition = "LastName Like " & QUOTE & Me.txtLastName & "*" & QUOTE
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    If Me.txtPhone & "" <> "" Then
        strCondition = "(Phone = '" & Me.txtPhone & "' OR CellPhone = '" & Me.txtPhone & "')"
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    If Me.chkT19 = True Then
        strCondition = "T19 = True"
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    Select Case Me.fraStatus
        Case 1
            strCondition = "StatusID = 1318"          'Open
        Case 2
            strCondition = "StatusID = 1319"          'Closed
        Case 4
            strCondition = "StatusID = 1427"          'Pending
        Case Else
            strCondition = ""
    End Select
    If strWHERE = "" Then
        If strCondition = "" Then
        Else
            strWHERE = strCondition
        End If
    Else
        If strCondition = "" Then
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    If strWHERE = "" Then
        strSQL = strSelect
    Else
        strSQL = strSelect & " WHERE " & strWHERE
    End If

    strSQL = strSQL & strOrderBy
    Me.txtQuery = strSQL

Open in new window

FillFormFields130127.zip
0
Adam EhrenworthLead Technology AnalystAuthor Commented:
Thanks to all - i was able to piece the report together with the code provided. Have a much better understanding of how to iterate through the list box to get all values.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.