Verify values change before updating record

[gopherking]

I have a form that updates the last modified time and by which user when the Save and Close button is clicked but I am trying to prevent it from updating these fields if no other fields have been updated. For example, a user opens the form and doesn't make any changes at all and clicks on Save and Close vs. Cancel, this updates the modified by and date fields when it shouldn't.

I thought that I could write in an If statement to look at the .value and .oldvalue for the fields that should be changed in order for the Modified by/date should be updated, but I keep getting a Run Time 424 error.



Here is the code I'm using and I'm hoping someone can help (See the ElseIf line for the If Statement I spoke of above):

Private Sub Command10_Click()
Dim intMissInfo As String
Dim Admin As Variant
Dim ID As Variant
Dim Name As Variant
Dim Security As Variant
 
ID = Me.ID
Name = Me.UserName
Security = Me.SecurityLevel
Admin = Forms!Dashboard_Form.UserName
 
If (IsNull(ID) Or IsNull(Name) Or IsNull(Security)) Then
GoTo MissingInfo
 
ElseIf ((ID.Value = ID.OldValue) And (Name.Value = Name.OldValue) And (Security.Value = Security.OldValue)) Then
GoTo NoChange
 
Else
Me.ModifiedBy = Admin
Me.ModifiedDate = Now()
Forms!Security_Main_Form.Requery
If Me.Dirty Then Me.Dirty = False
DoCmd.Close
 
 
 
End If
 
Exit Sub
 
MissingInfo:
'Display message that can't be saved without an ID
intMissInfo = MsgBox("All fields are required to add a new user to the database. Please make sure you have entered a valid ID, User Name, and selected the appropriate Security Level before proceeding.", vbOKOnly, "Missing Required Fields")
 
Exit Sub
 
NoChange:
Exit Sub
 
End Sub

[RuralGuy]

Variables do not have a .OldValue.

[ketbdnetbp]

As an addition to that which the Rural Guy indicated: variables do not have an .OldValue.

But, you can put an .OldValue from a control on a form into a variable.

I have use this in the past:

'Test for .OldValue
If IsNull(Me![ControlName].OldValue) = False Then


Dim x1 As Variant
x1 = Me![ControlName].OldValue
Dim y2 As Variant
y2 = Me![ControlName]


'Compare values
If x1 <> y2 Then
'Do Something
else
'Process alternative
End if

else
'Do Something
End if

Just a thought.

All the best,

Jim

[gopherking]

@Rural Guy -- you had me thinking I was crazy for a second there, but then I realized that you were right that variables don't have .oldvalue <facepalm> ... Thanks!

@ketbdnetbp -- THANK YOU! Your comment got me back on track and I was able to resolve the issue! Below is the code I used just in case anyone else can benefit from it:

Private Sub Command10_Click()
Dim intMissInfo As String
Dim Admin As Variant
Dim ID As Variant
Dim Name As Variant
Dim Security As Variant
Dim OldID As Variant
Dim OldName As Variant
Dim OldSecurity As Variant
 
ID = Me.ID
Name = Me.UserName
Security = Me.SecurityLevel
Admin = Forms!Dashboard_Form.UserName
OldID = Me.ID.OldValue
OldName = Me.UserName.OldValue
OldSecurity = Me.SecurityLevel.OldValue
 
If (IsNull(ID) Or IsNull(Name) Or IsNull(Security)) Then
GoTo MissingInfo
 
ElseIf ((ID = OldID) And (Name = OldName) And (Security = OldSecurity)) Then
GoTo NoChange
 
Else
Me.ModifiedBy = Admin
Me.ModifiedDate = Now()
Forms!Security_Main_Form.Requery
If Me.Dirty Then Me.Dirty = False
DoCmd.Close
 
 
 
End If
 
Exit Sub
 
MissingInfo:
'Display message that can't be saved without an ID
intMissInfo = MsgBox("All fields are required to add a new user to the database. Please make sure you have entered a valid ID, User Name, and selected the appropriate Security Level before proceeding.", vbOKOnly, "Missing Required Fields")
 
Exit Sub
 
NoChange:
DoCmd.Close
Exit Sub
 
End Sub

[RuralGuy]

That's great! I see my task to get the poster to think the problem through so I generally just give a nudge in the right direction.

[ketbdnetbp]

Glad to hear you resolved the issue!!

One other thought comes to mind -

When I looked at your variable intMissInfo, I noticed that it talked about adding a new user. Just a note of caution, with a new record, I don't think there will be an .OldValue for those controls. As such, it may throw an error under those circumstances. So, you may need to add some error handling to your code to cover that possibility.

All the best,

Jim

[gopherking]

Jim, I actually had gone back through all my code recently and added in error handling where it was missing. Thanks for bringing this up and reminding me!