Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

multi select list box access 2007

Status
Not open for further replies.

GirlBlonde

Programmer
Oct 30, 2007
94
AU
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!
 
Code:
& "WHERE occupant = '" & lngID & "';"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
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!
 
& "SELECT DISTINCT occupant FROM CarParkingList " _

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
thank you so much for your help!

Energy & persistance conquer all things!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top