Go Premium for a chance to win a PS4. Enter to Win

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

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?
0
cklosko
Asked:
cklosko
  • 6
  • 4
  • 2
1 Solution
 
rockiroadsCommented:
Have you tried looking at  .OldValue ?

0
 
rockiroadsCommented:
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
 
ckloskoAuthor Commented:
Would I use this in BeforeUPdate?
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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