ACCESS 2000 - bad input

[Pres Holod]

When entering an account number I ant a message to appear if the account
number is not 10 digits. I have figured out how to get the message in a
macro BUT I dont want the user to be able to proceed to another field
until the account number is fixed. What do I have to add to the code?

Len([AccountNo])<>10
Preston Holod

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Mal Reeve]

In the design view....look for the Before Update event...
there uyse your
If Len([AccountNo])<>10 then
msgbox "Bad user...stop it...do it again"
AccountNo = "" (assuming this is a text field)
AccountNo.setfocus (Puts them right back in it again)
end if

Something to consider....what if they decide...to heck with it...and want to
stop...
you might want to allow for a CANCEL

HTH
MAL.
"Pres Holod" <ho***@optonline.net> wrote in message
news:40**********************@news.newsgroups.ws.. .

When entering an account number I ant a message to appear if the account
number is not 10 digits. I have figured out how to get the message in a
macro BUT I dont want the user to be able to proceed to another field
until the account number is fixed. What do I have to add to the code?

Len([AccountNo])<>10
Preston Holod

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Pres Holod]

Mal
I typed this in the Before Update in the Control AccountNo and after
typing in a 5 digit number the message "Bad user...stop it...do it
again" appeared. GREAT, RIGHT? After hitting OK, a RunTime error
2147352567 came up - The Macro or Function set to the Before Update
property for this field is preventing Microsoft Access from saving the
data in the field. When I hit OK, I am back in the ACCOUNTNO field.
Bit I'd prefer to get there without the RunTime Error. Any ideas?
Preston Holod

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Allen Browne]

1. Set the text box's BeforeUpdate property to:
[Event Procedure]

2. Click the Build button (...) beside this.
Access opens the code window.

3. Enter:

Private Sub AccountNo_BeforeUpdate(Cancel As Integer)
If Len(Me.AccountNo) <> 10 Then
Cancel = true
MsgBox "Enter 10 digits, or press <Esc> to undo."
End If
End Sub

Setting Cancel to True stops the user from exiting the box.

BTW, if you need to store some accounts with leading zeros, AccountNo must
be a Text field (not a Number field) in your table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Pres Holod" <ho***@optonline.net> wrote in message
news:40**********************@news.newsgroups.ws.. .

When entering an account number I ant a message to appear if the account
number is not 10 digits. I have figured out how to get the message in a
macro BUT I dont want the user to be able to proceed to another field
until the account number is fixed. What do I have to add to the code?

Len([AccountNo])<>10
Preston Holod

[Pres Holod]

Alan: Thanks for your input

I used your code as discussed and it worked. HOwever, when the Message
appears when the account number does not equal 10 and I press OK, and
Access message appears stating The Value in the field vilates the
validation rule for the record or field.............etc". When I hit
OK, I am then sent back the the AccountNo field.

Preston Holod

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Allen Browne]

The behavior you describe suggest that you are using both a Validation Rule
and also the BeforeUpdate event procedure. Try one or the other.

You won't be able to get out of the text box if the validation rule is not
met, and you won't be able to get out of the text box if the BeforeUpdate is
canceled. Either should do the job.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Pres Holod" <ho***@optonline.net> wrote in message
news:40**********************@news.newsgroups.ws.. .

Alan: Thanks for your input

I used your code as discussed and it worked. HOwever, when the Message
appears when the account number does not equal 10 and I press OK, and
Access message appears stating The Value in the field vilates the
validation rule for the record or field.............etc". When I hit
OK, I am then sent back the the AccountNo field.

Preston Holod

[Pres Holod]

Unfortunately, there is no validation rule. Any other ideas?

Preston Holod

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Larry Linson]

Set the Cancel argument to True before exiting to Cancel the Update and you
will avoid that message. Mal must have forgotten, or assumed you'd know to
do so.

Larry Linson
Microsoft Access MVP

"Pres Holod" <ho***@optonline.net> wrote in message
news:40**********************@news.newsgroups.ws.. .

Mal
I typed this in the Before Update in the Control AccountNo and after
typing in a 5 digit number the message "Bad user...stop it...do it
again" appeared. GREAT, RIGHT? After hitting OK, a RunTime error
2147352567 came up - The Macro or Function set to the Before Update
property for this field is preventing Microsoft Access from saving the
data in the field. When I hit OK, I am back in the ACCOUNTNO field.
Bit I'd prefer to get there without the RunTime Error. Any ideas?
Preston Holod

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Pres Holod]

Neither code seems to work perfectly. I still get runtime errors with
Mals code and the validation error with Allens. Help!!!

Preston Holod

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!