Solved: BeforeUpdate Event

[philfer]

Hello,

Does anyone have or know where to find a working example of code using this event to compare the current and previous value of a control

Many Thanks
Phil

[OBP]

Phil, yes, I developed the following code to store the "Change of Value" of all the fields on a form in a table called Action Audit Trail.
If the database is Secured you can also add who made the change>

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim rs As Object, count As Integer, data() As Variant, rstable As Object, db As Object, action As String
If Me.NewRecord Then
action = "Added Record"
Else
action = "Edited Record"
End If
Set db = CurrentDb
Set rstable = db.openrecordset("Action Audit Trail")
ReDim data(fieldcount, 2)
Set rs = Me.RecordsetClone
rs.Bookmark = Me.Bookmark
For count = 0 To fieldcount
data(count, 1) = rs(count).Name
data(count, 2) = rs(count).Value
If IsNull(data(count, 2)) Then data(count, 2) = "None"
For Each ctl In Forms(Me.Name).Controls
If ctl.ControlType = acTextBox Then
If ctl.Name = rs(count).Name And ctl.Value <> data(count, 2) Then
With rstable
.AddNew
'!UserID = Application.CurrentUser
!Form = Me.Name
!FieldName = ctl.Name
!action = action
![Action Date/Time] = Now()
![Old Value] = data(count, 2)
![New Value] = ctl.Value
.Update
.Bookmark = .LastModified
MsgBox "added Record " & rs(count).Value & " becomes " & ctl.Value
End With
End If
End If
Next ctl
Next count
rs.Close
Set rs = Nothing
rstable.Close
Set rstable = Nothing

End Sub

[philfer]

Thanks OBP,

Thats a very useful code example. I find reading code examples (as well as practical execution) to be the best way to learn.

I am quite a beginner and was looking for something a bit simpler in order to understand the event i.e. is it possible to do :-

If Me.txtPrice > (Me.txtPrice.OldValue * 10) Then
MsgBox "You've got to be kidding me!!!"
End If

Thanks again and all the best
Phil

[OBP]

No you can't do that because the me.txtprice holds the "Current" value of the field, although it hasn't yet been written to the table.
You could use the RecordsetClone version I used in my example to do the comparison or you could hold the Value in a vba "Variable" which would be populated with the original value in the "On Current" event.
It would be best to use a Public Variable to do this.
So in the On current event enter
oldvalue = me.txtPrice

and right at the top of the VBA window enter
Public oldvalue as currency
if your txtPrice field in the Table is Currency.
Then in the Before Update event you could use

If Me.txtPrice > (oldvalue * 10) Then
MsgBox "You've got to be kidding me!!!"
End If