Pesky Users Prevention: How to AVOID Editing record to produce a duplicate record.

[DBApprentice]

Hey, guys.

I have a question that is not exactly equal to thousands of threads on the forum - which I have already asked before! - on how to prevent duplicates while ADDING new records to a table/query via form.

I have a form that EDITS the employee information, for instance, and if a super-duper-smart-ass-pesky user tries to EDIT a given record to providing the same information of another employee on these fields: FIRSTNAME, LASTNAME, COMPANY, POSITION, I would like to spot that and prevent that editing.



Now, this form is also used to add information, I have a big Select Case already in place to detect when the user is in a new record or is editing. However, running some tests I noticed that if I remove this part:

 Case DCount("ObserverID", "qryObserversInfo", "TestName= """ & Me.txtTestName & """ ") > 0
                        MsgBox "This Observer is already recorded on database." & vbCrLf & vbCrLf & "Please review the information and re-try.", vbCritical + vbOKOnly, "SAVE OPERATION FAILED"

FYI I am using this:

It is possible to EDIT the information so that I can create duplicates.

If I leave the DCOUNT then I can´t EDIT any information because I will get a warning that I am making a duplicate...

Private Sub Form_Current()
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone

I was wondering if I can retrieve any information from the recordset that can tell me the user is EDITING a information and then use that in a select case that can validate it.

Any ideas?

 

[jdraw]

How are you defining "duplicate"?
You could create a unique composite index of two or more fields and Access will not allow duplicates. It will probably give you a 3022 error which you can trap and give your own custom error message.

 

[DBApprentice]

How are you defining "duplicate"?
You could create a unique composite index of two or more fields and Access will not allow duplicates. It will probably give you a 3022 error which you can trap and give your own custom error message.

Thanks jdraw!

To be more clear I have an example, let´s say I have already registered this - I will use code formatting just for visual cue.

ID = 1084, FName = Diogo, MName = Cuba, LName = Moraes, Company = 1, Position = 104.

Then I will go to another person´s record:

ID = 1193, FName = Edward, MName = Null, LName = Hopkins, Company = 1, Position = 94.

And Change it to the following:

FName = Diogo, MName = Cuba, LName = Moraes, Company = 1, Position = 104.

I will end up with this:

ID = 1084, FName = Diogo, MName = Cuba, LName = Moraes, Company = 1, Position = 104. 
ID = 1193, FName = Diogo, MName = Cuba, LName = Moraes, Company = 1, Position = 104.

I am creating a partial DUPLICATE info intentionally and I would like to know if I can prevent that.

The Unique Compsite Key does not allow null values and that will be a problem on this design because I can have NULL in MiddleName or Company and it won´t constraint what I need. In addition If I only use it on FName and LName then I would be prevailing homonyms and I need to accept that fact. I was wondering if I could disable the editing of the names by operators and allow it only to Supervisors...

Please not that I understand I need to live with the fact that there is no way I can prevent 100% of pesky users messing around the data, but I am trying to programatically reach what is possible, then I will leave to the ADMIN to decide what to do to the OPERATOR by providing him reports of the history of these duplicates.

 

[jdraw]

Depending on your specific requirements there are things you could check for and if found, ask the user/operator to confirm or review an entry or edit
-if FName and LName already exist
-if Position already exists

This would be in some data validation/verification routine. Just as you would check to ensure FName and LName were valued; you could also check for duplicate.

You could restrict the verification to others, but you probably want to "correct" and potential duplicates early in the data entry process or at least flag them as "questionable/needs review".

You could have DataEntry =yes form(s) for initial entry of data and then separate process for verification. If you do this, you may want to record who did the data entry in order that if more than normal errors occur you can re-educate/train the person involved.

There is not likely 1 answer fits all--it depends on your environment, the criticality of duplicates, the abilities of the user/operators and management requirements.

Good luck.

 

[DBApprentice]

Depending on your specific requirements there are things you could check for and if found, ask the user/operator to confirm or review an entry or edit
-if FName and LName already exist
-if Position already exists

This would be in some data validation/verification routine. Just as you would check to ensure FName and LName were valued; you could also check for duplicate.

You could restrict the verification to others, but you probably want to "correct" and potential duplicates early in the data entry process or at least flag them as "questionable/needs review".
...

That´s a good idea, I will modify my validation to flag it to the operator and then I will offer an exit to prevent it.

Thanks for the explanation!

 

[DBApprentice]

Depending on your specific requirements there are things you could check for and if found, ask the user/operator to confirm or review an entry or edit
-if FName and LName already exist
-if Position already exists
...

Good luck.

After going through some studies on the events at form and control level I have tested this approach and it is working fine.

The god thing is that I can allow the user to add the DUPE if he decides to do so and it will carry a txtDupe to that form everytime the user navigate trhough it.

This is how I solved:

1st - Created:

Private strOriginalObserver As String

2nd - On the cmdSave event:

...
Case strOriginalObserver <> Me.txtFullName And (DCount("ObserverID", "qryObserversInfo", "TestName = """ & Replace(Me.txtTestName, " ", "") & """ ") > 1 Or _
DCount("ObserverID", "qryObserversInfo", "FullName = """ & Replace(Me.txtFullName, " ", "") & """ ") > 1)
            Dim intUserResp As Integer
                intUserResp = MsgBox("The person you are trying to add to the application seems to be already recorded." & _
                vbCrLf & vbCrLf & "Unless it´s a case of homonym you should avoid saving duplicates to the database to avoid bad data contamination. If you truly believe this is a homonym case then you can save it." & _
                vbCrLf & vbCrLf & "However, it is strongly recommended to you go back and check the potential duplicated person." & _
                vbCrLf & vbCrLf & "Would you like to resume to SAVE the operation?", vbExclamation + vbYesNo + vbDefaultButton2, "POTENTIAL DUPLICATE OBSERVER DETECTED")
                    Select Case intUserResp
                        
                        Case vbNo
                            Me.Application.Echo False
                            DoCmd.Close , , acSaveYes
                            DoCmd.OpenForm "frmObservers", , , , acFormAdd
                                                   
                        Case vbYes
                            'TELL USER OBSERVER IS SAVED.
                            MsgBox "OBSERVER INFORMATION SAVED", vbOKOnly + vbInformation, "SAVE OPERATION SUCESSFUL"
                                GoTo SaveRecord:
                    End Select
        Case Else
SaveRecord:
...

3rd - Added this to the Form_Current event for flagging a potential dupe:

Select Case IsNull(Me.txtLastName) = True And IsNull(Me.txtFirstName) = True
            Case True
                Me.txtDupe.Visible = False
                Exit Sub
            Case False
                Dim intDupe As Integer
                    intDupe = DCount("ObserverID", "qryObserversInfo", "TestName = """ & Replace(Me.txtTestName, " ", "") & """ ") > 1 Or _
DCount("ObserverID", "qryObserversInfo", "FullName = """ & Replace(Me.txtFullName, " ", "") & """ ") > 1
                        If intDupe < 0 Then
                            Me.txtDupe.Visible = True
                            Beep
                        Else
                            Me.txtDupe.Visible = False
                        End If
            End Select

I am very happy with the results so far.

Edit:I forgot to mention I have also created a Field on the query level:

TestName: Replace([FirstName] & [MiddleName] & [LastName] & [CompanyFK] & [PositionFK];" ";"")

This I way the string with the name will chop off the spaces between the names and will spot if a person types in more spaces by accident where:

"DIOGO CUBA "
"DIOGO CUBA"
Will become "DIOGOCUBA" everytime.