Results 1 to 9 of 9
  1. #1
    Magnolia1 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    May 2016
    Location
    USA
    Posts
    25

    Issue with duplicates being added to the table if clicking on an append button more then once

    Hello Everyone,


    I am very new to VBA. I was wondering is their a way to add an additional security step to a button that is places on a form?
    I have a form with an append button on it. The button is used to add new records to a table that this form linked to. The issue is that if a user clicks on this button more then once the same records will be added to the table again therefore duplicate records will be added to the table.
    I would like to add a code that would prevent the user from clicking on this button for the second time to prevent this issue. Can this be done in access? If yes, Can someone please help me to write this code?
    Thank you

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Something I will do sometimes is to use the Click event of the button to set focus in a safe place, disable the command button, and then execute the code.

    So it might look like ...
    Me.SomeOtherControl.SetFocus
    Me.DoNotClickAgainButton.Disable
    'Begin logic to do stuff

  3. #3
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Also, what is the Primary Key of the underlying table?
    If it is one or more of the fields in the tables (and not an Autonumber), this would automatically prevent duplicate records from being entered, as it would cause a key field violation.

  4. #4
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    When they hit Append before your code to add the values, you could do a DLookUp on the field(s) and if it already exists, then put up message.

    x = DLookup("FieldName" , "TableName" , "Criteria= 'string'" AND "Criteria2= 'string2'")

    If not isnull(x) thenMsgbox "Record already exists."
    EndEnd If

    Numeric

    DLookup("FieldName" , "TableName" , "Criteria = n")

    String
    DLookup("FieldName" , "TableName" , "Criteria= 'string'") Notice the single apostrophe before and after the string value.

    Date
    DLookup("FieldName" , "TableName" , "Criteria= #date#")

  5. #5
    Magnolia1 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    May 2016
    Location
    USA
    Posts
    25
    Bulzie, Thank you for your response. I will test your approach tomorrow and let you know if I will have any questions or problems.
    Thanks again, I appreciate your help.

  6. #6
    Magnolia1 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    May 2016
    Location
    USA
    Posts
    25
    Hello ItsMe,
    Thank you for your response. I will try to use your approach tomorrow and will let you know what the result is.
    I appreciate your response.

  7. #7
    Magnolia1 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    May 2016
    Location
    USA
    Posts
    25
    JoeM,
    Thanks for your response. The PK in my underlying table is not an autonumber. But unfortunately the dups are being created when/if the user clicks on the append button again.

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What is it that makes the record a duplicate?
    Take those fields and make a unique composite index.

    With some code in the button click event you could
    -do a DCount to see if the combination exists already and display a message
    if DCount(your criteria) >0 then a duplicate exists....

    with error handling you could check for error 3022 (duplicate) and display a message.

    You may find this info/material useful.

    Duplicates are not something you want in your database. Most people would make prudent use
    of primary Key and unique composite indexes to ensure no duplicates could be entered. the database software
    would prevent the duplicate.

    Disabling a button is one approach. But it reminds me of gluing a thumb tack (upside down) to a keyboard key that the user "must not click".

  9. #9
    Magnolia1 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    May 2016
    Location
    USA
    Posts
    25
    Thanks to all of you for your input and help. I was able to fix the issue with my append button by using Bulzie's approach. It works great. I appreciate the time you all spent on trying to help me and sharing your knowledge.

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

Similar Threads

  1. Replies: 2
    Last Post: 05-10-2016, 08:53 AM
  2. Automating Button Clicking
    By csn102 in forum Programming
    Replies: 3
    Last Post: 11-05-2015, 02:01 PM
  3. Replies: 2
    Last Post: 09-15-2015, 03:08 AM
  4. Replies: 3
    Last Post: 09-11-2015, 03:22 PM
  5. Comments when clicking a button
    By Juan4412 in forum Programming
    Replies: 4
    Last Post: 06-23-2011, 04:34 PM

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