multi select list box access 2007

[GirlBlonde]

i am trying to create a multi select list box using the below code. i am storing the occupant into a table for use later in reporting, however i keep getting the following error message

"Error No 3075 description syntax error (missing operator) in query expression 'occupant = 1 car bay'

can anyone see if i am missing anything





Dim strSQL As String
Dim lst As Access.ListBox
Dim varItem As Variant
Dim lngID As String

Set lst = Me![ListOccupant]

'Clear old temp table
DoCmd.SetWarnings False
strSQL = "DELETE * from cboSelectOccupant"
DoCmd.RunSQL strSQL

'Check that at least one contact has been selected


If lst.ItemsSelected.Count = 0 Then
MsgBox "Please select at least one item"
lst.SetFocus
Exit Sub
End If

For Each varItem In lst.ItemsSelected

DoCmd.SetWarnings False

'All information is present; write a record to the temp table
lngID = lst.Column(0, varItem)

Debug.Print "Selected ID: " & lngID

strSQL = "INSERT INTO cboSelectOccupant (occupant) " _
& "SELECT occupant FROM CarParkingList " _
& "WHERE occupant = " & lngID & ";"
DoCmd.RunSQL strSQL
Next varItem


Energy & persistance conquer all things!

 

[PHV]

& "WHERE occupant = '" & lngID & "';"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[GirlBlonde]

hi
thank you that worked. however it does not group the items. eg smith may appear 3 times as there are 3 smiths in the list. is there a way i can group so only 1 appears?

many thanks

Energy & persistance conquer all things!

 

[PHV]

& "SELECT DISTINCT occupant FROM CarParkingList " _

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[GirlBlonde]

thank you so much for your help!

Energy & persistance conquer all things!