How to prevent Duplicates when Null is involved

isladogs said:
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

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

I managed to tweak my code and accomodate the DCount and it is working:

Code: 
Case Nz(DCount("FullName", "qryCreateUsers", "FullName= """ & strFormFullName & """"), "") > 0
                 Msgbox "This person already registered!" & vbCrLf & vbCrLf & "You cannot register the same person twice", vbOKOnly + vbExclamation, "OPERATION FAILED"
Exit Sub

So I guess now I will have better perfomance as the DB grows!

Thanks for the help!:)
 
1. If you have apostrophes the first method won't work unless you first replace single apostrophes with doubles e.g. Peter O''Toole.
The second method is one way of managing that issue.
Another is the use of Chr(39) in place of apostrophes

2. You don't need Nz if you are using DCount. If there is no record, the DCount=0

3. I just did a repeat test on a table of 2.6 million records checking for the existence of a single record in an indexed field and looping through 1000 times

Results:
a) Nz(DLookup...)<>"" Time =7.17 seconds
b) DCount>0 Time= 6.69 seconds
c) SQL Count("*"...) >0 Time = 4.07 seconds
d) SQL SELECT... RecordCount>0 Time = 4.93 seconds

The code used was very similar to that in the tests done by David Marten
The relative times were ALWAYS in the order shown above in my tests
 
Isladogs it seems I need to have a AutoThanks in Replication ID to be enough to give you! :) I am verry happy to join this forum!

isladogs said:
1. If you have apostrophes the first method won't work unless you first replace single apostrophes with doubles e.g. Peter O''Toole.
The second method is one way of managing that issue.
Another is the use of Chr(39) in place of apostrophes
Click to expand...

Thanks for the explanation! I got the second option and it is working fine!

isladogs said:
2. You don't need Nz if you are using DCount. If there is no record, the DCount=0
Click to expand...

I did not noticed that. I have updated my code and removed the Nz function. Thanks!

isladogs said:
3. I just did a repeat test on a table of 2.6 million records checking for the existence of a single record in an indexed field and looping through 1000 times

Results:
a) Nz(DLookup...)<>"" Time =7.17 seconds
b) DCount>0 Time= 6.69 seconds
c) SQL Count("*"...) >0 Time = 4.07 seconds
d) SQL SELECT... RecordCount>0 Time = 4.93 seconds

The code used was very similar to that in the tests done by David Marten
The relative times were ALWAYS in the order shown above in my tests
Click to expand...

So the SQL Count would be the faster... The syntax for this is the same as it is for DCount>0?

Regards,
Diogo Cuba
 
I doubt you will notice the speed difference for any of them unless you have millions of records. Bear in mind that I got different results to those by David Marten despite almost identical code. I was searching for one specific record whereas he had lots of identical record in his sample.

If you want to try Count(*), just use this

Code: 
 Case Count("*", "qryCreateUsers", "FullName= """ & strFormFullName & """") > 0
      Msgbox "This person already registered!" & vbCrLf & vbCrLf & "You cannot register the same person twice", vbOKOnly + vbExclamation, "OPERATION FAILED"
Exit Sub
 
[SOLVED] How to prevent Duplicates when Null is involved

Hello everyone,

I finished my "Add New User" form and I would like to thank you all for your inputs and insights.
uarulez2.gif


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.

Here is the final code:



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 to all once again and sorry for my typos and english!
thanks.gif


Regards,
Diogo Cuba
 
Last edited:
You must log in or register to reply here.

Similar threads

A
DCOUNT - Prevent duplicate
Replies
8
Views
628
ayam
A
M
BEFORE UPDATE PREVENT DUPLICATE
Replies
6
Views
801
arnelgp
arnelgp
B
Update main form text boxes as subform is filled in? Also, subforms not linking?
Replies
4
Views
779
gemma-the-husky
gemma-the-husky
D
Master/Child Field is Null when detail subform has no records
Replies
6
Views
1,167
Pat Hartman
P
S
Access to Outlook Contacts with ContID
2
Replies
24
Views
1,852
silentwolf
S

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom