Combo's beforeupdate and oldvalue

[Lars Brownies]

For my form's comboboxes I'd like to pop up a question when the user changes
an existing value. I found the following code from Allen Browne in thread
http://tinyurl.com/cxtka7:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
Dim bWarn As Boolean

With Me.Product_name 'I changed this field name
If .Value <> .OldValue Then
bWarn = True
strMsg = strMsg & "Changed customer from " & .OldValue & " to " &
..Value & vbCrLf
End If
End With
If bWarn And Not Cancel Then
If MsgBox(strMsg, vbOKCancel + vbQuestion, "Confirm change") <> vbOK
Then
Cancel = True
Me.Undo
End If
End If
End Sub

However, this only fires before the form gets updated. I need this to be
fired right after the selection in the combo is made. So I added:

Private Sub Product_name_BeforeUpdate(Cancel As Integer)
Call Form_BeforeUpdate(0)
End Sub

It does work to some extent, but if a user changes the value more than 1
time, the previous values haven't been saved yet so the oldvalue remains the
same and the comparison is not working.

Any thoughts on how to make this work? Should I declare a global variable
that gets set when the combo gets focus and when the value is changed?

Thanks,

Lars

 

[Lars Brownies]

I put Allen's code in a function and added a form variable varOld. First
tests turn out it works. Any comments or tips are appreciated.

Function fCheckCancel(strControl As String, varNew As Variant) As Boolean
Dim strMsg As String
fCheckCancel = False
With Me.Controls(strControl)
If Not IsNull(varOld) Then
If .Value <> varOld Then
strMsg = "Changed " & strControl & " from " & varOld & " to " &
varNew & "?" & vbCrLf
If MsgBox(strMsg, vbOKCancel + vbQuestion, "Confirm change") <>
vbOK Then
fCheckCancel = True
.Undo
Else
varOld = .Value
End If
End If
End If
End With
End Function

Private Sub Product_name_BeforeUpdate(Cancel As Integer)
If fCheckCancel("Product_name", Me.Product_name) = True Then
Cancel = True
End If
End Sub

Private Sub Product_name_GotFocus()
varOld = Me.Product_name
End Sub