Insert (1 Viewer)

[mike60smart]

Hi Everyone

I have a Continuous Form that displays records parsed from a Linked Outlook folder which display as follows:-



There are 2 Records - the first has 2 Participant Names and the second has 1 Participant names.

I am using the following code to loop through each record and insert the various Participant names.

When the code runs it inserts the records as follows:-



When they should be inserted as follows:-



Any help appreciated in highlighting where I am going wrong with this.

PS I have cross posted this on Utter Access as follows:-

http://www.utteraccess.com/forum/index.php?showtopic=2048226&st=0&p=2677403&#entry2677403
Code is as follows:-

Private Sub cmdIn_Click()

On Error GoTo cmdIn_Click_Error
    Me.RecordsetClone.Filter = "[Updated] = -1"
    Test Me.RecordsetClone.OpenRecordset
Exit Sub
cmdIn_Click_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdIn_Click of Sub Form_frmTwo"

End Sub

Sub Test(RS As DAO.Recordset)

    On Error GoTo Test_Error
    
If Me.Dirty Then Me.Dirty = False
Dim dbsMydbs As DAO.Database
Dim rstMyTable As DAO.Recordset
Dim rstOtherTable As DAO.Recordset
Dim strSQL As String
Dim VariableName As Long
Dim lngBookingNumber As Long

lngBookingNumber = Me.txtCourseID

Set dbsMydbs = CurrentDb
Set rstMyTable = dbsMydbs.OpenRecordset("short course group bookings")
Set rstOtherTable = dbsMydbs.OpenRecordset("tblGroupCourseParticipants")
If RS.EOF Then
    MsgBox "No recs"
Else
With RS
    .MoveLast
   .MoveFirst
    MsgBox .RecordCount

Do Until .EOF
'rstOtherTable.AddNew


If Len(ParticipantName1 & vbNullString) > 0 Then
  rstOtherTable.AddNew
  rstOtherTable![BookingNumber] = lngBookingNumber
  rstOtherTable!ParticipantName = ParticipantName1
  rstOtherTable.Update
End If

If Len(ParticipantName2 & vbNullString) > 0 Then
  rstOtherTable.AddNew
  rstOtherTable![BookingNumber] = lngBookingNumber
  rstOtherTable!ParticipantName = ParticipantName2
  rstOtherTable.Update
End If

If Len(ParticipantName3 & vbNullString) > 0 Then
  rstOtherTable.AddNew
  rstOtherTable![BookingNumber] = lngBookingNumber
  rstOtherTable!ParticipantName = ParticipantName3
  rstOtherTable.Update
End If

If Len(ParticipantName4 & vbNullString) > 0 Then
  rstOtherTable.AddNew
  rstOtherTable![BookingNumber] = lngBookingNumber
  rstOtherTable!ParticipantName = ParticipantName4
  rstOtherTable.Update
End If

If Len(ParticipantName5 & vbNullString) > 0 Then
  rstOtherTable.AddNew
  rstOtherTable![BookingNumber] = lngBookingNumber
  rstOtherTable!ParticipantName = ParticipantName5
  rstOtherTable.Update
End If

   .MoveNext
Loop

End With
End If

MsgBox "Participants have been added.", vbInformation, "Complete"

'DoCmd.RunMacro "mcrDeleteTable2True"

Forms("frmTwo").Requery

    On Error GoTo 0
    Exit Sub

Test_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Test of Sub Form_frmTwo"

End Sub