CODEWITHSUNDEEP
vbalistms-accessfilter
Misen
Misen

Reputation: 7

Multi select with listbox Microsoft Access

I'm trying to filter a splitform with a listbox but I keep getting the error '3075'.

I used the debug tool and I got:

strSearch = "52185A, 515674B"

Task = "select * from tOrder where ([OrderID] in (52185A,515674B))"

I'm pretty sure I'm missing some quotations, however I couldn't seem to figure out where. They're all short text format.

Option Compare Database

Private Sub cmdSearch_Click() 
Dim varltem As Variant 
Dim strSearch  As String 
Dim Task As String

For Each varltem In Me!LstMatricule.ItemsSelected 
   strSearch = strSearch & "," & Me!LstMatricule.ItemData(varltem) 
Next varltem
If Len(strSearch) = 0 Then 
   Task = "select * from tOrder" 
Else 
   strSearch = Right(strSearch, Len(strSearch) - 1) 
   Task = "select * from tOrder where ((OrderID] in (" & strSearch & "))" 
End If 
DoCmd.ApplyFilter Task 

End Sub

Upvotes: 0

Views: 136

Answers (1)

SunKnight0
SunKnight0

Reputation: 3351

strSearch = strSearch & ",'" & Me!LstMatricule.ItemData(varltem) & "'"

It is difficult to see but basically I added single quotes around each varItem. You want the result to look like this:

strSearch = "'52185A', '515674B'"

Personally I would do this:

strSearch = strSearch & IIf(Len(strSearch) = 0, "", ",") & "'" & Me!LstMatricule.ItemData(varltem) & "'"

It is slightly more expensive computationally if you have more than 2-3 IDs to add but cleaner than removing the starting comma at the beginning later on.

Upvotes: 1

Related Questions