Add an error message or capture the default error message code

Topic

New Reply

I have a form in access 2013 that fills in city and county when a zip code is entered from tblZipCodes. After all the information is entered into the last field access will give an error message if the zip code entered is not a valid code. I would like to give my own error message on the after update event of the zip code field. How can I do that?

Reply | Quote

Replies

Error handling is a topic that’s really too big to try to deal with in a forum thread, so let me suggest you take a look at Allen Browne’s Microsoft Access tips: Error Handling in VBA and the more detailed FMS article by Luke Chung Error Handling and Debugging Tips and Techniques… that Allen refers to. Then come back with questions and we will do our best to help.

Reply | Quote

Thank You Wendell. The articles you referred me to will be very helpful in the future. But if I may, let me try one more time to explain what I am looking for. The form I am referring to is based on a query that joins the table for applicant information, name, address, zipcode, etc. to the ZipCodes table. On my form the tab order for fields places the zip code field before the city and county fields as these are filled in automatically after the zipcode is enterd. However, the form continues on to a subform for communications information which means after the zip code is entered I continue to tab through the city and county fields to the subform. Once the subform has the focus Access will attempt to save the record at which time it gives a message if the zip code entered is not in the zipcodes table. The error code is 3101, but rather than capturing that and replacing the Access message with my own, I thought I could use the after update event of the zip code field to check if the data entered matches a record in the zipcodes table. I have been away from Access for over 6 years and never knew a lot to start with, but I remember we would open a recordset, in this case the zipcodes table and check if the data entered matched a record in the table. I just can’t remember how we did that.

Reply | Quote

… The form I am referring to is based on a query that joins the table for applicant information, name, address, zipcode, etc. to the ZipCodes table. On my form the tab order for fields places the zip code field before the city and county fields as these are filled in automatically after the zipcode is enterd.

Normally the query of a form is based on a single table when you are entering data.

I would recommend WendellB’s suggestion of using a combo box.

I also use WendellB ‘s suggestion of using code to verify the enter exists. I find it best to you the Before Update event not the After update event. The Before update can be cancelled if needed. You may be able to use the built in fiction DCount() or DLookip() to verify that record exists. If you need to do more then you could use a DAO recordset.

Reply | Quote

From the After Update event for the ZIP code text box, You can open a recordset, using DAO or ADO, and use the entered ZIP code as a criteria in a simple query that looks to see if that value exists in the table. You then check to see if the recordset is empty – I typically test to see if the EOF property of the recordset is True – and then display your custom error message if that is the case. You could then also set the values of the City and County text boxes in that same control, and set the focus to the subform.

Another option would be to use a combo box for the ZIP code, and use the NotInList event to issue a custom error message. That would avoid using a fair amount of VBA, and with the multi-column capabilities of combo boxes, you could also display the City and County info.

Reply | Quote