Insert

[pbaldy]

I assume that's the record that just got added? Are there any other required fields not being added?

 

[mike60smart]

Hi Paul

All required fields are being added to tblOrganisationOpenCourseBooking

 

[pbaldy]

Can you attach the db here to play with?

 

[mike60smart]

Hi Paul

It is too big to attach. I will try to upload an example

 

[mike60smart]

Hi Paul

Here with a stripped down version of the tables required.

When the Db opens it opens Form 1 - Click Process

This opens frmTwo - Click Insert Organisation command button

The record will be inserted and you get the error message

View attachment AlternateMikeSample2.zip

 

[pbaldy]

I should have noticed this in the code above. You set VariableName and then use lngOrgOpenCourseBookingID. You need to be consistent. If I replace lngOrgOpenCourseBookingID with VariableName it works. I get an error on the macro, but that's because it isn't in the sample.

 

[mike60smart]

Hi Paul

That is absolutely spot on.

Many thanks for hanging in there on this one.

I have used the same process for another set of emails and when testing I get the following error:-



Here is the Code which I have checked over and over but can't spot the error. Can you spot it for me??:-

Code is:-

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_frmTwoIndividual"

End Sub

Sub Test(rs As DAO.Recordset)

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

lngIndividualBookingID = VariableName

Set dbsMydbs = CurrentDb
Set rstMyTable = dbsMydbs.OpenRecordset("tblIndividualOpenCourseBooking")
Set rstOtherTable = dbsMydbs.OpenRecordset("tblIndividualCourseParticipants")
If rs.EOF Then
    MsgBox "No recs"
Else
With rs
    .MoveLast
   .MoveFirst
    MsgBox .RecordCount
Do Until .EOF

rstMyTable.AddNew
rstMyTable!ShortCourseBookingID = Me.txtShortCourseBookingID
rstMyTable!Firstname = Me.Firstname
rstMyTable!Surname = Me.Surname
rstMyTable!EmailAddress = Me.EmailAddress
rstMyTable!PhoneNumber = Me.PhoneNumber
rstMyTable!Address1 = Me.Address1
rstMyTable!Address2 = Me.Address2
rstMyTable!Address3 = Me.Address3
rstMyTable!TownCityID = Me.txtTown
rstMyTable!PostCode = Me.PostCode
rstMyTable!CourseID = Me.txtCourseID
rstMyTable!CourseDate = Me.CD2
rstMyTable!ParticipantName = Me.ParticipantName
rstMyTable!AdditionalComments = Me.AdditionalComments
rstMyTable!RecordID = Me.id

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

If Len(ParticipantName & vbNullString) > 0 Then
  rstOtherTable.AddNew
  rstOtherTable!IndividualBookingID = VariableName
  rstOtherTable!ParticipantName = ParticipantName
  rstOtherTable.Update
End If
   .MoveNext
Loop

End With
End If

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

DoCmd.RunMacro "mcrDeleteTableIndividual2True"

End Sub

 

[mike60smart]

Hi Paul

Here with the sample Db again for you to try and find what I am missing

View attachment AlternateMikeSample2.zip

Any help appreciated

 

[pbaldy]

The DLookup() in Me.txtShortCourseBookingID returns an error, because the referred to form doesn't exist. You'll run into another error because a field doesn't exist in the target table.

 

[mike60smart]

Hi Paul

Many Many thanks. Spot on yet again.

Corrected the reference to the Form and also amended the field names in the target table and everything works like a charm.

 

[pbaldy]

Happy to help!