Check for possible duplicate(s) 1

[debbieg]

I have created a database for a senior softball league.

If we sign up a new player, I would know to check to see if they played in the past, but I'm going to be giving this database to someone else and they may not. So, I'm trying to put some checks and balances in it.

When I add a new player, I have it checking for duplicates in this way in Form_BeforeUpdate:

If Me.NewRecord Then
    If Not IsNull(Me.LN) And Not IsNull(Me.BirthDate) Then
        Dim x As Variant
        x = DCount("*", "Members", "[LN] = '" & Me.LN & "' And [Birthdate] = #" & Me.BirthDate & "#")
        If x > 0 Then
... I DON'T KNOW WHAT TO DO HERE!
            Cancel = True
        End If
    End If
End If

I want to display the possible duplicates. Would I need to display it(them) in a form that pops up? If so, if one that is displayed is the correct one, can I select it, cancel the new record, and have my first form display it? If so, how?

Thanks in advance for any help.

Debbie

 

[TheAceMan1]

How are ya debbieg . . .

Try this in the forms [blue]Before Update[/blue] event:

[blue]   Dim Msg As String, Style As Integer, Title As String
   Dim NL As String, DL As String, ID As Long, Cri As String
   
   NL = vbNewLine
   DL = NL & NL
   
   Cri = "[LN] = '" & Me!LN & "' AND " & _
         "[FN] = '" & Me!FN & "' AND " & _
         "[Birthdate] = #" & Me.BirthDate & "#"
   ID = Nz(DLookup("[MemNum]", "Members", Cri), 0)
   
   If ID > 0 Then
      Msg = "'" & Me!LN & ", " & Me!FN & "' Already Exist!" & DL & _
           "The current record will be removed or returned to its origional state." & DL & _
           "Then the database will move to the duplicate record!"
      Style = vbCritical + vbOKOnly
      Title = "Duplicate Member Found! . . ."
      MsgBox Msg, Style, Title
      
      Me.Undo
      Me.Recordset.FindFirst "[MemNum] = " & ID
      [blue][b]Cancel = True[/b][/blue]
   End If[/blue]

[blue]Your Thoughts . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[debbieg]

TheAceMan1,

Thank you for your help.

I had to check for NewRecord because it wouldn't save changes to an edited record. I had to add the extra quotes because some names have apostrophe in them.

If Me.NewRecord Then
    Dim NL As String, DL As String, ID As Long, Cri As String, DQ As String
    NL = vbNewLine
    DL = NL & NL
    Cri = "[LN] = """ & Me.LN & """ AND " & _
          "[Birthdate] = #" & Me.BirthDate & "#"
    ID = Nz(DLookup("[MemNum]", "Members", Cri), 0)
    If ID > 0 Then
        msg1 = Me.FN & " " & Me.LN & " already Exists!"
        msg2 = "The new record will be deleted."
        msg3 = "Then the database will move to the existing record."
        style = vbExclamation
        answer = MsgBox(msg1 & DL & msg2 & NL & msg3, style, conTitle)
        Me.Undo
        Me.Recordset.FindFirst "[MemNum] = " & ID
        Cancel = True
    End If
End If

This is working great with the exception of getting error 3075 after it goes to the existing record. I cannot figure out why.

Thanks,
Debbie

 

[TheAceMan1]

debbieg . . .

[blue]Replace:
   Cri = "[LN] = """ & Me.LN & """ AND " & _
          "[Birthdate] = #" & Me.BirthDate & "#"
 
With:
   Cri = "[LN] = '" & [purple][b]Replace(Me!LN, "'", "''")[/b][/purple] & "' AND " & _
         "[FN] = '" & Me!FN & "' AND " & _
         "[Birthdate] = #" & Me.BirthDate & "#"[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[debbieg]

TheAceMan1,

Slick! That got rid of the error. I'll remember that one!

Thank you so much for your quick responses and your help.

Debbie