Cannot use ListBox values in MS Access 2007 to pass to query

Question

Good morning,

I am helping to develop an interface via a Form in MS Access. We have a list box with various user values and the user should be able to select multiple values in the ListBox and then press the button to execute a query, returning only the rows whose Car Name is what was selected.

UPDATE - thanks to some great feedback on this forum, the primary issue was resolved. My secondary issue is now not being able to execute the query. When I try, I get the error that the query cannot be executed.

My code (as event procedure) for the button is:

Option Explicit
Private Sub btnSearchCars_Click()
    MsgBox "Starting Sub"
    Call QueryCars.myQuery
    MsgBox "Ending Sub"
End Sub

Then, my QueryCars module looks like this:

Sub myQuery()
    Dim strWhere As String
    Dim strSQL As String
    Dim varItem As Variant

    For Each varItem in Forms!FormSelect!listCarID.SelectedItems
        strWhere = strWhere & "'" & Forms!FormSelect!listCarID.ItemData(varItem) & "',"
    Next

strWhere = Left(strWhere, Len(strWhere) -1)

    strSQL = "SELECT tblBig.* FROM tblCars INNER JOIN tblBig ON tblCars.Car_ID = tblBig.Car_ID WHERE tblCars.Car_ID IN (" & strWhere & ");"
    DoCmd.RunSQL strSQL

End Sub

My error is an "A RunSQL requires an argument of an SQL statement" error on the line.

DoCmd.RunSQL strSQL

I would really appreciate it if someone could help. All I am trying to do is take the values from the list box the user selects and use them as WHERE criteria in my query. I have searched various SO and Access forums all morning and have not found anything to help.

Thank you. Please let me know if you have any questions.

strWhere seems not well constructed, 1st you have twice the items added, 2nd you have a , at the end that need to be removed. — Vincent G
– Vincent G, Commented Jun 26, 2018 at 14:26
@VincentG thank you for the feedback. I made those changes but am still getting the same error. — artemis
– artemis, Commented Jun 26, 2018 at 14:28
ThemyForm declaration is wrong. It should be As Access.Form By using Option Explicit at the top of each module, you should be able to pick those errors at compile time.. — Kostas K.
– Kostas K., Commented Jun 26, 2018 at 14:31
Hi @KostasK. thank you for your response. Unfortunately, that did not fix my error, though I have adjusted the code. — artemis
– artemis, Commented Jun 26, 2018 at 14:34
How can we know that you "have added an additional function to remove the trailing ','" if you don't tell us? — Vincent G
– Vincent G, Commented Jun 26, 2018 at 15:25

Darren Bartrup-Cook · Accepted Answer · 2018-06-27 13:51:52Z

1

This isn't the perfect answer I was hoping to give you - but can't figure out how to use parameter queries in an IN command.

I'll assume that your listbox contains two columns of data and the CarID values are in the first column.

The main function is called ProcessQuery and accepts a reference to the listbox as an argument:
Public Sub ProcessQuery(myList As ListBox)

You can then call your code from the event on the listbox and pass it the listbox reference.

Private Sub btnSearchCars_Click()
    ProcessQuery Me.listCarID
End Sub

The ProcessQuery procedure then looks at the first column to get the index numbers, constructs the SQL, opens the resulting recordset and pulls the info from each record.

Public Sub ProcessQuery(myList As ListBox)

    Dim vItem As Variant
    Dim IDList As String
    Dim qdf As dao.QueryDef
    Dim rst As dao.Recordset

    For Each vItem In myList.ItemsSelected
        'Column 0 is first column in listbox.
        IDList = IDList & "'" & myList.Column(0, vItem) & "',"
    Next vItem
    'Removes the final ,
    IDList = Left(IDList, Len(IDList) - 1)

    'Create a temporary query definition & open the recordset.
    Set qdf = CurrentDb.CreateQueryDef("", _
        "SELECT tblBig.* FROM tblCars INNER JOIN tblBig ON tblCars.Car_ID = tblBig.Car_ID WHERE tblCars.Car_ID IN (" & IDList & ")")
    Set rst = qdf.OpenRecordset

    'Move through the recordset and output the first two fields from each record
    'to the Immediate window.
    With rst
        If Not (.BOF And .EOF) Then
            .MoveFirst
            Do While Not .EOF
                Debug.Print .Fields(0) & " - " & .Fields(1)
                .MoveNext
            Loop
        End If
    End With

End Sub

To display the query result as a datasheet you could use the following, but I'd prefer to use a stored query with a parameter for the IN. I'll try and figure that bit out.

Public Sub ProcessQuery(myList As ListBox)

    Dim vItem As Variant
    Dim IDList As String
    Dim qdf As dao.QueryDef
    Dim rst As dao.Recordset

    For Each vItem In myList.ItemsSelected
        'Column 0 is first column in listbox.
        IDList = IDList & "'" & myList.Column(0, vItem) & "',"
    Next vItem
    'Removes the final ,
    IDList = Left(IDList, Len(IDList) - 1)

    'Create a temporary query definition & open the recordset.
    Set qdf = CurrentDb.CreateQueryDef("TempQDF", _
        "SELECT tblBig.* FROM tblCars INNER JOIN tblBig ON tblCars.Car_ID = tblBig.Car_ID WHERE tblCars.Car_ID IN (" & IDList & ")")

    DoCmd.OpenQuery "TempQDF", acViewNormal

End Sub

edited Jun 27, 2018 at 13:51

answered Jun 26, 2018 at 15:27

Darren Bartrup-Cook

20.2k2 gold badges27 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

14 Comments

artemis Over a year ago

Hi @Darren Bartrup-Cook, thank you so much for your reply! I just went ahead and spent the last 30 minutes implementing your idea, as well as trying to learn what you were doing and Googling terms I was unfamiliar with. I do have one problem. It runs great with no errors, but, nothing happens...meaning no records return anywhere. I have added in some MsgBoxs to show that the function runs, which it does, but nothing actually populates. Do you have any advice?

Darren Bartrup-Cook Over a year ago

A few things to check - 1. step through the code using F8. Does it execute the code within the If Not (.BOF And .EOF) Then block - if not then it's a good sign the query hasn't returned anything. 2. With the code paused after IDList = Left(IDList, Len(IDList) - 1) open the Immediate window (Ctrl+G to open in the VBE) and enter

?"SELECT tblBig.* FROM tblCars INNER JOIN tblBig ON tblCars.Car_ID = tblBig.Car_ID WHERE tblCars.Car_ID IN (" & IDList & ")"

. Create a query using the SQL and see if it returns anything.

Darren Bartrup-Cook Over a year ago

3. Open the Locals window and keep an eye on the variables as you step through the code - is IDList getting populated with the correct values?

artemis Over a year ago

Hi @Darren Batrup-Cook. Thanks a lot for your help. Unfortunately I cannot step through the code. Pressing F8 does nothing, and when I click "Run -> Step Into", Access prompts me to look for a macro and this is saved as a Module. Would you advise I create a formal macro?

artemis Over a year ago

Hi @Darren Bartrup-Cook. Again, thanks a lot for being so patient with me. I feel like I've been living on SO and UtterAccess trying to configure this thing. I stepped through the code and the IDList variable fills out appropriately, the If Not Then block is looped through appropriately as well. I also took your advice and copied the SQL out of the VBE, and when I do, it runs perfectly. (FYI - it is in quotes in the VBE, and I needed to remove the quotes...should it be in the VBE EXACTLY how it is run?)

|

Zack · Accepted Answer · 2018-06-26 18:01:07Z

0

I would suggest first taking a look at the actual WHERE clause being generated...keep a separate string variable to store it, and then dump it to the Immediate Window when it's generated.

I would also suggest creating a separate function to return values selected in a list box as an array. Something like:

Public Function getListBoxSelection(ctl As Access.ListBox) As Variant
   Dim arr() As Variant
   Dim varItem As Variant, i As Long

   If ctl.ItemsSelected.Count > 0 Then
      ReDim arr(0 To ctl.ItemsSelected.Count - 1)
      i = 0

      For Each varItem In ctl.ItemsSelected
         arr(i) = ctl.ItemData(varItem)
         i = i + 1
      Next varItem

   End If

   getListBoxSelection = arr
End Function

Then, you would call it in SQL generation. Something like

whereClause = join(getListBoxSelection(me.listCarID), " AND ")
debug.Print whereClause

qdf.SQL = _
    "select tblBig.* " & _
    "from tblCars " & _
    "inner join tblBig on tblCars.Cat_ID = tblBig.Car_ID " & _
    "where tblCars.Card_ID in (" & whereClause & ")"

answered Jun 26, 2018 at 18:01

Zack

2,3501 gold badge10 silver badges13 bronze badges

1 Comment

artemis Over a year ago

Hey @Zack. I can go ahead and make these changes. Thanks for suggesting them. My current problem is, even when I run the Debug.Print OR run the DoCmd.OpenQuery, nothing actually pops up or happens...despite me not getting any errors.

Collectives™ on Stack Overflow

Cannot use ListBox values in MS Access 2007 to pass to query

2 Answers 2

14 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

14 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related