Find error number for validation error

Question

I have a table where some fields have a validation rule set, and a validation message. Data entry is done in a form, and when the data validation rule is broken then the validation error message is displayed in a pop-up. All good so far.

However I then want to have the form text box or combobox for the field that is triggering the validation message be highlighted on the form e.g. with a different background colour.

I assumed I would need to do this in the On Error procedure for the form, and specify the error number. But I have no idea how to find the error number? E.g. this method: How to Change Table Validation Error Message in MS Access

Perhaps I am going about this completely the wrong way and instead of specifying the error messages in the table properties I should be setting up it up with VBA?

V grateful if anyone can point me in the right direction with this as I am still a beginner :)

Answer 1

The error number you seek is 7753 - this is returned by the DataErr (1st argument) of the event procedure for the On Error event.

However, I personally don't like using Validation Rules in Access, and prefer to test the validity of input through VBA as part of the data submission (i.e. when the user clicks on a control to submit their data)

For example, this approach is similar to web forms flagging invalid fields when the form is submitted, as opposed to locking a user into a particular field until valid data has been specified.