insert record on update vba

I have an access database with a form frm_main

I need to insert a record into a table (audit log type) every time an update is made in frm_main

tbl_main has field field1 that we need to monitor

tbl_audit has the following 3 fields that need to be updated

fld_previous = value of prior value of field1
fld_new = new value of field1
fld_date = date/time when update occurred

can somebody help me with the vba logic, I would much appreciate it.

thanks,
Vinnie
damixaAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
The Form's AfterUpdate event could be used for this, but you'll need to do some work before hand to capture your "old" values.

First create a global variable in the General Declarations section:

Private fChanged As Boolean

Next, create a hidden textbox named "txField1_Old". In the Current event of the Form, do this:

Me.txField1_Old = Me!Field1

In the Change or AfterUpdate event of Field1, do this:

fChanged = True

Now in the Form's AfterUpdate event:

If fChanged Then
  Currentdb.Execute "INSERT INTO YourAuditTable(fld_Previous, fld_New, fld_Date)   VALUES('" & Me.txField1_Old & "','" & Me.Field1 & "',#" & Now & "#)"
End If

If fld_New and fld_Previous are Numeric values:

Currentdb.Execute "INSERT INTO YourAuditTable(fld_Previous, fld_New, fld_Date) VALUES(" & Me.txField1_Old & "," & Me.Field1 & ",#" & Now & "#)"

Finally, in the Form's Current event reset fChanged:

Sub Form_Current()
  fChanged = False
End Sub
0
 
damixaAuthor Commented:
This was perfect Scott. The level of detail in your response made it flawless. Thank you so much.
0
 
damixaAuthor Commented:
Great response, thanks a bunch
0
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.