I've written some code that does exactly what you want. Just writing the values into a table is pretty simple. You can just use the listbox.ListCount property, which gives you the number of items selected. You can then use a 0 based count to identify which item was selected by using the listbox.itemdata(counter) to write the data into your table. The tricky part comes when the user deselects an item that was previously selected. There's also the matter of populating the listbox with the existing records when the form loads. The way that I addressed this problem was to create an array that contained the old values that were selected and then compared the newly selected values to determine if a selected item was a new addition to the table, or if an item that was previously selected was deselected. I copied this code out of a more reusable module and deleted the extra parts, so I won't guarentee that this code will run perfectly, but you can get the idea of the process for doing this using dao recordsets. It's from a database I designed to track patients and their symptoms for a clinical research project.
Hope this helps.
Matt Weiner
Private Sub Select_Old(boxname As String)
Dim TB As DAO.Recordset
Dim SQLstmt As String
Dim Item, count As Integer
Set currdb = CurrentDb
SQLstmt = "SELECT symptomName FROM tblsymptoms Where patientID = " & currpatient & ""
Erase strOldSymptom
count = symptombox.ListCount - 1
Set TB = currdb.OpenRecordset(SQLstmt)
Do While Not TB.EOF 'for each symptom that the patient has in the database
'Scroll through each member of symptom box, and if it was previously selected and is registered in the
'database, make it selected
For Item = 0 To count
If symptombox.ItemData(Item) = TB!SymptomName Then
symptombox.Selected(Item) = True
strOldSymptom(Item) = TB!SymptomName
End If
Next Item
TB.MoveNext
Loop
TB.Close
Set TB = Nothing
End Sub
Private Sub Update_Box
Dim i As Integer
Dim SQLstmt, strsearch As String
Dim TB As DAO.Recordset
Dim condition1, condition2 As Boolean
SQLstmt = "SELECT * from tblsymptoms where PatientID = " & currpatient & ""
counter = symptombox.ListCount - 1
Set TB = currdb.OpenRecordset(SQLstmt)
For i = 0 To counter
condition1 = symptombox.Selected(i) = True And strOldSymptom(i) = ""
condition2 = symptombox.Selected(i) = False And strOldSymptom(i) = symptombox.ItemData(i)
If condition1 Then 'The item was not selected, but now is
TB.AddNew
TB!PatientID = currpatient
TB!SymptomName = symptombox.ItemData(i)
TB.Update
ElseIf condition2 Then 'The user deselects an item that was previously selected
strsearch = "SymptomName = """ & strOldSymptom(i) & """"
TB.FindFirst (strsearch)
TB.Delete
End If
Next i
TB.Close
Set TB = Nothing
End Sub
