Different actions for Insert and Update

[merry_fay]

Hi,

I'm probably having a really blonde day, but I'm trying to get set a form to run one bit of code if I update a record in a table & a slightly different bit if I add a new record.

I've added msgbox's to test the code & if I update a record, only the 'After Update' code runs but if I add a new record, the 'After Update' code runs & then the 'After Insert'. I'm trying to think of a way to make it distinguish between an update & a new record so I can stop the 'After Update' event happening for new records. Coming up blank at the moment.

Is there a trigger? -am I just being really blonde & missing it?

Thanks
merry_fay

 

[Galaxiom]

Use the NewRecord Property
http://msdn.microsoft.com/en-us/library/aa201533(v=office.10).aspx

 

[LPurvis]

Just to be clear, the AfterUpdate event will always be raised. You need to make a decision based on the NewRecord property in that Event Procedure.
It could just be to execute the code or not - and let AfterInsert handle new records. But the event (and hence the associated Event Procedure) will be raised regardless. So what you were seeing (and had determined to be the case) was totally right.

 

[merry_fay]

Just to be clear, the AfterUpdate event will always be raised. You need to make a decision based on the NewRecord property in that Event Procedure.
It could just be to execute the code or not - and let AfterInsert handle new records. But the event (and hence the associated Event Procedure) will be raised regardless. So what you were seeing (and had determined to be the case) was totally right.

Hiya,

Yes I've realised the afterupdate event is always happening.
In that event, I've tried using the newrecord property:

Private Sub Form_AfterUpdate
If Form.NewRecord = True Then
MsgBox "New record"
Else
MsgBox "after update triggered"
End If
End Sub

but it only ever gives me the after update message, never the new record!!
I've also tried it using the exact code in the link Galaxiom gave me but it still didn't work with that

Any ideas....?

Thanks

 

[gemma-the-husky]

i think you need the forms before_update event, not the after_update.

before the update, newrecord will be true or false

after the update, the write is committed, and newrecord is definitely false. furthermore, changing stuff in the afterupdate will just dirty it again, leading to another save, and possible an interminable loop (although you are just shoing a msgbox)

 

[merry_fay]

i think you need the forms before_update event, not the after_update.

before the update, newrecord will be true or false

after the update, the write is committed, and newrecord is definitely false. furthermore, changing stuff in the afterupdate will just dirty it again, leading to another save, and possible an interminable loop (although you are just shoing a msgbox)

Thanks but unfortunately before update won't work for me -I need to complete all the fields in the record before my code runs as they're needed for inputs.
I've managed to work out a work-around though:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.txtNew = "New"
End Sub

Private Sub Form_AfterUpdate()
If Me.txtNew = "New" Then
MsgBox "New record"
Me.txtNew = ""
Else
MsgBox "after update triggered"
End If
End Sub

Probably not the most efficient way but it's working so far!

Thanks for your help!