Access 2007 Using the After Insert event to trigger operations using information from that inserted record

Hello again

I know this answer is probably an obvious one but not to me at the moment so...

I have a sub-form which allows the user to add new entries to a table. When a new record is added I want to carry out various automatic procedures based on the values of this new record.

This processing can only occur once ALL the values have been added to the new record. So I thought the AfterInsert event would be the one to use.

The trouble is for the After Insert event to be triggered the user has moved off the inserted record so that it is no longer current and I can no longer access the appropriate data.

How do I (or is it possible to) access the data in the inserted record once all the req fields have been completed.

It is a multi-user DB so others may me adding new records to this table also.

Many thanks
Who is Participating?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
Try using the Form AfterUpdate event instead, like such - add these code snippets:

Option Compare Database
Option Explicit
    Dim fIsNewRecord As Boolean

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.NewRecord = True Then fIsNewRecord = True
End Sub

Private Sub Form_AfterupDate()
   If fIsNewRecord  = True Then
      ' Do the automatic stuff
  End If
  fIsNewRecord = False
End Sub

Hi stillers1994,

You can call a function after each field has been updated & only if it sees that all the fields have been entered will it call your other procedures

Good Luck!

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"The trouble is for the After Insert event to be triggered the user has moved off the inserted record"

Actually, that is not the case.  The focus remains on the record just added - UNLESS - you simply use the Navigation buttons to 'save' the record by moving to a new record.  If you have a Save button which saves the record, then focus will stay on the new record just added.

stillers1994Author Commented:
Many thanks.  I did use this idea but I also had to use more public variables in the BeforeUpdate sub to store the various data values from the new record as I didn't want to add yet another button on by form (ie Save) as the user would  position off the new record before the.
required task would trigger. Seem to work just wondered whether I was missing something obvious.
Thanks again
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.