[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 435
  • 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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