Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    275
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Add an error message or capture the default error message code

    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?
    Thanks
    chuck

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,497
    Thanks
    3
    Thanked 42 Times in 42 Posts
    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.
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    275
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Thanks
    chuck

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,497
    Thanks
    3
    Thanked 42 Times in 42 Posts
    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.
    Wendell

  5. #5
    2 Star Lounger HiTechCoach's Avatar
    Join Date
    Sep 2011
    Location
    Oklahoma City, OK
    Posts
    162
    Thanks
    0
    Thanked 24 Times in 24 Posts
    Quote Originally Posted by chuckrau View Post
    ... 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.
    Boyd Trimmell aka HiTechCoach (Access Information here)
    Microsoft MVP - Access Expert
    "If technology doesn't work for people, then it doesn't work."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •