Check for Duplicate entries

[stavro]

rivate Function NameOK() As Boolean
Dim NameCount As Integer
NameOK = True

If Surname & "" = "" Or Forename & "" = "" Then Exit Function
If Surname = Surname.OldValue And Forename = Forename.OldValue Then Exit Function

NameCount = DCount("Surname", "mytab", "Surname = """ & Surname & """ AND Forename = """ & Forename & """")

If NameCount = 1 Then
MsgBox "A PLAYER with this NAME & DOB already exists." & vbLf & vbLf & _
"PLEASE CHECK.", vbCritical, "PLAYERS Name"
NameOK = False

End If

End Function

Private Sub Forename_Exit(Cancel As Integer)

If Not NameOK Then
Cancel = True
End If
End Sub
I could someone check my poor vb and see where I have gone wrong. My database is for registered football players and with over 5000 players i need to check that players have not signed for two clubs hope you can help I will be using surname and DOB in the final version

 

[randy700]

What is your code doing / not doing?


Randy

 

[stavro]

doing nothing at presant have checked through but does not work

 

[randy700]

First, try adding an ELSE in your code to see what you're getting.

If NameCount = 1 Then
    MsgBox "A PLAYER with this NAME & DOB already exists." & vbLf & vbLf & _
        "PLEASE CHECK.", vbCritical, "PLAYERS Name"
    NameOK = False
Else
    MsgBox NameCount, vbOKOnly, "Name Count"
End If

Randy

 

[stavro]

orry nothing happening when entering data into test database still no msgbox appears

 

[randy700]

If no message box appears you must have 1 of 2 problems.

1. You are exiting the function early, based on the criteria set in your first two IF statements.

2. Your NameOK function is not in the same module. Try making it a Public function.


Randy

 

[ProgramError]

Why not use the wizard to create a duplication query?


Ian Mayor (UK)
Program Error
9 times out of 10 I know what I'm talking about. This must be my tenth reply.