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

x
?
Solved

Access Form - run query against table based on listbox selection

Posted on 2016-07-18
4
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.
0
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
  • 2
4 Comments
 
LVL 26

Accepted Solution

by:
Nick67 earned 1000 total points
ID: 41717872
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
 
LVL 26

Expert Comment

by:Nick67
ID: 41717879
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
 
LVL 39

Assisted Solution

by:PatHartman
PatHartman earned 1000 total points
ID: 41719450
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
 

Author Closing Comment

by:aehrenwo
ID: 41719487
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

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

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

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question