insert multi select Listbox values to table

[Milton via AccessMonster.com]

I have a form with a list box that i am using to multi select values. How
can I insert these multi selected values into a table?

I have a table built, but do not know how to get the values there.

Thanks,
Milton

 

[Kipp Woodard]

Below is code for a button click event.

Begin Code
=============
Private Sub cmdSaveToTable_Click()
Const PROC_NAME As String = "cmdSaveToTable_Click"

Dim vItem As Variant
Dim iColumns As Integer
Dim sSQL As String
Dim oDatabase As Database

On Error GoTo ErrorHandler

If Me.lstUsers.ItemsSelected.Count = 0 Then
Exit Sub
End If

Set oDatabase = CurrentDb

' This assumes that UserID is in the first column of my listbox
' and that UserName is in the second column.
' It also assumes both fields are text.

' Loop the selected items of the ListBox control.
For Each vItem In Me.lstUsers.ItemsSelected
' Build an insert statement for your table.
sSQL = "Insert Into tblUsers (UserID, UserName) Values (" & _
"""" & Me.lstUsers.Column(0, vItem) & """, " & _
"""" & Me.lstUsers.Column(1, vItem) & """)"

' You can put a break here and examine the value of the sSQL string.
' Execute your insert.
oDatabase.Execute sSQL, dbSeeChanges
Next

Cleanup:
Set oDatabase = Nothing

Exit Sub

ErrorHandler:
MsgBox "Error: " & Err.Number & ", " & Err.Description, , Me.NAME & "."
& PROC_NAME

On Error Resume Next

GoTo Cleanup

End Sub
=============
End Code

 

[John W. Vinson]

I have a form with a list box that i am using to multi select values. How
can I insert these multi selected values into a table?

I have a table built, but do not know how to get the values there.

Thanks,
Milton

Try adapting this code, from one of my databases:

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
currently
' 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