Insert Into query not working

[Galley]

I have designed a database to store training records. I created a form with a multi-column text box so I can select all employees who have attended a training session that day, and update all records at once. The form has a combo box, as well as two text boxes. Even though the listbox has three columns, I only need to pass the first column. The three columns (in order) are Employees.EmployeeNumber, Employees.FirstName and Employees.LastName. Selecting the command button should run an append query to enter the EmployeeNumber, SOPNumber, RevisionNumber and TrainingDate to the SOPTraining table.

FORM
frmAppendSOPTraining
lstEmployees (unbound)
cboSOPNumber (unbound) (the numbers are pulled from an SOP table)
txtRevisionNumber (unbound)
txtTrainingDate (unbound)
cmdAddRecords

TABLE Employees
EmployeeID (AutoNumber)
EmployeeNumber (Number Long)
FirstName (Text)
LastName (Text)

TABLE SOPTraining
TrainingID (AutoNumber)
EmployeeNumber (Number Long)
SOPNumber (Text)
RevisionNumber (Text)
TrainingDate (Date/Time)

I had some errors in the code at first, but with help from another site, I was able to fix those. The problem now is, that the SOPTraining table is not being appended.

Private Sub cmdAddRecords_Click()

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Dim strSQL2 As String
Set frm = Forms!frmAppendSOPTraining
Set ctl = frm!lstEmployees
strSQL = "INSERT INTO SOPTraining (EmployeeNumber, SOPNumber, RevisionNumber, TrainingDate) VALUES ("
strSQL = strSQL & "'" & Me.cboSOPNumber & "', '" & Me.txtRevisionNumber & "', #" & Me.txtTrainingDate & "#, "

For Each varItem In ctl.ItemsSelected
strSQL2 = strSQL & ctl.ItemData(varItem) & ")"
CurrentDb.Execute strSQL2
Next varItem
End Sub

 

[pbaldy]

The fields in the INSERT INTO clause do not appear to be in the same order as the fields in the VALUES clause. They should be.

 

[Galley]

I'm trying to grasp what is going on here, but my SQL knowledge is pretty flaky.
The SQL statement says: INSERT INTO the table [SOPTraining] in the following fields, (EmployeeNumber, SOPNumber, RevisionNumber, TrainingDate) the following VALUES (the selected values from lstEmployees, the value of cboSOPBNumber, the value of txtRevisionNumber, and the value of txtTrainingDate).
After that, there is a For Next loop, where it adds each selected item in lstEmployees to a string. I guess I don't understand how that string is inserted into the place wher the EmployeeNumber should be.

 

[pbaldy]

It isn't; it's being put at the end, which is why they're out of sequence. The VALUES clause is in this order:

SOPNumber, RevisionNumber, TrainingDate, EmployeeNumber

You can try changing the order of the INSERT INTO clause to match.

 

[Galley]

WooHoo! You guys rock!
I still need to add some error trapping, and some message boxes, but I'm sure I can figure that out on my own. (If not, I know where to come for help).