I have a bound form in MS Access. I want to be able to tell:
- When a new record is inserted
- When an existing record is updated
I thought the events Form.AfterUpdate and Form.AfterInsert would be perfect for this, but I just tested Form.AfterUpdate and found that it fires after updates and inserts.
Is there some way to tell the difference between updates and inserts from within Form_AfterUpdate()? Or is there another approach to detecting these events within Access that I should consider?
Private Sub Form_AfterInsert()
Debug.Print "after insert"
End Sub
Private Sub Form_AfterUpdate()
Debug.Print "after update"
End Sub
When I insert a new record, this is printed:
after update
after insert
When I update an existing record, this is printed:
after update
If you really just need to know if this was a new or existing record commitment, use the BeforeUpdate event to set a module-level variable that can then be read in the AfterUpdate event.
Option Compare Database
Option Explicit
Dim booNew As Boolean
Private Sub Form_BeforeUpdate(Cancel As Integer)
booNew = Me.NewRecord
End Sub
Private Sub Form_AfterUpdate()
Debug.Print IIf(booNew, "New", "Existing")
End Sub
I am thinking that if this matters to you, then you should not be using either AfterUpdate or AfterInsert but in fact BeforeUpdate event. You do not say why you need to know whether a record is an insert or an update, nor what you plan to do in either cases. Even so, I am having hard time of conceiving a good scenario where you need to know that in an After*** event.
In the BeforeUpdate, you still can check the NewRecord property and take some actions based on this. More importantly, you get to cancel the attempt to insert/update by setting the Cancel parameter to true within the BeforeUpdate event.
Also, to present with a different option - if the action you wish to perform is data-centric and would be true regardless of how the data get entered, via this particular form or not, you should consider using data macros instead, which does differentiate between inserts and updates. Note that you still can call VBA code out from data macros in the case the data macro actions are not sufficient for what you need to do.
The AfterUpdate and AfterInsert events are fine for doing things after the fact and usually won't care about how the record came to be. Examples include stuff like refreshing a related form, changing the focus, enabling/disabling controls, that kind of stuff. Note that the examples I cited were all UI-related. That's what it is for and should be used like so.
