Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Preventing duplicates (2000)

    Hi,
    Is there a way to prevent the update of a field from having duplicates?
    The duplicates that I need to check should be in the following format:
    36KC425604
    I have set the field propert of this to no duplicates.
    The problem I'm having is that people are entering data in different formats '36KC4256.04' if the record already exists. Naturally this can be in any format:
    36KC0425604
    36KC4256004
    36KC425604/1, etc.
    I'd like to be able to check that these characters are not duplicated in any format. They all start with 36, then 2 letters, then from 3 to 7 numbers. But the digits from the KC to the 04 will always be in the same order..............give or take an added 0 at the beginning.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Preventing duplicates (2000)

    You would have to write a lot of VBA code for the Before Update event of the form, and set the Cancel argument to True if you find a duplicate. Judging from your examples, there can be many different formats, so it will be a lot of work.

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Preventing duplicates (2000)

    One thing you could do to help would be to use an input mask. This would restrict entry to numbers/letters in specific positions, and eliminate the other stray symbols. If you do have a unique index on that field, then this might be all you need to handle duplicates.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Preventing duplicates (2000)

    Thanks Mark.
    I've implimented this and it does limit the usage/or abuse of adding unique numbers.
    Still a bit of a pain theres no specific way of cross referencing the number without loads of code

Posting Permissions

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