Duplicate Check Issue (1 Viewer)

[dgreen]

The below code is telling me at I have a duplicate record whenever I am editing an existing record.

I want to catch the duplicate entry before the user goes into the next field on the record.
If the record already exists and I'm thinking about making a change to the field, maybe adding a space, but then I reconsider and undo the change, is there a way to better handle it?

Private Sub Organization_BeforeUpdate(Cancel As Integer)
'https://access-programmers.co.uk/forums/showthread.php?t=82326

     If DCount("*", "[t_Organization]", "[Organization]= " & Chr(34) & Me![Organization] & Chr(34)) > 0 Then
     MsgBox "Duplicate organization.  Filter for the organization and confirm you need a separate entry.", vbInformation, "Message"
    End If
End Sub

 

[theDBguy]

Hi. Preventing duplicate records is usually done for new records only. Are you saying you don't want the user to accidentally create a duplicate record when editing an existing one?

 

[dgreen]

Yes, I'm also trying to catch possible duplicate creations for already established records, in addition to new records.

 

[theDBguy]

Yes, I'm also trying to catch possible duplicate creations for already established records, in addition to new records.

Well, if a duplicate record means having similar Organization and you want to notify the user immediately, then it looks like you're using the appropriate event. If so, what exactly is the issue?

 

[dgreen]

Maybe it's just my thought process. The value in the cell is "Smith", let's say I think it should be changed and start to do something in the cell "Smith-Western" but before I leave the cell, I decide to back out the changes back to "Smith". Since I didn't change the actual values from the original why would it tell me I have a duplicate.

I guess, I'd need to capture the value when I first entered the cell and compare it to the value before updating to see if there was a change before running the duplicate vba code?

 

[Dreamweaver]

It may be you are editing a dirty record make sure it's saved first then make you changes, I it networked with multple uers?

 

[theDBguy]

Maybe it's just my thought process. The value in the cell is "Smith", let's say I think it should be changed and start to do something in the cell "Smith-Western" but before I leave the cell, I decide to back out the changes back to "Smith". Since I didn't change the actual values from the original why would it tell me I have a duplicate.

I guess, I'd need to capture the value when I first entered the cell and compare it to the value before updating to see if there was a change before running the duplicate vba code?

Well, let's see if I can explain that. Your code is checking for an existence of a value in your table. If the form is on a new record and you enter Smith, it will tell you if Smith already exists in the table as soon as you leave the Textbox. Now, if the form is in an existing record, then obviously Smith already exists in the table, and since you made a change to the name , even if you backspaced it to the original value, then the code fires, and it will tell you Smith already exists in the table, because it does - you're updating it right now. In this scenario, if you changed your mind and don't really want to change the name, then just hit the Esc key instead of the backspace.

 

[dgreen]

Makes sense. Just a little frustrating if I forget to click the right button, get the message and then spend time searching for a duplicate that doesn't actually exist.

 

[HiTechCoach]

If you are editing an existing record, then your check for duplicates must filter out the record you are editing.

Your current code:

DCount("*", "[t_Organization]", "[Organization]= " & Chr(34) & Me![Organization] & Chr(34)) > 0 Then

is including the record, you are editing. So yes, there is an existing record using this logic which results in a false positive for a duplicate.

You need to use something like this:

DCount("*", "[t_Organization]", "[Primary_Key_field] <> " & Me.Primary_Key_Control_Name & " AND [Organization]= " & Chr(34) & Me![Organization] & Chr(34) ) > 0 Then

Exclude the primary key for the record you are editing to see if there are other records that match.

 

[Pat Hartman]

Your validation has to distinguish between new records and existing records and determine if the actual value of the current record is being changed to duplicate something existing.

Private Sub Organization_BeforeUpdate(Cancel As Integer)
Dim DupCount as int
    DupCount = DCount("*", "[t_Organization]", "[Organization]= " & Chr(34) & Me![Organization] & Chr(34))
    If DupCount > 0 Then
        if Me.NewRecord Then
            If me.Organization = Me.Organization.OldValue Then
            Else
                 MsgBox "Duplicate organization.  Filter for the organization and confirm you need a separate entry.", vbInformation, "Message"
                Cancel = True
                Me.Organization.SetFocus
            End If
        Else
            MsgBox "Duplicate organization.  Filter for the organization and confirm you need a separate entry.", vbInformation, "Message"
            Cancel = True
            Me.Organization.SetFocus
        End If
    End If
End Sub

 

[dgreen]

@Pat Hartman
You gave me some good thoughts... My modified code is below but I'm running into other situations...

1) Spell check appears to be thwarting the code. I have the spell check code on the LostFocus and this Duplicate check on the BeforeUpdate.
2) If there is a duplicate of an existing record, I want to put the cursor back into the search box, so the user can filter for the value. The .setfocus is giving me a run-time error"2108", you must save the field before you execute the setfocus method. Thoughts on either where else to put the code or how to modify it?

Private Sub Organization_BeforeUpdate(Cancel As Integer)
'https://access-programmers.co.uk/forums/showthread.php?t=82326
'https://www.access-programmers.co.uk/forums/threads/duplicate-check-issue.309996/#post-1675228

Dim ClickResult As VbMsgBoxResultEx
Dim DupCount As Integer
DupCount = DCount("*", "[t_Organization]", "[Organization]= " & Chr(34) & Me![Organization] & Chr(34))
    
    If Me.NewRecord Then
        If DupCount > 1 Then
            ClickResult = Dialog.RichBox("Duplicate organizational name.  Filter for the organization and confirm you need a separate entry. If you do, provide a different / unique abbreviation.", vbOKOnly, "Check before adding", , , 0, False, False, False)
            Me.Undo
            Forms!f_Organizations!textsearch.SetFocus 'Error here.... Need help.
        End If
    ElseIf DupCount > 1 Then
        'Tell the user it's a duplicate name.  But give them time to either rename it or move the subform records to the master record.
        ClickResult = Dialog.RichBox("Duplicate organization.  Either rename it or move subform records to the primary and delete this one.", vbOKOnly, "Check before adding", , , 0, False, False, False)
    End If
End Sub

Your validation has to distinguish between new records and existing records and determine if the actual value of the current record is being changed to duplicate something existing.

Private Sub Organization_BeforeUpdate(Cancel As Integer)
Dim DupCount as int
    DupCount = DCount("*", "[t_Organization]", "[Organization]= " & Chr(34) & Me![Organization] & Chr(34))
    If DupCount > 0 Then
        if Me.NewRecord Then
            If me.Organization = Me.Organization.OldValue Then
            Else
                 MsgBox "Duplicate organization.  Filter for the organization and confirm you need a separate entry.", vbInformation, "Message"
                Cancel = True
                Me.Organization.SetFocus
            End If
        Else
            MsgBox "Duplicate organization.  Filter for the organization and confirm you need a separate entry.", vbInformation, "Message"
            Cancel = True
            Me.Organization.SetFocus
        End If
    End If
End Sub

 

[Pat Hartman]

Move the spell check to the form's BeforeUpdate event.

 

[dgreen]

I assume you were responding to the other thread on my spelling challenge.

When I put the code on the BeforeUpdate I get a run-time error (2108) on the .SetFocus. this one tells me I must save the field before I run the setfocus method. This is what pushed me to put the code on the AfterUpdate.

Move the spell check to the form's BeforeUpdate event.