Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2004
    Posts
    48

    Unanswered: Preventing Duplication in database

    In an Access database, I have a subform attached to a main form linked by the Service ID. There is a combo box on the subform where the user can select names of adults who received a specific service (the bound column in the combo box is the Adult ID). Jane Smith, for example, could be chosen more than once provided that the Service IDs are different. The user shouldn’t be able to enter her name twice under the same Service ID. Right now, the user is able to do that. How do I prevent that?

    Thanks in advance for any help offered.

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Make both the AdultID and the ServiceID primary key to the table that the form enters into.

    Either that or you'll have to write some code to check for it each time the form data is saved.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Feb 2004
    Posts
    214

    ...

    I would go with Startrekker's suggestion with the pk's. Make sure when setting the pk's though that you adjust the index to not allow duplicates.
    You know, I'm sick of following my dreams, man. I'm just going to ask where they're going and hook up with 'em later

  4. #4
    Join Date
    Aug 2004
    Posts
    48

    Preventing duplication

    Thanks Startrekker and Mr. Blonde.

    I'm not having much luck assigning the primary key to the suggested fields.

    How involved is the code to prevent the duplication?

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    A lot more involved than setting a PKF.

    I'm not having much luck assigning the primary key to the suggested fields.
    Why? What errors are you getting? There's probably already duplicate violations... or your table design is wrong.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the most likely cause in my mind is going to be preexisting duplicates

    so I'd suggest you create a new table
    apply tyhe changes to the desing of that table to make whatever is required as unique
    then transfer all the old data into the new table
    review any or all duplicates and try to resolve which is the moszt recent row

    if you already have prexisting relationships on the table, what you could do instead is sanitise the data
    manually search through every row (using a query) to find duplicates, resolve the discrepancies and move on.

    applyiong a code fix would in my view be a unstaisfatory kludge
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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