Multiselect listbox in Access 2010

Question

So I have this search form that filters projects based on the designer type. The designer can be internal, external, or combination. It was originally done using a combobox. I was asked to make it so that the user can filter by multiple options, so I changed it to a listbox with the idea of using the multiselect option.

The listbox works fine, until I set the multiselct option to anything besides none. Then, all the projects are returned instead of the ones that are selected.

I'm not very experienced in Access or VBA so any assistance would be greatly appreciated. The following is the code for this section.

Private Sub toDesignerExcel_Click()
Dim db As DAO.Database
Dim rs1, rs2 As DAO.Recordset
Dim sSQL1, sSQL2, SourceExcel, FileName, Path As String


Set db = CurrentDb


sSQL1 = "Select ExcelPath from tblBackendFiles where Setting = 'ExcelDesignerParameters'"
sSQL2 = "Select Setting from tblBackendFiles where Code = 'SourceExcel'"

Set rs1 = db.OpenRecordset(sSQL1)
FileName = Nz(rs1!ExcelPath, "")

Set rs2 = db.OpenRecordset(sSQL2)
SourceExcel = Nz(rs2!Setting, "")

Path = SourceExcel + "\" + FileName

rs1.Close
rs2.Close
db.Close
Set rs1 = Nothing
Set rs2 = Nothing
Set db = Nothing

Shell "C:\WINDOWS\explorer.exe """ & Path, vbNormalFocus
End Sub

EDIT: And also,

If Forms(formName).txtDesigner <> "" And Not IsNull(Forms(formName).txtDesigner) Then
   If selEngConditions <> "" Then
        selEngConditions = selEngConditions & " AND "
   End If
        selEngConditions = selEngConditions & "[Activity].[GWPDesigner] = '" &    Forms(formName).txtDesigner & "'"
End If

I am not seeing anywhere your listbox. Where does that come into your code? — parakmiakos
Yes, but you don't use it anywhere in the code above to filter your results. — parakmiakos
I'm not really sure how to do that... It doesn't look like the combobox was coded in either. — Mechanical Notaprogrammer

parakmiakos parakmiakos · Accepted Answer · 2014-06-18T13:42:42

I would do something like this :

Private Sub toDesignerExcel_Click()
Dim db As DAO.Database
Dim rs1, rs2 As DAO.Recordset
Dim sSQL1, sSQL2, SourceExcel, FileName, Path As String

Set db = CurrentDb
sSQL1 = "Select ExcelPath from tblBackendFiles where Setting = 'ExcelDesignerParameters' And [Tblbackendfiles] = '"
sSQL2 = "Select Setting from tblBackendFiles where Code = 'SourceExcel'  And [Tblbackendfiles] = '"

For Each it In Me.ListBoxName.ItemsSelected

    Set rs1 = db.OpenRecordset(sSQL1 & it & "'")
    FileName = Nz(rs1!ExcelPath, "")

    Set rs2 = db.OpenRecordset(sSQL2 & it & "'")
    SourceExcel = Nz(rs2!Setting, "")
    Path = SourceExcel + "\" + FileName

    Shell "C:\WINDOWS\explorer.exe """ & Path, vbNormalFocus

    rs1.Close
    rs2.Close
    Set rs1 = Nothing
    Set rs2 = Nothing

Next it

db.Close
Set db = Nothing

End Sub

Where you basically iterate over all the selected items in the listbox. This adds each selected item from the listbox in the where clause of the query.

You can also try concatenating all the values and changing the query to something like :

And [Tblbackendfiles] In (" & comma_separated_list & ")

Multiselect listbox in Access 2010

1 Answers