How to return the value of listbox to a table

  • Thread starter Thread starter Jologs
  • Start date Start date
You need to OpenRecordset() on the table, and programatically loop through
the ItemsSelected collection of the list box, to AddNew and Update each one.
 
Thanks for the prompt response. Well, I'm not really good yet in VBA,
so I copied codes from the internet just like the one below. It works
when only single record is selected in the listbox. When multiple
records are selected it prompts "Syntax error (comma) in query
expressions '( "selectedvalue", "selectedvalue")'. Where selectedvalues
are the values selected in the listbox.

Dim Criteria As String
Dim ctl As Control
Dim Itm As Variant
Dim strSQL As String


' Build a list of the selections.
Set ctl = Me![lstAssignee]

For Each Itm In ctl.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
Else
Criteria = Criteria & "," & Chr(34) & ctl.ItemData(Itm) &
Chr(34)
End If
Next Itm

If Len(Criteria) = 0 Then
Itm = MsgBox("You must select one or more items in the" & _
" list box!", 0, "No Selection Made")
Exit Sub
End If

strSQL = "INSERT INTO tblTest (DescriptionID,Assignee) VALUES ('" &
txtDescriptionID & "',(" & Criteria & "));"
CurrentDb.Execute strSQL
 
The code you copied is good for criteria. It is no good for adding new
records to a table. To do that you will need to execute the sql string for
each item, inside the loop.
 
Back
Top