473,714 Members | 2,485 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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?
Feb 5 '06 #1
6 4240
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
Feb 5 '06 #2
MLH
Searching for changestr on the mvp's site didn't
result in a hit - could it be something else?
Feb 5 '06 #3
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

Feb 5 '06 #4

"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
Feb 6 '06 #5
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
Feb 6 '06 #6
MLH
Hey, I like both of those, Anthony.

Thx!
Feb 6 '06 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
12838
by: Gene | last post by:
When entering a record in a form, I would like a value in a field of the previous record to be entered automatically into a different field of the current record. Which way should I go? Is it also possible to do this based on criteria from the previous record?
11
12478
by: David Messner | last post by:
Ok I know this is simple but the statement eludes me... I have a date field where I want the default value on the data entry form's date field to be the last date entered. I figure I can do this with a query but don't know what the criteria needs to be. Any help would be appreciated, Thanks
2
6087
by: Mark | last post by:
I am attempting to populate several textbox controls from VBA code. With each attempt, I get the following error: "The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing Microsoft Acess from saving the data in the field." Naturally, I have no BeforeUpdate events or validation rules defined for these fields. If I set
2
14826
by: PC Datasheet | last post by:
In a form/subform I have an unbound combobox in the form header that sets the value of a field in the subform so that it does not have to be entered for each record. In the BeforeUpdate event of the combobox I have some code to check the value against some criteria and if the new selected value fails the criteria, I cancel the BeforeUpdate event. I need the combobox to return to its previous value. For example, the current value is MyValue...
10
2317
by: MLH | last post by:
Would like to examine the value entered into a textbox on an A97 form during the BeforeUpdate event. The textbox may or may not have had an earlier entry in it prior to the latest value that is now in the process of being entered. What's the best way to refer to the value just typed that is about to update the textbox?
0
4236
by: Anonieko Ramos | last post by:
ASP.NET Forms Authentication Best Practices Dr. Dobb's Journal February 2004 Protecting user information is critical By Douglas Reilly Douglas is the author of Designing Microsoft ASP.NET Applications and owner of Access Microsystems. Doug can be reached at doug@accessmicrosystems.com. --------------------------------------------------------------------------------
2
1722
by: deancarstens | last post by:
Hi, I'm a novice with VB and I've written this pretty simple script, but for the life of me, I can't get the last bit of the script to work. It should compare the value entered by the user under the BeforeUpdate event and compare it to the value in the main form. It's doing that just okay, but when I want to reset the value using the original value (from the main form), it seems the BeforeUpdate event prevents me from changing it...
5
3829
by: ApexData | last post by:
I have a bound textbox called txtMyBox with the current string value of "200". Once the user enters the textbox, and while the user is still in the textbox, I would like to check the changed value using a function to see if this changed value exists in a table. If it does, I want do give a Msgbox Warning and restore the original value. I thought that I would require the OnUpdate property of the textbox to do this, but I can't seem t...
5
3229
by: vsteshenko | last post by:
Hello, This is my second post to the any usernet group and the first one was posted to the wrong one. I am currently working on creating an order form for sales associates at my work to be used at conventions. I have a main form with two subforms. On the main form, there is a text box that displays the sum of total orders entered in the subform, . I'm trying to create another text box on the main form, that looks at the subtotal...
0
8803
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8711
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
9076
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9020
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7953
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6636
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4726
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2523
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2112
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.