Append records to table based upon listbox selected items

[Guest]

I am trying to use a listbox to append multiple records for employee training
when all selected employees have received the same training on the same day.
I've gotten close, but my code is not working correctly. It is appending
multiple records for the same employee, not for each employee selected in the
listbox.

Can someone please look at my code and tell me what I did wrong or what I am
missing?

Arlene

Private Sub cmdAddGroupTraining_Click()
On Error GoTo Err_cmdAddGroupTraining_Click
Dim rst As New ADODB.Recordset
Dim cnn As ADODB.Connection
Dim varNumber As Variant
Dim strEmpId As String
Dim strCourseName As String
Dim dtCompletedDate As String

If IsNull(cboCourseName) Then
MsgBox "You must select a Course from the list.", vbInformation,
"Add Group Training Records"
Exit Sub
ElseIf IsNull(Calendar) Then
MsgBox "You must select a Date on the Calendar.", vbInformation,
"Add Group Training Records"
Exit Sub
End If
If lstEmployees.ItemsSelected.Count = 0 Then
MsgBox "You must select at least 1 employee.", vbInformation, "Add
Group Training Records"
Exit Sub
End If
Set cnn = CurrentProject.Connection
rst.Open "tblTraining", cnn, adOpenStatic, adLockOptimistic,
adCmdTableDirect
For Each varNumber In lstEmployees.ItemData(varNumber)
strEmpId = Forms!frm_GroupTraining!lstEmployees.Column(0)
strCourseName = Forms!frm_GroupTraining!cboCourseName
dtCompletedDate = Forms!frm_GroupTraining!CompletedDate
With rst
.AddNew
!EmpId = strEmpId
!CourseName = strCourseName
!CompletedDate = strCompletedDate
.Update
End With
Next varNumber
rst.Close
Set rst = Nothing
Set cnn = Nothing
Exit_cmdAddGroupTraining_Click:
Exit Sub
Err_cmdAddGroupTraining_Click:
MsgBox Err.Description
Resume Exit_cmdAddGroupTraining_Click
End Sub

 

[Guest]

try...

For varNumber = 0 to lstEmployees.listCount -1
strEmpId = Forms!frm_GroupTraining!lstEmployees.Column(0,varNumber)
....
....
Next varNumber

 

[Guest]

For Each varNumber In lstEmployees.ItemsSelected
strEmpId = Forms!frm_GroupTraining!lstEmployees.Column(0)
strCourseName = Forms!frm_GroupTraining!cboCourseName
dtCompletedDate = Forms!frm_GroupTraining!CompletedDate
With rst
.AddNew
!EmpId = strEmpId
!CourseName = strCourseName
!CompletedDate = strCompletedDate
.Update
End With
Next varNumber

 

[Guest]

Many thanks - it works now!

Arlene

try...

For varNumber = 0 to lstEmployees.listCount -1
strEmpId = Forms!frm_GroupTraining!lstEmployees.Column(0,varNumber)
...
...
Next varNumber

 

[Guest]

I would really appreciate help with this. I am NOT a proficient Access
programmer.

I have a table "A" with a "Status" fireld. There is a form attached to the
table, and the "Status" may be changed multiple times but everytime it is
changed I would like to append the change to Table "B", together with the key
of Table "A" , a sequence number fro the change and the Date and Time of
change to "Status". I hope this gets to someone because I cannot create a
new thread any other way.

Regards,

 

[Douglas J Steele]

See whether you can apply the techniques Allen Browne has in
http://www.allenbrowne.com/AppAudit.html