Update Table based on Multiple List Box selections.

[TitaniaTiO2]

Background:
I have the following tables/queries:

tblPerson
PersonID
LastName
FirstName
JobTitle
EmploymentStatus

tblDocument
DocumentNumber
DocumentTitle

tblTraining
TrainingID
PersonID
DocumentNumber
TrainingDate
HistoryTrainedTo
TrainingStatus

qryAssignSOPsToEmployee
DocumentNumber
DocumentTitle
LastName

I would like to have a form where I can use a combo box to select a LastName
and a List box where I can select multiple DocumentNumbers to be assigned to
that LastName.

I have the list box. I have the combo box. I have a close button. How do
I create all the record when I close the form?

Thanks

Titania

 

[Steve Sanford]

Hi Titania,

Given your tables, I think you would be creating the records in
"tblTraining". If so, try the following code.

'-------- code beg ------------------
Private Sub Form_Close()
On Error GoTo ErrorHandler

Dim rs As DAO.Recordset
Dim ctl As Control
Dim varItem As Variant
Dim strSQL As String
Dim i As Integer
Dim WasAdded As Boolean

'you need to change Form2 to the name of your form
' you need to change List0 to the name of your list box
Set ctl = Forms!Form2.List0

WasAdded = False
' open a recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblTraining")

'loop thru the items in the list box
For i = 0 To ctl.ListCount - 1
If ctl.Selected(i) Then
rs.AddNew
rs!PersonID = Me.Combo2
rs!DocumentNumber = ctl.Column(0, i)
rs.Update
ctl.Selected(i) = False 'clears the selection
WasAdded = True
End If
Next i

If WasAdded Then
MsgBox "Done"
End If

Exit_Here:
On Error Resume Next
rs.Close
Set rs = Nothing
Exit Sub

ErrorHandler:
MsgBox Err.Number & vbCrLf & Err.Description
Resume Exit_Here


End Sub
'-------- code end ------------------

HTH

 

[TitaniaTiO2]

PERFECT!

THANK YOU!

Hi Titania,

Given your tables, I think you would be creating the records in
"tblTraining". If so, try the following code.

'-------- code beg ------------------
Private Sub Form_Close()
On Error GoTo ErrorHandler

Dim rs As DAO.Recordset
Dim ctl As Control
Dim varItem As Variant
Dim strSQL As String
Dim i As Integer
Dim WasAdded As Boolean

'you need to change Form2 to the name of your form
' you need to change List0 to the name of your list box
Set ctl = Forms!Form2.List0

WasAdded = False
' open a recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblTraining")

'loop thru the items in the list box
For i = 0 To ctl.ListCount - 1
If ctl.Selected(i) Then
rs.AddNew
rs!PersonID = Me.Combo2
rs!DocumentNumber = ctl.Column(0, i)
rs.Update
ctl.Selected(i) = False 'clears the selection
WasAdded = True
End If
Next i

If WasAdded Then
MsgBox "Done"
End If

Exit_Here:
On Error Resume Next
rs.Close
Set rs = Nothing
Exit Sub

ErrorHandler:
MsgBox Err.Number & vbCrLf & Err.Description
Resume Exit_Here


End Sub
'-------- code end ------------------

HTH

 

[Steve Sanford]

You're welcome