Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2009
    Posts
    67

    Unanswered: No duplicate in form whitin the same day.

    Hello,

    I have table name TbleProductSales and it has these fields [IDNumber], [SerialNumber], [EndingNumber] and [SaleDate]. What I like to do is prevent duplicate SerialNumber whitin each day's SaleDate for example SerialNumber 123 can have ending number only once a day. IDNumber is the key and it is auto number SerialNumber is text and SaleDate is date. Also if someone tries to enter duplicate within the same date I would like pop up message and delete the entry. I tried little bit in BeforeUpdate but I'm not good in codes and I recently started using access so i need help.

    Thanks

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    If I am interpreting this correctly, you need to set a unique index across two fields, but those fields are not the primary key. I think there is an easy way to do this, but I can't remember it off the top of my head atm.

    I'll have a fiddle during my day and see if I can remember
    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
    Apr 2009
    Posts
    67
    What you said is exactly what I need. Thanks

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    What version of Access are you using?
    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

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    declare an index which is unique comprised of the relevant fields


    ...just a thought
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Realise that Healdem, but not sure on exactly how to do that without it being a primary key
    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

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you can declare what ever indexes you like selecting the lightening button in the table design mode
    declare the index name, specify you want it unique, decalre the columns required
    jobsagoodun

    you can have multiple unique indexes but only one primary index which is also unique
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Apr 2009
    Posts
    67
    But how do I do that. The serial number can be duplicate only when the sale date is diffrent in another way no serial number duplicate in the same day.

  9. #9
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    If you set up an index across more than one field, the indexer only looks for unique combinations instead of unique values. This will allow you to have as many entries for each serial number as there are days.

  10. #10
    Join Date
    Apr 2009
    Posts
    67
    When I did I'm still able to enter duplicate serial number within the same day. which is what I need to avoid.

  11. #11
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Hmm.

    Can you post a screen grab of the indexes screen?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  12. #12
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Ah. Sussed it. You don't put multiple field names in a row, but one under the other without a new index name. Works a treat.

    Here's an Indexes dialog filled in with a non primary unique index over two fields:
    Attached Thumbnails Attached Thumbnails sshot.jpg  
    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

Posting Permissions

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