Multi Select List Box to a query (1 Viewer)

Kass · Nov 22, 2017

Hi All,

I am trying to use the following code to take data based on selections in a multi select list box to a query in access. I am new to VBA and can't figure out where I have gone wrong. When I make a selection it opens up dialog box asking for the [Location] field so I have to type it in again. Also, when repeating the process it brings up an error.

Code:

Private Sub Command22_Click()
   Dim db As DAO.Database
   Dim qdf As DAO.QueryDef
   Dim varItem As Variant
   Dim strCriteria As String
   Dim strSQL As String

   Set db = CurrentDb()
   Set qdf = db.QueryDefs("Step 3 - Selected Final Fields")
   For Each varItem In Me!List0.ItemsSelected
      strCriteria = strCriteria & "," & Me!List0.ItemData(varItem) & ""
   Next varItem

   If Len(strCriteria) = 0 Then
      MsgBox "You did not select anything.", vbExclamation, "Nothing to find!"
      Exit Sub
   End If

   strCriteria = Right(strCriteria, Len(strCriteria) - 1)
   strSQL = _
      "SELECT* FROM [Step 2 - Final Fields] " & _
      "WHERE [Step 2 - Final Fields].[Location] IN(" & strCriteria & ");"
   qdf.SQL = strSQL

   DoCmd.OpenQuery "Step 3 - Selected Final Fields"
   Set db = Nothing
   Set qdf = Nothing

End Sub

Mark_ · Nov 22, 2017

Before anything else, I would review your naming conventions.

For a control, Command22 is mostly meaningless.
Having queries such as "Step 3 - Selected Final Fields" is not only cumbersome but also does lack meaning without extensive experience exactly what you are trying to use it for.

For the error at hand, I would either use MsgBox or debug.print to show what is currently in your StrSql. This should help narrow down what is not working properly.

Cronk · Nov 22, 2017

Is the list box data text? If so, there are 2 issues with the generated sql

If you had inserted a line in your code

Code:

debug.print strCriteria

you would see the output as somethinglike

Code:

,a,b,c

sql will fail because of the leading comma and also because the text needs single or double quotes around each item
ie

Code:

'a','b','c'

If the list box data is numeric, you still need to remove the leading comma. Use the mid function

Kass · Nov 23, 2017

Thanks Both.

I have found a solution with a slight workaround. Probably not the best way, I will also look at your suggestions to see if I can improve the process. I will come back to you if I have any further issues.

P.S. I think my naming conventions definitely need work.

Multi Select List Box to a query (1 Viewer)

Kass

New member

Mark_

Longboard on the internet

Cronk

Registered User.

Kass

New member

Similar threads

Users who are viewing this thread