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

    Unanswered: help with Key field in form

    I have table that I put in product information and it has these fields
    [ProdNumber] [ProdName] [FaceValue] and the table name is [TblProductInfo]. The ProdNumber is the key and i set up form to enter new products that comes in. How can I code it when someone tries to enter duplicate that message will come up and tell them the product is already in instead of access standard message also if the product is already in they can leave the form. right now when i tried to enter duplicate I got the standrd error and when i delete i got the error message says Key field can't be null. Please help.

  2. #2
    Join Date
    Mar 2007
    Posts
    277
    Do a DCount() in the BeforeUpdate event of the [ProdNumber] control and Set Cancel = True and display a message when there is a duplicate. Me.UnDo will clear any changes that have been made so you can leave the form without a new record.
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I'd put in some validation code AfterUpdating the ProdNumber.

    Psuedocode:

    If the entered prodnumber exists already then
    Show a message to the user, informing them.
    Undo all changes to the current record.
    End If
    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

  4. #4
    Join Date
    Apr 2009
    Posts
    67
    I'm new to this and i don't how to code this.

    Thanks

  5. #5
    Join Date
    Mar 2007
    Posts
    277
    I'm pretty sure StarTrekker intended to say the *BeforeUpdate* event rather than the AfterUpdate event. Here's a help link for DCount().
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Before or After updating the ProdNumber -- it makes little difference imo

    Quote Originally Posted by hmho
    I'm new to this and i don't how to code this.
    What a perfect little project to get you started with VBA coding!!

    You'll need to find out about:

    IF statements
    the DLOOKUP function
    The MSGBOX statement (or function).
    The UNDO method

    See what you can figure out, post your code if you get stuck!
    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
    Mar 2007
    Posts
    277
    You can hold the focus in the control in the BeforeUpdate event but not in the AfterUpdate event.
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yup, that's one of the little differences :Þ
    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
  •