Adding Records from MultiSelect ListBox

[Voodeux2014]

Private Sub cmdAssignClasses_Click()

Dim MyDB As dao.Database
Dim varItem As Variant
Dim lst As ListBox
 
Dim rst As dao.Recordset


If lstTrainingProviders.ItemsSelected.Count = 0 Then
    MsgBox "Please select at least 1 (one) training provider.", vbOKOnly, "Error"
    Exit Sub
End If


DoCmd.RunCommand acCmdSaveRecord
 
Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("tbl2GrantTrainers", dbOpenDynaset, dbAppendOnly)
 
With rst
  For Each varItem In lstTrainingProviders.ItemsSelected
    .AddNew
       ![GrantID] = frmAddNewGrant![ID]
       ![TrainerID] = lstTrainingProviders.ItemData(varItem)
    .Update
  Next varItem
End With
 
rst.Close
Set rst = Nothing
 
MsgBox "The training providers have been successfully added to the new grant.", vInformation, "Information"


DoCmd.Maximize
End Sub

For some reason this code is not executing the following code:

With rst  For Each varItem In lstTrainingProviders.ItemsSelected
    .AddNew
       ![GrantID] = frmAddNewGrant![ID]
       ![TrainerID] = lstTrainingProviders.ItemData(varItem)
    .Update
  Next varItem
End With

It steps through With rst
Highlights For Each varItem line to show it will execute next
Then it jumps down and highlights End With without stepping through the other lines of code. Any idea where I went wrong here?

[pbaldy]

I wonder if the SaveRecord line is clearing the selections? Try commenting that out.

[Voodeux2014]

Wow thanks for that. It is running through the code now. Now just to changed it to where the record gets added to the Grant table as well. Thanks a bunch!

[pbaldy]

Happy to help! If the form is bound, it will save without code, either when you move to a different record or close the form.