Results 1 to 6 of 6
  1. #1
    kelliejean is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    21

    prevent duplicate client entry

    Hello brains trust!

    I'm just starting to dip my toe into VBA (huge learning curve!) to sort a couple of problems with my db and went looking for a way to prevent our team entering a duplicate client (it's happening a lot!). I found this: http://www.iaccessworld.com/prevent-...ry-two-fields/ and have had a go at trying to set this up without luck.

    The two fields in my 'add new client' form are First name and Surname. I have the 'afterupdate' action on the Surname field as I assumed it would trigger after adding first name then surname.

    This is where I've got to - nothing happens at all - no error, just nothing. No doubt I've mucked up some crucial component!

    Ideally all I want is for the db to check the provided first name and surname combination and send a msg when it finds a repeat. We often have clients with the same name so I don't want it to do anything else other than warn the person that they could be entereing a duplicate client and to double check. I could combine with birth date to help with this but sometimes dates go in wrong so I thikn I'd just prefer that peeps get a msg when its a repeat name so they can check.

    Any help would be greatly appreciated! I need newbie speak - sorry in advance! lol

    Private Sub Surname_AfterUpdate()

    Dim NewCustomer, NewFirstName, NewSurname As String
    Dim stLinkCriteria As String
    Dim custNo As Integer

    NewFirstName = Me.[First Name].Value
    NewSurname = Me.Surname.Value
    stLinkCriteria = "[First name] = " & "'" & NewCustomer & "' and [Surname] = " & "'" & NewSurname & "'"


    If Me.[First Name] = DLookup("[First name]", "Clients", stLinkCriteria) Then

    MsgBox "This client name, " & NewCustomer & ", has already been entered in database." _
    & vbCr & vbCr & "with that first name " & NewSurname & "" _
    & vbCr & vbCr & "Please check client is not already recorded.", vbInformation, "Possible duplicate information"
    custNo = DLookup("[Client Id]", "Clients", stLinkCriteria)
    Me.DataEntry = False
    DoCmd.FindRecord custNo, , , , , acCurrent

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,860
    Well you are checking firstname with newcustomer? not NewFirstName?

  3. #3
    kelliejean is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    21
    thanks Welshgasman....I guess that's what happens when you copy a formula found online but don't know what you are doing! lol
    I think you are saying that I need to change the stLinkCriteria "& NewCustomer &" to NewFirstName. I'll give it a try

  4. #4
    kelliejean is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    21
    that sorted it welshgasman! I knew it would have been something dumb like that. Thanks so much for taking the time to help out!

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,860
    Quote Originally Posted by kelliejean View Post
    thanks Welshgasman....I guess that's what happens when you copy a formula found online but don't know what you are doing! lol
    I think you are saying that I need to change the stLinkCriteria "& NewCustomer &" to NewFirstName. I'll give it a try
    That is how I started (and still do when I want to do something) Google is my first point of call, everytime.
    However you need to take the time to understand what that code is doing, else it becomes a hindrance not a help.

    I also tend to walk through the code in the Debug window line by line with F8 and inspect the variables. That usually helps in the understanding.

    Good luck with the project.

  6. #6
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Hi Kelliejean,
    My main DB is very dependant on not allowing duplicate people to be enterred so I tend to go to great lengths to avoid it.
    In your procedure are you ensuring that the data is not written to the table if there is a possible match? If not then you may wind up with numerous orphan entries which may then make it difficult to decide which one is the correct one when they go to check them. There is also the possibility of mis-spellings, Jon vs. John or Epstein vs. Epstien.

    In the link you posted you'll note that they use a secondary identifier, address, to distinquish a duplicate entry.

    I know you're just dipping you toes into the vba pool but thought I'd point that out.

    There are many methods to overcome some of those obstacles. As far as mis-spelling there are procedures such as Soundex and Levenschtein Distance. (Google those terms if your interested)
    I'm attaching an example which is part of the method I use.
    It uses a lot of code and unbound forms but it may give you some ideas. In summary it takes a name and checks it against the table of names. It checks for an exact match, a soundex match, and a levenschtein distance of 4. If no matches are found it will open a form to enter a new person. If possible matches are found it will open a form which lists those matching names along with a secondary identifier ( I used date of birth in the example.) You can then either choose a person from the list or opt to enter a new person if none of the matches are the person you want.

    In the example type in a name and search. Then type it in again to see the matches. There are over a 1000 names in the table so your likely to get some matches. You can also search by first or last name only.
    Attached Files Attached Files
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

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

Similar Threads

  1. Prevent duplicate value
    By IvanGAa in forum Forms
    Replies: 1
    Last Post: 03-11-2020, 01:18 AM
  2. Replies: 4
    Last Post: 12-21-2015, 10:57 PM
  3. Prevent Duplicate Entries
    By Abhijeet in forum Forms
    Replies: 2
    Last Post: 08-23-2015, 12:15 AM
  4. Replies: 5
    Last Post: 03-09-2014, 07:16 PM
  5. Prevent Duplicate Entry
    By kilosierra71 in forum Forms
    Replies: 4
    Last Post: 07-28-2011, 03:08 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