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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 Architect / Systems AnalystCommented:
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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.