[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 478
  • Last Modified:

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
0
damixa
Asked:
damixa
  • 2
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now