Appending Values from a Multi-Select List Box

[Guest]

Good day,

A user selects several values in a list box (lstCities). I want to copy each
selection to a table (tblCitiesSelected). Each city must have it's own record
(not one record with a string list of cities).

Any suggestions?

 

[Guest]

Something like

Dim MyDB As DAO.Database
Dim qdef As DAO.RecordSet
Dim i As Integer
Dim flgSelectAll As Boolean
Dim varItem As Variant

Set MyDB = CurrentDb()
Set MyRec = MyDb.OpenRecordset("Select * From tblCitiesSelected")

'Add records by looping through the listbox
For i = 0 To lstCities.ListCount - 1
If lstCities.Selected(i) Then
MyRec.AddNew
MyRec!Cities = lstCities.Column(0, i)
MyRec.Update
End If
Next i

*****
Note: I didn't try it, so I hope no errors will happen
*****

 

[Guest]

Dim varItem As Variant
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("MyTable", dbOpenDynaset)

With Me.MyListBox
For Each varItem In .ItemsSelected
rst.AddNew
rst![CITY] = .ItemData(varItem)
'***Populate other fields here as well***
rst.Update
Next varItem
End With

rst.Close
Set rst = Nothing