Results 1 to 14 of 14
  1. #1
    Join Date
    Sep 2011
    Posts
    5

    Unanswered: How do I stop a user from entering duplicate data into a particular field?

    Hi

    Can someone help me please.

    I am trying to prevent duplicate data from being entered into a form. For instance, for bank account details, if that same bank account number is already in the database, then the user will be alerted and prevented from adding a new record with this data.

    I am relatively new to VBA, so do not know how to code this.

    Any help will be welcome.

    Thank you.

  2. #2
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Easiest way to achieve this is to:

    1) Open up your table in design view
    2) Click on the field name you don't want duplicates in ('bank account number', for example)
    3) At the bottom of the screen, select where it says: 'Indexed', 'Yes (No Duplicates)'
    4) Save & exit

    Now, when you try inserting 123 to a new account on your form, when account 123 already exists, an error will be displayed and the data will not be saved.


    Alternatively, you could make the account number the primary key (probably a good idea anyway), which will not allow duplicates as standard.


    This method isn't very thorough, but doesn't require writing any code, so will be easier to maintain for yourself.
    Looking for the perfect beer...

  3. #3
    Join Date
    Sep 2011
    Posts
    5

    How do I stop a user from entering duplicate data into a particular field?

    Hi kez1304

    thank you for your response.

    I have tried this and it works, but I would like to add a custom message, that would simply say 'Bank account number already exists'. I tried writing it in the Validation Text but that did not work as I have since figured that it only works if you have a Validation Rule.

    I guess the only way is to go down the VBA route and I don't mind. Have you got any further suggestions?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Set a UNIQUE CONSTRAINT on the column in the table definition. This is the ultimate guaranty that no duplicate values can be entered into the column, whatever happens.

    2. Let's suppose that the textbox used to enter the bank account number is named Txt_AccNumber and that the related column is named AccNumber in a table named TblAccounts, you can use this code when you want to check if the account number entered in Txt_AccNumber already exists:
    Code:
    If DCount("AccNumber", "TblAccounts", "AccNumber = " & Me.Txt_AccNumber.Value) > 0 Then
        ' --> This number already exists.
    Else
        ' --> Number not found.
    End If
    If AccNumber is not defined as numeric, the test becomes:
    Code:
    If DCount("AccNumber", "TblAccounts", "AccNumber = '" & Me.Txt_AccNumber.Value & "'")...
    Have a nice day!

  5. #5
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    With the form in question open:

    1) Push Alt+F11
    2) Copy and Paste:

    Code:
    Private Sub Form_Error(DataErr As Integer, Response As Integer)
    
        If DataErr = 3022 Then
            MsgBox ("Bank account number already exists!")
            Response = 0
        End If
    
    End Sub
    Anywhere after:

    Code:
    Option Compare Database

    3) Check out your cool custom error message.
    Looking for the perfect beer...

  6. #6
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    If you need more help, look at "DemoDupliNHSA2000.mdb"
    (attachmnet, zip), open Form1 and try.
    Look at VBA in form.
    Attached Files Attached Files

  7. #7
    Join Date
    Sep 2011
    Posts
    5
    Thanks Guys

    all very helpful - it is working now. I used the indexing one for some other fields and Sinndho's code for the bank account number field - they all work.

    MStef-ZG, I will have a look at db u sent and c what I can glean from there.

    Thanks a zill.

    :-)

  8. #8
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Welcome mate.

    Probably best for the account numbers to use Sinndho's code. You shouldn't run into problems using the indexing method, but as I said it's not 100% thorough.

    The UNIQUE constraint or a Primary Key are the only completely certain way to prevent duplicate data.

    For most things it's just the quickest and easiest method to add a bit of indexing.

    Also I've never been able to find a method to add the UNIQUE constraint to a table once it's been created..?

    EDIT: Would appear that adding constraints after creation is only possible using the Primary Key method in Access 2000. The ability to add constraints only appears in 2003+
    Last edited by kez1304; 09-06-11 at 12:03.
    Looking for the perfect beer...

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    @kez1304: I wasn't sure, so I checked on a machine with Office 2000 (Access 2000 9.0.2720) installed and this works:
    Code:
    strSQL = "ALTER TABLE Table1 ADD CONSTRAINT CU_SysCounter UNIQUE (SysCounter);"
    CurrentDb.Execute strSQL
    Have a nice day!

  10. #10
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Quote Originally Posted by Sinndho View Post
    @kez1304: I wasn't sure, so I checked on a machine with Office 2000 (Access 2000 9.0.2720) installed and this works:
    Code:
    strSQL = "ALTER TABLE Table1 ADD CONSTRAINT CU_SysCounter UNIQUE (SysCounter);"
    CurrentDb.Execute strSQL
    Hmm... I see what you mean, one thing that does occur to me now, that I've never had to worry about before is; Is there a console somewhere inside Access? Or to do this, would I have to make a tmp form with a command button on it?
    Looking for the perfect beer...

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    @kez1304: Don't tell me about it! I usually work with Access 2003 but I have several virtual machines installed with Access 97, 2000, 2007 and 2010. Sometimes I provide an answer and the response is "it does not work". I then realize that most of the time it's because the version of Access is different from the one I used.
    Have a nice day!

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by kez1304 View Post
    Is there a console somewhere inside Access?
    you mean to run SQL statements?

    yes, there is, it's called Query SQL View
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jul 2016
    Posts
    1

    prevent duplicate entries into database column

    Quote Originally Posted by Sinndho View Post
    1. Set a UNIQUE CONSTRAINT on the column in the table definition. This is the ultimate guaranty that no duplicate values can be entered into the column, whatever happens.

    2. Let's suppose that the textbox used to enter the bank account number is named Txt_AccNumber and that the related column is named AccNumber in a table named TblAccounts, you can use this code when you want to check if the account number entered in Txt_AccNumber already exists:
    Code:
    If DCount("AccNumber", "TblAccounts", "AccNumber = " & Me.Txt_AccNumber.Value) > 0 Then
        ' --> This number already exists.
    Else
        ' --> Number not found.
    End If
    If AccNumber is not defined as numeric, the test becomes:
    Code:
    If DCount("AccNumber", "TblAccounts", "AccNumber = '" & Me.Txt_AccNumber.Value & "'")...
    Please what do you mean by Dcount?

  14. #14
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by amoeba202 View Post
    Please what do you mean by Dcount?
    DCount() is an Access Domain Function that returns the number of rows for a specified domain (i.e. a data set).
    The syntax is:
    Code:
    CountOfRows = DCount("[Column]","[Table]", "[Criteria[")
    e.g.
    Code:
    NumberOfPaidInvoices = DCount("*", "[Invoices]", "[AmountDue] = 0")
    with [Criteria] being optional. See Access help for details.
    Have a nice day!

Posting Permissions

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