Solved

Access Form - run query against table based on listbox selection

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

Accepted Solution

by:
Nick67 earned 250 total points
Comment Utility
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
Comment Utility
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 34

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
Comment Utility
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
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now