G
Guest
Can you track the changes that are made to a record in access? Does anyone know how to do this?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
A
Allen Browne
Access does not provide this for you.
However, if all changes are made through forms, you can use the events of
the form to log the edits, inserts, and deletions.
You will need to be comfortable with VBA code. Details in article:
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.
However, if all changes are made through forms, you can use the events of
the form to log the edits, inserts, and deletions.
You will need to be comfortable with VBA code. Details in article:
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.
anyone know how to do this?Tony A. said:Can you track the changes that are made to a record in access? Does
A
Arvin Meyer
anyone know how to do this?Tony A. said:Can you track the changes that are made to a record in access? Does
Yes. In the before update event of the form, grab the OldValue property of
each control and insert that value into an audit table, together with the
date\time of the record change, and the username of the person doing it.
Here's one sample (aircode):
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Handler
Dim lngItemID As Long
Dim strContractorID As String
Dim strSubdivision As String
Dim lngModelID As Long
Dim dblCost As Double
Dim dblCostCode As Double
Dim dtmEffectiveDate As Date
Dim db As DAO.Database
Dim strSQL As String
lngItemID = Me.txtItemID.OldValue
strContractorID = Me.txtContractorID.OldValue
strSubdivision = Me.txtSubdivision.OldValue
lngModelID = Me.txtModelID.OldValue
dblCost = Me.txtCost.OldValue
dblCostCode = Me.txtCostCode.OldValue
dtmEffectiveDate = Me.txtEffectiveDate.OldValue
Set db = CurrentDb
Me.txtLastUpdated = Now
strSQL = "INSERT INTO tblItemHistory ( ItemID, Subdivision, ModelID,
CostCode, Cost, ContractorID, EffectiveDate )"
strSQL = strSQL & " VALUES (" & lngItemID & ", '" & strSubdivision & "',
" & lngModelID & ", " & dblCostCode & ", " & dblCost & ", '" &
strContractorID & "', '" & dtmEffectiveDate & "');"
db.Execute strSQL
Exit_Here:
Exit Sub
Err_Handler:
MsgBox Error$
Me.Undo
Resume Exit_Here
End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access