Insert (1 Viewer)

[mike60smart]

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:-

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

 

[pbaldy]

Since you're using a recordset, adding this after the .Update will get you the autonumber:

rstMyTable.Bookmark = rstMyTable.LastModified
VariableName= rstMyTable!OrgOpenCourseBookingID

 

[mike60smart]

Hi Paul

Many thanks for that.

So if I am able to obtain the Variablename as the Last PrimaryKey from tblOrgOpenCourseBooking

How would I use an Insert to put the following fields into "tblGroupCourseParticipants"??



I can usually work it out when there are just a specific number of fields but have no idea on how to create an insert for a number of Participants from the one record as shown.

Any help appreciated

 

[pbaldy]

Well, your participants data isn't normalized. It would normally be a record for each participant, not a field. Like that, you'll have to test each field to see if it contains anything, and react accordingly.

 

[arnelgp]

since you already captured the FK (say VarID) through Mr.Baldy,
you insert first all participant name with normal insert query or by recordset.
then later issue an Update Query to update the FK key.

Update table Set PKField=varKey Where PKField Is Null;

 

[mike60smart]

Hi Paul

Correct it is not normalised because this is data being parsed from an EMail in a linked Outlook Folder

 

[pbaldy]

Personally I would store it normalized, whatever the source. Either way, like I said you'd have to test each participant field if you're building SQL in code. If it's staying in that un-normalized format, you can simply include them all:

INSERT INTO TableName(Participant1, Participant2,...
SELECT Participant1, Participant2,...

 

[mike60smart]

Hi Everyone

I think the following explains it a little better:-

 

[mike60smart]

Hi Everyone

The Code is now this:-

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 strSQL As String
Dim lngVariableName As Long

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

rstMyTable.Bookmark = rstMyTable.LastModified
VariableName = rstMyTable!OrgOpenCourseBookingID

  
strSQL = "INSERT INTO tblGroupCourseParticipants ( OrgOpenCourseBookingID, ParticipantName1, ParticipantName2, ParticipantName3, ParticipantName4,ParticipantName5) " & _
" SELECT (" & VariableName & ", '" & ParticipantName1 & "', '" & ParticipantName2 & "', '" & ParticipantName3 & "', '" & ParticipantName4 & "','" & ParticipantName4 & "')"
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError


   .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

When i run the code I get the following error:-

 

[pbaldy]

You don't need an update query. Test each participant field. If it has a value use a second recordset to add to the target table, using the variable for the key field. Pseudo code (I don't know where you're getting the source participants, new code in red):

...
rstMyTable.Update
rstMyTable.Bookmark = rstMyTable.LastModified
VariableName= rstMyTable!OrgOpenCourseBookingID

[COLOR="Red"]If Len(Participant1 & vbNullString) > 0 Then
  rstOtherTable.AddNew
  rstOtherTable!KeyField = VariableName
  rstOtherTable!Participant1 = Participant1
  rstOtherTable.Update
End If[/COLOR]

   .MoveNext

Edit: you'd repeat that bit for your participant fields. A loop is possible.

 

[pbaldy]

You need to use the variable you declared, lngVariableName .

 

[mike60smart]

Hi Paul

OK changed the code to the following but not sure how to declare the VariableName.

When I run it I get this error:-



Code is:-

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 lngOrgOpenCourseBookingID As Long

lngOrgOpenCourseBookingID = VariableName

Set dbsMydbs = CurrentDb
Set rstMyTable = dbsMydbs.OpenRecordset("tblOrganisationOpenCourseBooking")
Set rstOtherTable = dbsMydbs.OpenRecordset("tblGroupCourseParticipants")
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
rstMyTable.Bookmark = rstMyTable.LastModified
VariableName = rstMyTable!OrgOpenCourseBookingID

If Len(Participant1 & vbNullString) > 0 Then
  rstOtherTable.AddNew
  rstOtherTable!lngOrgOpenCourseBookingID = VariableName
  rstOtherTable!Participant1 = Participant1
  rstOtherTable.Update
End If
If Len(Participant2 & vbNullString) > 0 Then
  rstOtherTable.AddNew
  rstOtherTable!lngOrgOpenCourseBookingID = VariableName
  rstOtherTable!Participant2 = Participant2
  rstOtherTable.Update
End If
If Len(Participant3 & vbNullString) > 0 Then
  rstOtherTable.AddNew
  rstOtherTable!lngOrgOpenCourseBookingID = VariableName
  rstOtherTable!Participant3 = Participant3
  rstOtherTable.Update
End If
If Len(Participant4 & vbNullString) > 0 Then
  rstOtherTable.AddNew
  rstOtherTable!lngOrgOpenCourseBookingID = VariableName
  rstOtherTable!Participant4 = Participant4
  rstOtherTable.Update
End If
If Len(Participant5 & vbNullString) > 0 Then
  rstOtherTable.AddNew
  rstOtherTable!lngOrgOpenCourseBookingID = VariableName
  rstOtherTable!Participant5 = Participant5
  rstOtherTable.Update
End If

   .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

 

[pbaldy]

Your earlier code had:

Dim lngVariableName As Long

Then use lngVariableName in the code. That said, I'd make it descriptive to you. I used that as a placeholder.

 

[mike60smart]

Hi Paul

I am not grasping this at all. Must be my 72 year old brain

If in the code we are saying this:-

rstMyTable.Update
rstMyTable.Bookmark = rstMyTable.LastModified
VariableName = rstMyTable!OrgOpenCourseBookingID

How do I use this in the If statement below:-


If Len(Participant1 & vbNullString) > 0 Then
rstOtherTable.AddNew
rstOtherTable!lngOrgOpenCourseBookingID = ((What do I use here???))
rstOtherTable!Participant1 = Participant1
rstOtherTable.Update
End If

 

[pbaldy]

"VariableName" is simply meant to refer to whatever variable you want to use, much as I might say FormName or TextboxName (in other words, replace with your desired name). You simply need to declare and use the same variable:

Dim VariableName As Long

VariableName = rstMyTable!OrgOpenCourseBookingID

rstOtherTable!lngOrgOpenCourseBookingID = VariableName

 

[mike60smart]

Hi Paul

OK I made what I think are the correct changes to the code.

Now when I run the code I get the following error:-



The correct data is being inserted into tblOrganisationShortCourseBooking but none of the participant names are being inserted into tblGroupCourseParticipants.

Code is:-

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 lngOrgOpenCourseBookingID As Long
Dim VariableName As Long

lngOrgOpenCourseBookingID = VariableName

Set dbsMydbs = CurrentDb
Set rstMyTable = dbsMydbs.OpenRecordset("tblOrganisationOpenCourseBooking")
Set rstOtherTable = dbsMydbs.OpenRecordset("tblGroupCourseParticipants")
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
rstMyTable.Bookmark = rstMyTable.LastModified
VariableName = rstMyTable!OrgOpenCourseBookingID

If Len(ParticipantName1 & vbNullString) > 0 Then
  rstOtherTable.AddNew
  rstOtherTable!lngOrgOpenCourseBookingID = VariableName
  rstOtherTable!ParticipantName = ParticipantName1
  rstOtherTable.Update
End If
If Len(ParticipantName2 & vbNullString) > 0 Then
  rstOtherTable.AddNew
  rstOtherTable!lngOrgOpenCourseBookingID = VariableName
  rstOtherTable!ParticipantName = ParticipantName2
  rstOtherTable.Update
End If
If Len(ParticipantName3 & vbNullString) > 0 Then
  rstOtherTable.AddNew
  rstOtherTable!lngOrgOpenCourseBookingID = VariableName
  rstOtherTable!ParticipantName = ParticipantName3
  rstOtherTable.Update
End If
If Len(ParticipantName4 & vbNullString) > 0 Then
  rstOtherTable.AddNew
  rstOtherTable!lngOrgOpenCourseBookingID = VariableName
  rstOtherTable!ParticipantName = ParticipantName4
  rstOtherTable.Update
End If
If Len(ParticipantName5 & vbNullString) > 0 Then
  rstOtherTable.AddNew
  rstOtherTable!lngOrgOpenCourseBookingID = VariableName
  rstOtherTable!ParticipantName = ParticipantName5
  rstOtherTable.Update
End If

   .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

 

[pbaldy]

Typically that would mean a field name is spelled wrong. Comment out the On Error line temporarily and run the code. You should be given a debug option that will take you to the offending line.

 

[mike60smart]

Hi Paul

I commented out the On Error line and it just does the same as before?

 

[pbaldy]

You should get the same error, but it should include a Debug button. Is "lngOrgOpenCourseBookingID" really the name of the field in the table? It looks like a variable name. If that's not it, can you attach the db here?

 

[mike60smart]

Hi Paul

Of course lngOrgOpenCourseBookingID is not the field name it should be OrgOpenCourseBookingID

I changed the name and now the error is this:-


The record is actually in the table so what would I need to tweek to get the code to recognise this??

The Code is now:-

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 lngOrgOpenCourseBookingID As Long
Dim VariableName As Long

lngOrgOpenCourseBookingID = VariableName

Set dbsMydbs = CurrentDb
Set rstMyTable = dbsMydbs.OpenRecordset("tblOrganisationOpenCourseBooking")
Set rstOtherTable = dbsMydbs.OpenRecordset("tblGroupCourseParticipants")
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
rstMyTable.Bookmark = rstMyTable.LastModified
VariableName = rstMyTable!OrgOpenCourseBookingID

If Len(ParticipantName1 & vbNullString) > 0 Then
  rstOtherTable.AddNew
  rstOtherTable!OrgOpenCourseBookingID = lngOrgOpenCourseBookingID
  rstOtherTable!ParticipantName = ParticipantName1
  rstOtherTable.Update
End If
If Len(ParticipantName2 & vbNullString) > 0 Then
  rstOtherTable.AddNew
  rstOtherTable!OrgOpenCourseBookingID = lngOrgOpenCourseBookingID
  rstOtherTable!ParticipantName = ParticipantName2
  rstOtherTable.Update
End If
If Len(ParticipantName3 & vbNullString) > 0 Then
  rstOtherTable.AddNew
  rstOtherTable!OrgOpenCourseBookingID = lngOrgOpenCourseBookingID
  rstOtherTable!ParticipantName = ParticipantName3
  rstOtherTable.Update
End If
If Len(ParticipantName4 & vbNullString) > 0 Then
  rstOtherTable.AddNew
  rstOtherTable!OrgOpenCourseBookingID = lngOrgOpenCourseBookingID
  rstOtherTable!ParticipantName = ParticipantName4
  rstOtherTable.Update
End If
If Len(ParticipantName5 & vbNullString) > 0 Then
  rstOtherTable.AddNew
  rstOtherTable!OrgOpenCourseBookingID = lngOrgOpenCourseBookingID
  rstOtherTable!ParticipantName = ParticipantName5
  rstOtherTable.Update
End If

   .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