populate table using multiselect listbox

  • Thread starter Thread starter deb
  • Start date Start date
D

deb

Access 2003

I have a main form called f001ProjectReview (PK ProjectID).
There is a subform called f022Shipping(PK ShipID, SK ProjectID) with a
simple multiselect listbox called lstIncoTerm. The listbox can have a
multiple choice selection.

When the user selects one or more items in the listbox, how do I get the
table called t12ShipIncoTerms to update with the ShipID and lstIncoTerms for
each selection in the listbox?

Thanks in advance!
 
When the user selects one or more items in the listbox, how do I get the
table called t12ShipIncoTerms to update with the ShipID and lstIncoTerms for
each selection in the listbox?
Click to expand...

You'll need some VBA code to do it. Here's some sample code from my animal
shelter database that you may be able to adapt.

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, 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 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
 
Back
Top