Results 1 to 7 of 7
  1. #1
    mainerain is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Location
    Maine, USA
    Posts
    146

    VBA - Error Handling for Run-Time Error: '3022' (duplicate)

    I'm a complete newbie to error handling. In a form I have a ComboBox that is tied to a table with a relationship of One to One, Indexed and No Duplicates.
    When I select from the ComboBox an item that already exists in another record and try to save I get an error, '3022', which is fine but I want to be able to show a message box stating they can't use the selection because duplicates are not allowed. They could just select OK and pick another item.

    Where do I put the Error Handler and what would the VBA look like? Thanks.

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    That's what I'd call a band aid solution. Best to simply eliminate the values from the list that are already used.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    mainerain is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Location
    Maine, USA
    Posts
    146
    Quote Originally Posted by Micron View Post
    That's what I'd call a band aid solution. Best to simply eliminate the values from the list that are already used.
    I like that, how do you propose to do that?

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I take it that the rowsource for your combo is a query because you're indicating there are 2 tables involved. You could try invoking the query wizard (Create tab of the ribbon) and building an 'find unmatched query'. You'll want to find/return values (in the table/query that is providing the combo list items) that are not in your table data records.

    It's rare that a 1 to 1 relationship is good design, so if you want to pursue that post some sample data of the 2 tables, indicating which fields are joined. Posting the rowsource of the combo might be a good idea as well. However, if the 1 to 1 is the result of splitting large field sets or a gazillion records into multiple smaller tables, whether in different back end files or not, then you're likely OK.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    mainerain is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Location
    Maine, USA
    Posts
    146
    Thanks for the reply. I changed to one to many. I created a 'find unmatched query'. So for the ComboBox it shows the items that don't have a match. On Dropdown I chose to view the serial numbers that are unmatched and bound the ID to the field. However after selection, the ID number is displayed in the field and not the serial number. I'm self taught and I have many holes in my knowledge. This concept is probably simple but haven't figured it out yet. Thanks for any assistance and patience. Will check later today.

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    the ID number is displayed in the field and not the serial number.
    If "the field" is in the many side of the relationship, that's the way it should be. Perhaps you should do some research on db normalization and when you get to the part that explains the use of the id number it will make sense. Here's a couple of links. If they don't work for you, there are lots of other ones you can review on the subject.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    It might be that you just need to adjust the combo's column width property to hide the first one (the bound ID column) by setting its width to 0.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Error Handling in Run time
    By Homegrownandy in forum Programming
    Replies: 26
    Last Post: 02-25-2019, 05:13 AM
  2. Replies: 14
    Last Post: 04-16-2018, 02:13 PM
  3. Run Time Error 3022...
    By BusDriver3 in forum Access
    Replies: 7
    Last Post: 10-23-2015, 04:17 PM
  4. The Error 3022 Duplicate Problem
    By boywonder381 in forum Programming
    Replies: 21
    Last Post: 09-01-2014, 11:27 PM
  5. Replies: 4
    Last Post: 02-13-2013, 10:46 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums