INSERT INTO in a One-toMany relationship

[Robin]

Hello,

I am trying to do an append query in VBA that works as follows:
Item(s) from a table ClientEngagement are selected in a multi-select listbox
and the Engagement(s) associated with the item(s) are appended to the
ClientEngagement table for the next year. (EngagementYr +1). Each Engagement
has 1-10 Assignments associated with it in a table ClientAssignments which
has the relationship:
ClientEngagement -> one-to-many -> ClientAssignment
on the fields:
ClientID,EngagementID,EngagementYr -> one-to-many ->
ClientID,EngagementID,EngagementYr,AssignmentID

The first part of my append query works fine as there is only one unique
ClientID,EngagementID,EngagementYr per year.
FIRST PART:
For Each varItm In Me.lstECsubEc.ItemsSelected
stEID = Form_zfmMain.lstEMainE
stSQL = "INSERT INTO ClientEngagement (ClientID, EngagementID, EngagementYr,
CEStaffID, CEBudgetHrs, CATrackBudget) " _
& "VALUES(" _
& """" & Me.lstECsubEc.Column(0, varItm) & """, " _
& """" & stEID & """, " _
& """" & Me.lstECsubEc.Column(2, varItm) + 1 & """, " _
& """" & Me.lstECsubEc.Column(3, varItm) & """, " _
& """" & Me.lstECsubEc.Column(5, varItm) & """, " _
& """" & Me.lstECsubEc.Column(7, varItm) & """" _
& ")"
DoCmd.RunSQL stSQL

But the second part may have one or more records to append.
SECOND PART:
stSQL = "INSERT INTO ClientAssignment (ClientID, EngagementID, EngagementYr,
AssignmentID, CAStaffID, CABudgetHrs) " _
& "VALUES(" _
& """" & Me.lstECsubEc.Column(0, varItm) & """, " _
& """" & stEID & """, " _
& """" & Me.lstECsubEc.Column(2, varItm) + 1 & """," _
& """" & stAID & """, " _
& """" & stCAStaffID & """, " _
& """" & sglBudgetHrs & """" _
& ")"
DoCmd.RunSQL stSQL
Next varItm

I know why the second part doesn;t work but I cannot figure out how to get
it to append multiple (Assignment) records based on the single Engagement
record selected in the list. I've tried SELECT and a loop to no avail.

Any help would be appreciated.
Thank you in advance,
Robin