How to prevent Duplicates when Null is involved (1 Viewer)

DBApprentice · Dec 24, 2018

Hello,

I have a form to add people to a table called "Observers", it cosists of ObserverID, FirstName, MiddleName and LastName only. I built query to underly a form to add new persons to the table.

My problem is: how can I prevent the operator to add duplicated names to it by deploying a message box?

I came up with a solution found in one of the forums:

Code:

Private Sub txtLastName_BeforeUpdate(Cancel As Integer)
 
If DCount("*", "qryObservers", "[FirstName] = '" & Me!txtFirstName & " ' And [MiddleName]= '" & Me!txtMiddleName & "' And [LastName]= '" & Me!txtLastName & "'") > 0 Then

Beep

MsgBox "This name already exists in the database." & vbCrLf & vbCrLf & "Please check that you are not entering a duplicate person before continuing.", vbOKOnly, "Duplicate Value"

Cancel = True

End If
End Sub

But I have noticed that if I leave txtMiddleName null the code does not fire off the message box and I can add as many dupes as I can. Since the operators can include Observers with only First and Last Names we run the risk of getting several dupes on the database.

Later I learnt that DCount does not work with Nulls, so I am on a dead end.

Can anybody help me to fix this code?

Thanks,
Diogo Cuba

arnelgp · Dec 24, 2018

use:

Code:

If DCount("*", "qryObservers", "[FirstName] & '-' & [MiddleName] & '-' & [LastName] = '" & Me!txtFirstName & "-" & Me!txtMiddleName & "-" & Me!txtLastName & "'") > 0 Then

June7 · Dec 24, 2018

Are you saying there is no middle name stored in the field as well as no middle name in the criteria? If person does not have middle name, can require the input of a substitute such as NMI. Or use Nz() to substitute a value when Null is encountered.

If DCount("*", "qryObservers", "[FirstName] = '" & Me!txtFirstName & "' And Nz([MiddleName],'NMI')= '" & Nz(Me!txtMiddleName,"NMI") & "' And [LastName]= '" & Me!txtLastName & "'") > 0 Then

But what if you do get more than 1 person with same first and last names? Names are poor unique identifiers. Might need more info to verify identity, such as birthdate.

Assume you have no concern about individuals with no last name?

moke123 · Dec 24, 2018

Preventing duplicate names can be a difficult task when you consider that many people can have the same first and last names. 2 of the 3 people in my office with the same last name have the same first name.
There is also the chance of user error that names can be mispelled or have numerous spellings (John,Jon or Cindy,Cyndee)

In my case I usually have some secondary data to compare such as date of birth or an address, city or state. I run my names through a procedure that compares first and last names for exact matches, then for a levenschtein distance, the for a soundex match. A levenschtein distance compares names for slight differences in spelling and a soundex compares for names that sound alike. Google the terms for a better explanation. If matches are found I open a form with a list of the matches which contains some of the secondary data like D.O.B. and the user can either select a match if found, or continue on to enter the new person. If no matches are found it just continues on entering the new person.

isladogs · Dec 24, 2018

Agree with using Nz as stated both here and at https://www.utteraccess.com/forum/index.php?showtopic=2051980&hl= where you cross posted.
Please read this link about the etiquette of cross posting https://www.excelguru.ca/content.php?184-A-message-to-forum-cross-posters

moke123 · Dec 24, 2018

you may also want to reconsider your event. What happens if they enter the last name first? the before update event will fire with no first or middle name.

DBApprentice · Dec 24, 2018

Arnelgp it worked!

DBApprentice · Dec 24, 2018

June7 said:
Are you saying there is no middle name stored in the field as well as no middle name in the criteria? If person does not have middle name, can require the input of a substitute such as NMI. Or use Nz() to substitute a value when Null is encountered.

If DCount("*", "qryObservers", "[FirstName] = '" & Me!txtFirstName & "' And Nz([MiddleName],'NMI')= '" & Nz(Me!txtMiddleName,"NMI") & "' And [LastName]= '" & Me!txtLastName & "'") > 0 Then

But what if you do get more than 1 person with same first and last names? Names are poor unique identifiers. Might need more info to verify identity, such as birthdate.

Assume you have no concern about individuals with no last name?

The code you provided work good!

The OBSERVERS table is oversimplified because we don´t get that much information from users, they have to submit their names on a safety card and in many cases they do not even supply a name and we have to use UNKNOWN. The real important information is the safety observation he made.

But I agree with you and I will come up with another identifier to test their names.

DBApprentice · Dec 24, 2018

moke123 said:
Preventing duplicate names can be a difficult task when you consider that many people can have the same first and last names. 2 of the 3 people in my office with the same last name have the same first name.
There is also the chance of user error that names can be mispelled or have numerous spellings (John,Jon or Cindy,Cyndee)

In my case I usually have some secondary data to compare such as date of birth or an address, city or state. I run my names through a procedure that compares first and last names for exact matches, then for a levenschtein distance, the for a soundex match. A levenschtein distance compares names for slight differences in spelling and a soundex compares for names that sound alike. Google the terms for a better explanation. If matches are found I open a form with a list of the matches which contains some of the secondary data like D.O.B. and the user can either select a match if found, or continue on to enter the new person. If no matches are found it just continues on entering the new person.

It would be impossible to use DOB on this case because the Observer are supposed to provide his name only, sometimes he can decides to be annonymous, where I have to use UNKNOWN.

Considering these observers are confined to a ship, chances of having a lot of people with the same name would be unlikely. Also considering that brazilian names are long names, e.g. my own full name is DIOGO CUBA GUEDES DE MORAES, I tend to believe having less controls could not hurt.

The main goal actually is to avoid the application operator to insert duplicated names as I could find on the Excel version of this application I am building.

Thanks for the help!

DBApprentice · Dec 24, 2018

moke123 said:
you may also want to reconsider your event. What happens if they enter the last name first? the before update event will fire with no first or middle name.

I agree with you moke123! Just when i was testing I realized that event trigger is not working good.

I believe I can move it to the btnSave_Click() to be be more suitable.

Thanks for the input!

moke123 · Dec 24, 2018

Bear in mind that Arnels solution, as written, will not work with names like O'Neal or O'Rourke. You would need to double up on the quotation marks.

you could also use the law of propagating nulls.
LOPN in this instance means anything + a null = null (note plus sign + as opposed to ampersand &)

something like this. you may have to adjust delimiters.

Code:

If DCount("*", "qryObservers", """ & ([FirstName]  + ' ') & [MiddleName] + ' ')  & [LastName] & """ = """ & (Me!txtFirstName + ' ') & (Me!txtMiddleName + ' ') & (Me!txtLastName ) & """) > 0 Then

DBApprentice · Dec 24, 2018

Hi, Isladogs.

Thanks for the tip! I read the Golden Rules and will abide them!

Just a heads-up: the link you posted took me to a webpage that did not had the post, from there I had to clink another link to land on the correct message. Since I don´t have authority to post link I can´t re-post it here.

DBApprentice · Dec 24, 2018

moke123 said:
Bear in mind that Arnels solution, as written, will not work with names like O'Neal or O'Rourke. You would need to double up on the quotation marks.

you could also use the law of propagating nulls.
LOPN in this instance means anything + a null = null (note plus sign + as opposed to ampersand &)

something like this. you may have to adjust delimiters.

Code:

If DCount("*", "qryObservers", """ & ([FirstName] + ' ') & [MiddleName] + ' ') & [LastName] & """ = """ & (Me!txtFirstName + ' ') & (Me!txtMiddleName + ' ') & (Me!txtLastName ) & """) > 0 Then

This might sound silly, but what you mean by adjusting the delimiters?

isladogs · Dec 24, 2018

Thanks for mentioning the link error. It should have been: https://www.excelguru.ca/content.php?184-A-message-to-forum-cross-posters.
I've corrected the earlier post as well

The_Doc_Man · Dec 24, 2018

what you mean by adjusting the delimiters?

Sometimes when you use the ' (apostrophe) as a delimiter in a string but the string can ALSO contain an apostrophe (such as O'Rourke or O'Shaughnessy), it will interact with the quotes you supplied internally to your quoted string. To prevent that, you need to take the "doubling" rule into account.

Inside a quoted string, a quote is its own "escape" character. So ...

"" = an empty string
""" = an illegal string by itself
"""" = a string containing ONE double-quote. The outer two " characters are string delimiters and the inner two "" characters together are a " mark that is "escaped by" another " mark.
"" "" = TWO empty strings with no operator between the two.
""" """ = a string containing " " (quoted space).

The same rules apply for apostrophes when you use them inside a double-quoted string. A single ' inside a double-quoted string is OK, but two together looks like you are "escaping" the second ' mark.

Search this forum for "quoted strings" to see lots of articles on the subject. It is quite common to see detailed write-ups on complex quoting issues.

June7 · Dec 24, 2018

Handle possibility of apostrophe in name with Replace() function.

Code:

Replace([LastName], "'","''")

This is called 'escaping' special character. Doubling a special character forces it to be treated as normal text, not a special character - in this case, delimiter.

You will encounter this issue with any code that utilizes SQL syntax and the WHERE CONDITION argument of domain aggregate functions follows syntax of WHERE clause of an SQL statement.

DBApprentice · Dec 28, 2018

Hi Guys!

A couple of days ago I had developed a theory about catching the dupe and asked GregoryParkGeorge if that was doable. He told to test it and I did.

The theory was:

What if I created a new field on my query but now concatenating the three fields, "FullName:FirstName&MiddleName&LastName" and then build a comparison on the btnSave_Click event to check if the match to the information input on the form.

So if a guy named FirstName: Peter MiddleName: Null and LastName: O´Toole would be FullName: PeterO´Toole and I could compare the form to the query

I spent a couple of hours working on my theory about the concatenated field on a query and use that concatenation to check if the name is dupe or not. Well, I have actually spent 1h and half just figuring out the apostrophes and inverted commas on the Dlookup function!

So here is what I have done:

On a form I have put all controls I wanted and added an extra unbound textbox with the countrolsource as "=Firstname&MiddleName&LastName", I would use this control as a variable to my DLookup criteria later. On my query I have created a FullName field concatenating Firstname&MiddleName&LastName.

Finally I have build this code on the cmdSave click event and indeed my theory was true, concatennating values of fullname on the underlying query and use it as a string on DCount or DLookUp criteria works.

In fact it is essentially the same as the other code provided by you but it requires less coding for checking FirstName, MiddleNames, LastNames and Suffix.

Code:

Private Sub cmdSave_Click()
'CODE BUILT BY DIOGO CUBA ON 28DEC2018
'This code was built using several help requests from:
'https://access-programmers.co.UK/forums/showthread.php?p=1604347&posted=1#post1604347
'https://www.UtterAccess.com/forum/index.php?showtopic=2051980&st=0&p=2706058&#entry2706058
'Special Thanks to Isladogs and Robert Crouser that helped me to understand the DCount, DLookUp and Count ("*")
'-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'PURPOSE:
'CHECK VALIDATION RULES ON THE FORM BEFORE SAVING THE USERNAME ON THE DATABASE. THE SELECT CASE EVALUATE THE INFORMATION SUPPLIED BY THE USER:
'>CHECK IF ALL FIELDS WERE SUPPLIED.
'>PREVENT DUPE USERNAME
'>PREVENT DUPE PEOPLE
'>CHECK THE LENGHT OF USERNAME AND PASSWORD.
'-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'ERROR HANDLER
'On Error GoTo ERRORHANDLER:
Select Case True
        Case IsNull(Me.txtFirstName) = True Or IsNull(Me.txtMiddleName) = True Or IsNull(Me.txtLastName) = True Or IsNull(Me.cboAccessLevel) = True
                         Beep
                         Msgbox "You did not includ all information necessary to create the username." & vbCrLf & vbCrLf & "Please review all the fields and try to save again.", vbOKOnly + vbExclamation, "OPERATION FAILED"
                         Me.txtFirstName.SetFocus
                         Exit Sub
        Case Else
                'VARIABLES
                Dim strFormFullName As String, strQryFullName As String, strFormUserName As String
                    strFormFullName = Nz(Me.txtFullName.Value, "")
                    strFormUserName = Nz(Me.txtUserName.Value, "")
                            'START ASSESSING THE FORM CONTROLS
                            Select Case True
                                    'CASE 2 - CHECK IF PASSWORD IS NULL
                                    Case IsNull(Me.txtPassword) = True
                                             Msgbox "Password must not be empty" & vbCrLf & vbCrLf & "Please review the password and try to save again.", vbOKOnly + vbExclamation, "OPERATION FAILED"
                                             Me.txtPassword.SetFocus
                                             Exit Sub
                                    'CASE 3 - CHECK IF PASSWORD IS SMALLER THAN 6 CHARS
                                     Case Len(Me.txtPassword) < 6
                                             Msgbox "Your password is too short!" & vbCrLf & vbCrLf & "Password must have at least 6 characters." & vbCrLf & vbCrLf & _
                                                           "Please review the password and try to save again.", vbOKOnly + vbExclamation, "OPERATION FAILED"
                                             Me.txtPassword.SetFocus
                                             Exit Sub
                                    'CASE 4 - CHECK IF THE PASSWORD IS 8 CHARS OR MORE
                                    Case Len(Me.txtPassword) > 8
                                             Msgbox "Your password is too long!" & vbCrLf & vbCrLf & "Password must have 8 characters max." & vbCrLf & vbCrLf & _
                                                           "Please review the password and try to save again.", vbOKOnly + vbExclamation, "OPERATION FAILED"
                                             Me.txtPassword.SetFocus
                                             Exit Sub
                                    'CASE 5 - CHECK IF THE USERNAME IS NOT EMPTY OR IS NOT SMALLER THAN 5 CHARS
                                    Case IsNull(Me.txtUserName) = True Or Len(Me.txtUserName) < 5
                                             Msgbox "The username is empty or it is too short!" & vbCrLf & vbCrLf & "Usernames must have have at least 5 characters." & vbCrLf & vbCrLf & _
                                                           "Please review the username and try to save again.", vbOKOnly + vbExclamation, "OPERATION FAILED"
                                             Me.txtUserName.SetFocus
                                             Exit Sub
                                    'CASE 6 - CHECK IF THE PERSON IS ALREADY REGISTERED ON THE DATABASE
                                                    'THANKS TO ISLADOGS FOR THE CODE FIXING:
                                                    'THE DCOUNT WILL SEARCH ALL QRY AND CHECK IF THE FULLNAME IS ALREADY IN USE, IF IT IS BIGGER THAN 1 IT MEANS THERE IS ALREADY A RECORD ON THE DATABASE.
                                                    'THE QUOTATION ARE DOUBLED UP TO PICKUP NAMES WITH SPECIAL CHARS.
                                    Case DCount("FullName", "qryCreateUsers", "FullName= """ & strFormFullName & """") > 0
                                             Msgbox "This person already registered!" & vbCrLf & vbCrLf & "You cannot register the same person twice", vbOKOnly + vbExclamation, "OPERATION FAILED"
                                             Me.txtFirstName.SetFocus
                                             Exit Sub
                                    'CASE 7 - CHECK IF THE USERNAME IS ALREADY SAVED ON THE DATABASE.
                                    Case DCount("Username", "qryCreateUsers", "Username= """ & strFormUserName & """") > 0
                                             Msgbox "This Username is already registered!" & vbCrLf & vbCrLf & "Please choose another Username  and try to save again.", vbOKOnly + vbExclamation, "OPERATION FAILED"
                                             Me.txtUserName.SetFocus
                                             Exit Sub
                                    'IF ALL OTHER CASES ARE OK THEN THE RECORD CAN BE SAVED.
                                    Case Else
                                            'TRIM AND UPPER CASE ALL FIELDS EXCEPT PASSWORD.
                                            Me.txtFirstName = Trim(UCase(Me.txtFirstName))
                                            Me.txtMiddleName = Trim(UCase(Me.txtMiddleName))
                                            Me.txtLastName = Trim(UCase(Me.txtLastName))
                                            Me.txtUserName = Trim(UCase(Me.txtUserName))
                                            'TRIM PASSWORD
                                            Me.txtPassword = Trim(Me.txtPassword)
                                            Me.txtCreatedOn.Value = Now ' TIMESTAMP
                                            Me.txtCreatedBy.Value = TempVars("Username").Value 'NAME OF THE USERNAME THAT HAS CREATED THIS USERNAME.
                                            blnGood = True ' RESET BOOLEAN TO AUTOSAVE ON THE BOUND FORM.
                                            Call DoCmd.RunCommand(acCmdSaveRecord) ' SAVE THE RECORD
                                            DoCmd.GoToRecord , , acNewRec ' DISPLAY THE EMPTY RECORD FOR NEW INSERTION.
                                            blnGood = False ' RESET BOOLEAN TO PREVENT THE AUTOSAVE ON THE BOUND FORM.
                                            Msgbox "" & UCase(strFormUserName) & " was sucessfully created and is ready to use the application" & _
                            vbCrLf & vbCrLf & "Please remind him to change his password regularly", vbOKOnly + vbInformation, "SUCCESS"
                                            Exit Sub
                            End Select
End Select
ERRORHANDLER:
        Msgbox "An error occurred, please contact the administrator and inform the details below:" & vbCrLf & vbCrLf & _
                        "Error number: " & Err.Number & vbCrLf & _
                        "Description: " & Err.Description & vbCrLf & vbCrLf & _
                        "Form: Add New User" & vbCrLf & _
                        "Originated on: Private Sub cmdSave_Click()", vbCritical, "Form New User Runtime Error"
                        Exit Sub
End Sub

Thanks!
Diogo Cuba

DBApprentice · Dec 28, 2018

June7 said:
Handle possibility of apostrophe in name with Replace() function.

Code:

Replace([LastName], "'","''")

This is called 'escaping' special character. Doubling a special character forces it to be treated as normal text, not a special character - in this case, delimiter.

You will encounter this issue with any code that utilizes SQL syntax and the WHERE CONDITION argument of domain aggregate functions follows syntax of WHERE clause of an SQL statement.

June7, how can add this to my code? I tried a couple of time but it didn´t work... :banghead:

Code:

strQryFullName = Nz(DLookup("[FullName]", "qryCreateUsers", "FullName= '"" & strFormFullName & ""'"))

isladogs · Dec 28, 2018

You should normally add a false part when using Nz

Your quotes are incorrect. Use

Code:

strQryFullName = Nz(DLookup("FullName", "qryCreateUsers", "FullName= '" & strFormFullName & "'"),"")

Or scrap single quotes completely and replace with double double quotes

Code:

strQryFullName = Nz(DLookup("FullName", "qryCreateUsers", "FullName= """ & strFormFullName & """"),"")

For more details on the latter method, see: http://allenbrowne.com/casu-17.html

EDIT:
I've also tried similar speed tests as David Marten comparing DLookup, DCount & Count(*).
Whilst unindexed fields are ALWAYS going to be slower, DCount is often as fast or faster than DLookup
Also Count("*") is often faster than either of those

In a real world situation using an indexed field, I doubt the difference in the 3 methods will be that significant

DBApprentice · Dec 28, 2018

Thanks for the help!

Please note that when I tested this first code I got error:
3075 - Syntax error (missing operator) in query expression 'FullName='JohnPeterO'Toole".

The second one works great!

If DCount is faster would it be able to spot the same thing as Dlookup on the string comparison?

How to prevent Duplicates when Null is involved (1 Viewer)

On Error GoTo AWF *****:

..forever waiting... waiting for jellybean!

AWF VIP

AWF VIP

MVP / VIP

AWF VIP

On Error GoTo AWF *****:

On Error GoTo AWF *****:

On Error GoTo AWF *****:

On Error GoTo AWF *****:

AWF VIP

On Error GoTo AWF *****:

On Error GoTo AWF *****:

MVP / VIP

Immoderate Moderator

AWF VIP

On Error GoTo AWF *****:

On Error GoTo AWF *****:

MVP / VIP

On Error GoTo AWF *****:

Similar threads

Users who are viewing this thread