Solved Getting an error using ValidationRule and ValidationText in a field

[pacctono]

Hello,

I have a field (email) with a ValidationRule and a ValidationText. I do not know if that is normal, but when the ValidationRule fails, Access give me an ERROR and try to open VBA.

Can I control the ValidationRule with VBA to inform the user that the entered data is not valid?

 

[CJ_London]

I would check the syntax and names for the rule and check the rule cannot return an error - it should only return true or false

 

[The_Doc_Man]

Can I control the ValidationRule with VBA to inform the user that the entered data is not valid?

Yes. Look up the concept of "Error Handler." The overview is that every error with Access has a number which is returned via the Err object, a built-in part of the Access environment. When an error is declared, the error handler is activated. Within the error handler, you can check Err.Number to see if it is the number that is returned for a validation violation and can choose to put up a message box, then resume execution in some appropriate location. If it happens that the error was not the one you expected, you have other possibilities including the choice to resignal the error and let Access handle it - but that would be another case where the user would see the error and Access would try to open a code segment. Note that if you use error handlers, they are like program interrupts and you need to be sure to properly dismiss them. It's not hard to do so, it is just that you need to remember to do so.

Here is a link to an article on error handlers. It is a decent place to start.

On Error statement (VBA)

Office VBA reference topic

learn.microsoft.com

Error handlers can get complicated if you reasonably expect more than one type of error with different options.

 

[pacctono]

Yes. Look up the concept of "Error Handler." The overview is that every error with Access has a number which is returned via the Err object, a built-in part of the Access environment. When an error is declared, the error handler is activated. Within the error handler, you can check Err.Number to see if it is the number that is returned for a validation violation and can choose to put up a message box, then resume execution in some appropriate location. If it happens that the error was not the one you expected, you have other possibilities including the choice to resignal the error and let Access handle it - but that would be another case where the user would see the error and Access would try to open a code segment. Note that if you use error handlers, they are like program interrupts and you need to be sure to properly dismiss them. It's not hard to do so, it is just that you need to remember to do so.

Here is a link to an article on error handlers. It is a decent place to start.

On Error statement (VBA)

Office VBA reference topic

learn.microsoft.com

Error handlers can get complicated if you reasonably expect more than one type of error with different options.

I use "On Error ...". The problem is that It give me an error number like -4233300098765

 

[The_Doc_Man]

That error is impossible for Windows or Access to have returned. I believe you either copied it wrong or somehow looked at the wrong thing. In order to express that number, you need not less than 40 bits (5 bytes) in a field that is 32 bits (4 bytes) wide. Even if you are on a 64-bit Windows running 64-bit Access, the Err structure still only returns a code of 32 bits. And if this is actually a Windows error, the O/S error reporting scheme is ALSO limited to 4 bytes for the "condition code" it is trying to report.

Recheck the error number. If that is what you actually see, please take a screen shot of it because that code makes no sense. Normally this kind of error would be in the 3300s range, most likely error 3349.

 

[CJ_London]

would also help to see what your validation code is and any description for the error you are getting

 

[pacctono]

That error is impossible for Windows or Access to have returned. I believe you either copied it wrong or somehow looked at the wrong thing. In order to express that number, you need not less than 40 bits (5 bytes) in a field that is 32 bits (4 bytes) wide. Even if you are on a 64-bit Windows running 64-bit Access, the Err structure still only returns a code of 32 bits. And if this is actually a Windows error, the O/S error reporting scheme is ALSO limited to 4 bytes for the "condition code" it is trying to report.

Recheck the error number. If that is what you actually see, please take a screen shot of it because that code makes no sense. Normally this kind of error would be in the 3300s range, most likely error 3349.

 

[CJ_London]

in English
You must enter a valid email or email address

So we need to see what you have written as the rule

 

[The_Doc_Man]

The error code (technically, "condition code") of 80004005 is an "unspecified non-Access run-time error".

The program associated with your mail operation didn't like something in some property or data field, which it detected when trying to use the data. I.e. it was not a compile-time error and not syntactically wrong to Access. The text of the message says the error is in the e-mail address field.

An "Unspecified run-time error" (some people call these "catch-all" errors) is what you get when the problem wasn't an error as far as Access is concerned, but IS an error for a supporting program - in this case, apparently a mail program. Access didn't think it was an error when you compiled the code, so it allowed you to specify whatever you put there. When the support program was called, it signaled an error to Access.

Access doesn't know the errors associated with that support program so it gave you the "unspecified error" code, which is simply telling you there was an error and the support program's error text was probably just passed through Access to you. For this reason, if you looked up error 80004005 via web search, you would get several different answers, each of which originated from a support program, not Access itself.

 

[pacctono]

I kown what it means. That text was put by me in the ValidationText field. I just would like to control it instead of getting an execution time error.

 

[The_Doc_Man]

Because the code is 80004005 instead of C0004005, you should be able to do that. (The C000... code is "fatal" and would crash Access.) You need to write an error handler routine.

Error Handling and Debugging Tips and Techniques for Microsoft Access, VBA, and Visual Basic (VB6)

Implementing Error Handling and Debugging Techniques for Microsoft Access, VBA, and Visual Basic application development

www.fmsinc.com

FMS, Inc. has some nice Access articles. This might get you started.

 

[pacctono]

The error code (technically, "condition code") of 80004005 is an "unspecified non-Access run-time error".

The program associated with your mail operation didn't like something in some property or data field, which it detected when trying to use the data. I.e. it was not a compile-time error and not syntactically wrong to Access. The text of the message says the error is in the e-mail address field.

An "Unspecified run-time error" (some people call these "catch-all" errors) is what you get when the problem wasn't an error as far as Access is concerned, but IS an error for a supporting program - in this case, apparently a mail program. Access didn't think it was an error when you compiled the code, so it allowed you to specify whatever you put there. When the support program was called, it signaled an error to Access.

Access doesn't know the errors associated with that support program so it gave you the "unspecified error" code, which is simply telling you there was an error and the support program's error text was probably just passed through Access to you. For this reason, if you looked up error 80004005 via web search, you would get several different answers, each of which originated from a support program, not Access itself.

Ok, thanks!

Can I check with vba that a Validation Rule (defined in the table design) is accomplished or I have to specify it in the vba code with a Before Update in the control?

 

[Pat Hartman]

Validation rules are very limited in what they can actually do. They are enforced by the database engine which has no access to VBA so you can't use your own UDF's or even any VBA functions.

I don't see anywhere that you have posted the actual Validation rule.

 

[pacctono]

Validation rules are very limited in what they can actually do. They are enforced by the database engine which has no access to VBA so you can't use your own UDF's or even any VBA functions.

I don't see anywhere that you have posted the actual Validation rule.

Es Nulo O ((ALike "%_@_%._%") Y (NoEs ALike "%[ ,;]%"))

Sorry, I do not know inenglish. Maybe, something like this:
isNull Or ((Like "%_@_%._%") And (Not Like "%[ ,;]%"))

 

[CJ_London]

What is the datatype for the field? A hyperlink? Or just text?

and just to be clear, what is the back end? Access or sql server or something else?