Saving multiselected list box items to a table

[cpath34]

I'm using Access 2003. I have a form with a multi select list box. I want the
user's selections to be save to a table. I've change the MultiSelect property
to simple; however, it does not save the data to the table.

Please help!

 

[Douglas J. Steele]

In order to save the value, you have to bind the list box to a field in the
form's underlying recordsource. You set the ControlSource property of the
list box to the appropriate field.

You cannot bind multi-select list boxes.

 

[pietlinden]

I'm using Access 2003. I have a form with a multi select list box. I wantthe
user's selections to be save to a table. I've change the MultiSelect property
to simple; however, it does not save the data to the table.

Please help!

I don't think you can bind a listbox to a table.

This way works:

I'm assuming you have a listbox on your form named "List0" with the
multi-select property set to Extended.
In my case, I created a SQL string to do the inserts for me, because I
can't bind the listbox directly to a column in a table.

Private Sub cmdSaveSelected_Click()
Dim strInsertSQL As String
Dim varItem As Variant

For Each varItem In Me.List0.ItemsSelected

strInsertSQL = "INSERT INTO t_lbxWriteHere(SomeValue) VALUES
('" & Me.List0.ItemData(varItem) & "')"
DBEngine(0)(0).Execute strInsertSQL, dbFailOnError

Next varItem

End Sub

You could also do this with recordsets, if you wanted.

Private Sub cmdShowSelected_Click()

Dim rs As DAO.Recordset 'to add values to table
Dim varItem As Variant

Set rs = DBEngine(0)(0).OpenRecordset("t_lbxWriteHere",
dbOpenTable, dbAppendOnly)

For Each varItem In Me.List0.ItemsSelected
rs.AddNew
rs.Fields(0) = Me.List0.ItemData(varItem)
rs.Update
Next varItem

rs.Close
Set rs = Nothing

End Sub

Yes, the both need error trapping...

 

[John W. Vinson]

I'm using Access 2003. I have a form with a multi select list box. I want the
user's selections to be save to a table. I've change the MultiSelect property
to simple; however, it does not save the data to the table.

Please help!

You may be able to adapt this code which I wrote for an animal-shelter
applircation:

Private Sub cmdProcess_Click()
' Comments : Update the AnimalCondition table based on the selections in
' the unbound multiselect listbox lstHealthIssues.
' Newly selected rows will be added to the table, newly
' cleared rows will be deleted.
' Parameters: None
' Modified : 01/29/02 by JWV
'
' --------------------------------------------------
' Populate the AnimalCondition table with the selected issues
On Error GoTo PROC_ERR

Dim iItem As Integer
Dim lngCondition As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset

' save the current record if it's not saved
If Me.Dirty = True Then
Me.Dirty = False
End If
Set db = CurrentDb
' Open a Recordset based on the table
Set rs = db.OpenRecordset("AnimalCondition", dbOpenDynaset)
With Me!lstHealthIssues
' Loop through all rows in the Listbox
For iItem = 0 To .ListCount - 1
lngCondition = .Column(0, iItem)
' Determine whether this AnimalID-HealthID combination is
' in the table
rs.FindFirst "[AnimalID] = " & Me.AnimalID & " AND " _
& "[HealthIssueID] = " & lngCondition
If rs.NoMatch Then ' this item has not been added
If .Selected(iItem) Then
' add it
rs.AddNew
rs!AnimalID = Me.AnimalID
rs!HealthIssueID = lngCondition
rs.Update
End If ' if it wasn't selected, ignore it
Else
If Not .Selected(iItem) Then
' delete this record if it's been deselected
rs.Delete
End If ' if it was selected, leave it alone
End If
Next iItem
End With
rs.Close
Set rs = Nothing
Set db = Nothing
Me.subAnimalCondition.Requery

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & " in cmdProcess_Click:" _
& vbCrLf & Err.Description
Resume PROC_EXIT

End Sub

 

[cpath34]

The recordset code you have is very similar to the code I've tried; however,
it's getting hung up at

Set rs = DBEngine(0)(0).OpenRecordset("t_lbxWriteHere",
dbOpenTable, dbAppendOnly)

 

[pietlinden]

The recordset code you have is very similar to the code I've tried; however,
it's getting hung up at

"t_lbxWriteHere" is the name of the table I wrote the data to. You
need to substitute the name of your table.

 

[cpath34]

Yes I understand that my table name went there; however, it's not accepting
the code.

Cpath34
Knowledge is Power!
Patience is a virtue.