mike60smart
Registered User.
- Local time
- Today, 00:04
- Joined
- Aug 6, 2017
- Messages
- 1,913
Hi Everyone
I am looking for any advise on the following process.
The screenshot below is 1 Record from a Continuous Form.
The Command Button Inserts the Organisation Details into a table named "tblOrganisationOpenCourseBookings" which has a Primary Key named "OrgOpenCourseBookingID"
As part of the insert process for the organisation the "ID" Autonumber field is added as a field named "RecordID"
I now need to amend the following code so that I can append into a table named "tblGroupCourseParticipants" all of the fields highlighted in Yellow as listed:-
ParticipantName1
ParticipantName2
ParticipantName3
ParticipantName4
ParticipantName5
Each of these listed ParticipantNames would need the same Foreign Key from the linked Parent table "tblOrganisationOpenCourseBookings" ie "OrgOpenCourseBookingID"
I thought of using this method to get the
lngOrgOpenCourseBookingID = Nz(DMax("OrgOpenCourseBookingID", "tblOrganisationOpenCourseBookings"), 0)
How would I then be able to incorporate the appending into the code below??
Any help appreciated
Code is:-
I am looking for any advise on the following process.
The screenshot below is 1 Record from a Continuous Form.
The Command Button Inserts the Organisation Details into a table named "tblOrganisationOpenCourseBookings" which has a Primary Key named "OrgOpenCourseBookingID"
As part of the insert process for the organisation the "ID" Autonumber field is added as a field named "RecordID"
I now need to amend the following code so that I can append into a table named "tblGroupCourseParticipants" all of the fields highlighted in Yellow as listed:-
ParticipantName1
ParticipantName2
ParticipantName3
ParticipantName4
ParticipantName5
Each of these listed ParticipantNames would need the same Foreign Key from the linked Parent table "tblOrganisationOpenCourseBookings" ie "OrgOpenCourseBookingID"
I thought of using this method to get the
lngOrgOpenCourseBookingID = Nz(DMax("OrgOpenCourseBookingID", "tblOrganisationOpenCourseBookings"), 0)
How would I then be able to incorporate the appending into the code below??
Any help appreciated
Code is:-
Code:
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
Set dbsMydbs = CurrentDb
Set rstMyTable = dbsMydbs.OpenRecordset("tblOrganisationOpenCourseBooking")
If rs.EOF Then
MsgBox "No recs"
Else
With rs
.MoveLast
.MoveFirst
MsgBox .RecordCount
Do Until .EOF
rstMyTable.AddNew
rstMyTable!ShortCourseBookingID = Me.txtShortCourseBookingID
rstMyTable!ContactFirstName = Me.ContactFirstName
rstMyTable!ContactSurname = Me.ContactSurname
rstMyTable!ContactEMail = Me.ContactEMail
rstMyTable!ContactPhoneNumber = Me.ContactPhone
rstMyTable!OrganisationNameID = Me.txtOrg
rstMyTable!Address = Me.txtOrgAddress
rstMyTable!BillingAddress = Me.txtBillingAddress
rstMyTable!NrPlacesBooked = Me.NrofParticipants
rstMyTable!PONumber = Me.PONumber
rstMyTable!Comments = Me.AdditionalComments
rstMyTable!RecordID = Me.ID
rstMyTable.Update
.MoveNext
Loop
End With
End If
MsgBox "Participants have been added.", vbInformation, "Complete"
DoCmd.RunMacro "mcrDeleteTable2True"
On Error GoTo 0
Exit Sub
Test_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Test of Sub Form_frmTwo"
End Sub