After Update and After Insert Events

[Anthony Fontana]

I am designing a form for customer names and address maintenance. I also
want it to add a record to another table (tbl_dbTbl_History) indicating when
the record was added or edited and by whom.

I have a procedure in modules as follows

procedure name pci_RecordDbWork
Dim strSQL As String

strSQL = "INSERT INTO tbl_dbTbl_History (TableAccessed, HowAccessed,
ClientID, AccessDate, AccessBy) " & _
" values ('" & strTBL & "', '" & strHow & "'," & lngID & ", #" & Now
& "# ,'" & CurrentUser() & "')"


DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True


I then call it in the After Update and After Insert Events thusly
In after insert event: pci_RecordDbWork "Added", "tbl_Clients",
Me.ClientID
In after update event: pci_RecordDbWork "Editing", "tbl_Clients",
Me.ClientID

I always get two records added to tbl_dbTbl_History, one for editing and one
for adding, whenever I just add a record.

How do I get my form to added just one record to tbl_dbTbl_History when a
record is added to the clients table.

Thank you.

 

[Allen Browne]

You will need to supress the form's AfterUpate if it was a new record.
Create a form-level variable to track whether it was a new record or not.
Set its value in Form_BeforeUpdate, and you can test it in Form_AfterUpdate.

Here's a utility that does that kind of logging if you want an example to
follow:
Audit Trail - Log changes at the record level
at:
http://allenbrowne.com/AppAudit.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[Anthony Fontana]

Thanks. I'll try that.

You will need to supress the form's AfterUpate if it was a new record.
Create a form-level variable to track whether it was a new record or not.
Set its value in Form_BeforeUpdate, and you can test it in Form_AfterUpdate.

Here's a utility that does that kind of logging if you want an example to
follow:
Audit Trail - Log changes at the record level
at:
http://allenbrowne.com/AppAudit.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message