How best to modify value entered into textbox on form during BeforeUpdate event code?

[MLH]

Using A97.

Want to examine 17-char VIN entered by user. VIN codes
are alphanumeric and do not contain Oh's to prevent the
confusion that could result if zeros were misread as O's
or o's. So, if a user types a 17-char VIN into the textbox
that has an Oh in it (lower or upper case) - I would like
to change it to a zero during the BeforeUpdate code.

So far, I've not been able to accomplish this. I can examine
the 17-char value entered easily enough, but I cannot seem
to replace that which was entered with a new value that has
replaced Oh's with zeros. I get a 2115 error complaining that
"The FN set to the BeforeUpdate property is preventing Access
from saving data in the field".

I have tried a line DoCmd.CancelEve nt before attempting to
replace the bad data with good data but this error won't let
me proceed. How can I do this?

[Tim Marshall]

MLH wrote:

I would like
to change it to a zero during the BeforeUpdate code.

I have similar requirements sometimes. There may be a way to do this
with the before update event and the text property of the text box, but
I've had great difficulty changing the text property in this event.

What I do is use a replace function in the after update event (as well
as using the UCASE or LCASE function if you don't want mixed cases). On
the mvps org site, there's a Terry Kreft (I believe) function called
something like "changestr" which has very reliably performed the replace
function (available on A2000 and later).

I think working with the after update event for changing data values
will be less of a headache for you - it lowered by aspirin intake,
anyway! 8)
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

[MLH]

Searching for changestr on the mvp's site didn't
result in a hit - could it be something else?

[MLH]

For now, I think I'll use the KeyDown event to trap 'n prevent
Oh's from being typed. Although I'd rather handle it behind the
scenes for my users, this is the only thing I can get to work at
this time.

Private Sub SerialNum_KeyDo wn(KeyCode As Integer, Shift As Integer)
On Error GoTo ErrorSerialNum_ KeyDown
Dim ThisForm As String
ThisForm = Me.Name

If KeyCode = 111 Or KeyCode = 79 Then
DoCmd.CancelEve nt
MyString = "VIN codes are alpha-numeric codes that sometimes
contain zeros but NEVER contain Oh's. I'm "
MyString = MyString & "talking about the letter 'O'. So, please
continue typing in your VIN number but "
MyString = MyString & "refrain from typing O's (Oh's) in your
code. Type a zero instead."
MsgBox MyString, vbInformation, "No Oh's Allowed! - " & MyApp$ &
", rev. " & MY_VERSION$
End If

ExitSerialNum_K eyDown:
Exit Sub

ErrorSerialNum_ KeyDown:
Dim r As String, k As String, Message3 As String
r = "The following unexpected error occurred in Sub
SerialNum_KeyDo wn, CBF on " & ThisForm & "."
k = CRLF & CRLF & str$(Err) & ": " & Quote & Error$ & Quote
Message3 = r & k
MsgBox Message3, vbExclamation, "Unexpected Error - " & MyApp$ &
", rev. " & MY_VERSION$
Resume ExitSerialNum_K eyDown

End Sub

[Anthony England]

"MLH" <CR**@NorthStat e.net> wrote in message
news:4r******** *************** *********@4ax.c om...

For now, I think I'll use the KeyDown event to trap 'n prevent
Oh's from being typed. Although I'd rather handle it behind the
scenes for my users, this is the only thing I can get to work at
this time.

Private Sub SerialNum_KeyDo wn(KeyCode As Integer, Shift As Integer)
On Error GoTo ErrorSerialNum_ KeyDown
Dim ThisForm As String
ThisForm = Me.Name

If KeyCode = 111 Or KeyCode = 79 Then
DoCmd.CancelEve nt
MyString = "VIN codes are alpha-numeric codes that sometimes
contain zeros but NEVER contain Oh's. I'm "
MyString = MyString & "talking about the letter 'O'. So, please
continue typing in your VIN number but "
MyString = MyString & "refrain from typing O's (Oh's) in your
code. Type a zero instead."
MsgBox MyString, vbInformation, "No Oh's Allowed! - " & MyApp$ &
", rev. " & MY_VERSION$
End If

ExitSerialNum_K eyDown:
Exit Sub

ErrorSerialNum_ KeyDown:
Dim r As String, k As String, Message3 As String
r = "The following unexpected error occurred in Sub
SerialNum_KeyDo wn, CBF on " & ThisForm & "."
k = CRLF & CRLF & str$(Err) & ": " & Quote & Error$ & Quote
Message3 = r & k
MsgBox Message3, vbExclamation, "Unexpected Error - " & MyApp$ &
", rev. " & MY_VERSION$
Resume ExitSerialNum_K eyDown

End Sub

You can intercept this and automatically correct it if you feel this is the
correct policy. It might appear weird to your users that they press the
keyboard button for one thing, but another thing appears on the screen. But
anyway:

Private Sub SerialNum_KeyPr ess(KeyAscii As Integer)
If KeyAscii = 111 Or KeyAscii = 79 Then
KeyAscii = 48
End If
End Sub
Having said that, I use this technique for postcode/zipcode fields so that
you can only enter in upper case:

Private Sub txtTest_KeyPres s(KeyAscii As Integer)
KeyAscii = Asc(UCase(Chr(K eyAscii)))
End Sub

[Tim Marshall]

MLH wrote:

Searching for changestr on the mvp's site didn't
result in a hit - could it be something else?

That's bizarre, I can't seem to find the one I'm used to there,
either... However, there is a find and replace by Aldon Streeter at
http://www.mvps.org/access/strings/str0004.htm that looks like it'll do
fine. But the one I'm used to is a bit different and wasn't written by
Aldon. It must have changed since I took it from the Access Web.
However, the function in the URL I've provided looks pretty similar and
should work just fine for your purposes.

The other approach Anthony has mentioned looks good too, but I think
having a user's entry change after s/he's left the text box might be
less confusing (as per Anthony's caution) but maybe not. 8)
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

[MLH]

Hey, I like both of those, Anthony.

Thx!