How do I access a field's old value before it is updated to a new value?

I am creating a change history of updates to fields. I need to capture the old value of the field before it was changed. Within BeforeUpdate, the field already contains the updated value, not the original value. How do I access the field's original value, before the update occurs, in order to be able to capture the old value and the new value, when a field is updated?
ckloskoAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
rockiroadsConnect With a Mentor Commented:
An example, say you had a field called myField in your form

Private Sub MyField_BeforeUpdate(Cancel As Integer)
    MsgBox "Before Update it is " & MyField.OldValue
    MsgBox "Going to be updated to " & MyField.Value
End Sub

To capture it in the afterupdate event, you could maybe hold this value in a temp variable then use it in the afterupdate
eg

'Defined globally in form
Dim sOldValue as String

Private Sub MyField_BeforeUpdate(Cancel As Integer)
    sOldValue = MyField.OldValue
End Sub

Private Sub MyField_AfterUpdate()
     Msgbox "Old Value was " & sOldValue & vbcrlf & "New Value is " & MyField.Value
end sub
0
 
rockiroadsCommented:
Have you tried looking at  .OldValue ?

0
 
ckloskoAuthor Commented:
Would I use this in BeforeUPdate?
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Private Sub YourControl_BeforeUpdate(Cancel As Integer)

Dim x
x=  Nz(Me.YourControl.OldValue,"")
If x = "" Then  ' no old value
   ' whatever
Else
   ' has old value - do what ever
End If

End Sub

mx
0
 
ckloskoAuthor Commented:
That worked. Thank you.
0
 
rockiroadsCommented:
Yes, you would. See the example I gave in using variables to hold the old value.

The auditing should take place after the record has been saved.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
You need to test OldValue for Null - because there will not be an OldValue for a new record - and you will get a run time error.

mx
0
 
rockiroadsCommented:
mx, Should be okay if sOldValue defined as variant?
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Better test that ...

mx
0
 
rockiroadsCommented:
it should be fine so as long as you use a recordset to insert. using sql then its a issue. Probably easier all round just to use NZ without having to phaff about.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Humm ... does not seem to be a problem  ... I SWEAR this used to be an issue.

mx

0
 
rockiroadsCommented:
its what I thought, variants handle null. The issue is if you generate sql then it becomes a issue due to the null, but adding via recordsets is fine
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.