Bound textbox control validation rules

[ben h]

Hi,
I'm new to Access, and working on an Access 2000 mdb. A form needs some
validation and error checking and already I'm stuck!
It seems that with bound controls (bound to a linked table in this case)
that the Validation rule in the control's property sheet don't take
effect, instead the 'in-built' validation rule applies?

The field in the db table that the control is bound to is a text(100),
so when the text in the form gets to more than that, a message generated
from Access pops up "The text is too long to be edited".
I want to replace that with a much more informative message - how do I
do this?
Property sheet? VBA?

Ben

 

[Allen Browne]

Use the Error event of the form to trap the engine-level error messages.
Write code to examine the DataErr, and show your own MsgBox for the values
you want to customize.

 

[ben h]

Use the Error event of the form to trap the engine-level error messages.
Write code to examine the DataErr, and show your own MsgBox for the values
you want to customize.

Allen, thanks for quick response.
The Error event will be OK for generic messages, but what if each text
box needs a specific error message? I think I'll need to set Response to
acDataErrContinue and also create an OnError event for the text box?
Does that sound about right?


ps. beautiful day in Perth today

 

[Allen Browne]

Haven't actually done this, Ben, but if you can assume that the error is
triggered by typing into the control (not programmatically assigning
something), it would be something like this.

Use:
Select Case Err.Number
to respond to specific DataErr values.

Examine Form.ActiveControl to see which control has focus.

Examine the Control Source property of that control, to determine the Field
it is bound to.

If the Control Source is not blank (unbound) or an expression (i.e. Not Like
"=*"), examine look at the Field of the RecordsetClone of the form. You can
then get at the Size and Type of the Field.

The last part of CarryOver() function in this link illustrates how to read
these Properties of the Field in the RecordsetClone of the Form, based on
the Control Source:
http://allenbrowne.com/ser-24.html

 

[ben h]

Haven't actually done this, Ben, but if you can assume that the error is
triggered by typing into the control (not programmatically assigning
something)

The actual case I want to capture is when text from the clipboard is
pasted into the text box.

I have added this to the Form_Error event code:
Debug.Print DataErr; AccessError(DataErr)

However, if I copy a large amount of text, say 200 characters, and paste
that into the bound text field on the form (bound to a 100 character
'field' in the linked table) then the default error message appears "The
text is too long to be edited.", and the Form_Error event doesn't seem
to fire. This error message I want to capture and customise somehow.

Note that if I try to type from the keyboard into the control, then the
control itself simply stops me from typing any more than 100 characters
- due to the database level validation I assume.

Ben

 

[Allen Browne]

I don't know what else to suggest, Ben.

I would have expected Form_Error to fire on an engine-level error.

 

[ben h]

I don't know what else to suggest, Ben.

I would have expected Form_Error to fire on an engine-level error.

No problem, thanks Allen for your help.