Using BeforeUpdate for data validation, reset to default value

[Guest]

Hello, here is a sample of my code. I'm trying to reset LuSN to the default
value(blank) if the record is not found. It works great if the record is
found utilizing the LuSN_Exit Sub. Is there a way to goto the LuSN_Exit Sub
from within the LuSN_BeforeUpdate Sub? Or is there a better way to reset it
to the default value?

Private Sub LuSN_BeforeUpdate(Cancel As Integer)
' Find the record that matches the control.
' Lookup SN (Combo48)
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[SN] = '" & Me![LuSN] & "'"

If rs.NoMatch Then
Dim strMessage As String, strTitle As String
strMessage = "Error Serial Number Not Found!"
strTitle = "Record Not Found"
Cancel = True
MsgBox strMessage, vbInformation + vbOKOnly, strTitle
End If
End Sub
Private Sub LuSN_AfterUpdate()
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[SN] = '" & Me![LuSN] & "'"

If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
End If
End Sub

Private Sub LuSN_Exit(Cancel As Integer)
Me.LuSN = Me.LuSN.DefaultValue
End Sub

 

[TC]

The way you would normally do this in Access is just to display the
message and then set the Cancel parameter - as you already do. At that
point, the user knows that the value he entered, has been rejected. He
can then press the Esc key once to return to the previous value -
blank, or whatever else it might have been. This is a standard keypress
in Access. You really don't need - and probably shouldn't try - to
erase the value via AfterUpdate.

For example, perhaps he meant to type 123456 but accidentally typed
123457. After your message, he won't thank you if you change the value
back to blank! He'll just want to fix the last digit, & send it again.

HTH,
TC

 

[Guest]

Thanks for your input, the problem is if the user does not use the ESC key
and instead back spaces to remove all of the data (Error Message, SN not
found). Another problem I have seen is if the ESC key is pressed twice by
mistake, this of course erases any data entered onto the form prior to
searching for the new serial number. Fortunately I'm the only user of this
database for now, but I'm trying to make it as robust as possible before
releasing it. If there is no easy way to reset it to the default value then
I will just have to leave it as is, thanks again.

 

[TC]

Yes, I do agree that it's a problem if they press Esc several times.

Try this:

beforeupdate:
msgbox "blah blah"
cancel = true
screen.activefield.undo

afterupdate:
(delete it)

I don't have Access here to check the details of screen.activefield. If
that doesn't work, try Me![blah].undo, where blah is the name of the
control in question.

*** No value that you can place into the field - either manually (by
typing it in), /or through code/ - will "undo" the update. ***

And if you Cancel the beforeupdate call, afterupdate will not fire. So
your current approach is bound to fail. Try the Undo method instead.

HTH,
TC

 

[Rick Brandt]

Thanks for your input, the problem is if the user does not use the
ESC key and instead back spaces to remove all of the data (Error
Message, SN not found). Another problem I have seen is if the ESC
key is pressed twice by mistake, this of course erases any data
entered onto the form prior to searching for the new serial number.
Fortunately I'm the only user of this database for now, but I'm
trying to make it as robust as possible before releasing it. If
there is no easy way to reset it to the default value then I will
just have to leave it as is, thanks again.

I agree with TC. Virtually ANY windows app that validates a field leaves the
incorrect entry in the box after alerting the user so they can SEE what is wrong
with it.

If I intended to type 123456 and accidentally typed 123457 and your form told me
that what I had entered was incorrect (and cleared the entry) I am now left
scratching my head. "Gee I'm pretty sure 123456 is a valid entry. This program
has bugs in it!"

 

[Guest]

Thanks for the advice from both of you, here's what I added to BeforeUpdate:
Screen.ActiveControl.Undo
This keeps the text in the field and if I tab or move to any other field the
text entered is removed. This is perfect, thanks again.