Solved

Access Form - run query against table based on listbox selection

Posted on 2016-07-18

Microsoft Access

2 solutions

55 Views

Last Modified: 2016-07-19

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.

Comment

Question by:aehrenwo

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

4 Comments

LVL 26

Accepted Solution

by:Nick67

Nick67 earned 250 total points

ID: 417178722016-07-18

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...

LVL 26

Expert Comment

by:Nick67

ID: 417178792016-07-18

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 & "#"

LVL 37

Assisted Solution

by:PatHartman

PatHartman earned 250 total points

ID: 417194502016-07-19

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

Author Closing Comment

by:aehrenwo

ID: 417194872016-07-19

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.

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Delivering a Word Template for Use in Merging Data from Access

Article by: Helen

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…

Show pictures directly from URLs in Access forms and reports

Article by: Gustav

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…

Microsoft Access

MS Access – Writing Solid VBA Code

Video by: Jim

In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

Microsoft Access

How To Make a Graph with Microsoft Access

Video by: crystal

Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…