Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

Solved

Access Form - run query against table based on listbox selection

Posted on 2016-07-18

Microsoft Access

2 solutions

Medium Priority

68 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 1000 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 39

Assisted Solution

by:PatHartman

PatHartman earned 1000 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

Splitting Microsoft Access Database with Multiple User

Article by: Jason

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…

Dynamic Printer Selection for Access

Article by: Helen

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …

MS Access – Cascading Combos

Video by: Jim

In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

Microsoft Access

Access Desktop Databases – A Quick Tour

Video by: Jim

What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.