Prevent Duplicate Record

[gator9]

Here is my code and it works great but it queries for duplicate records threw out my whole projects table, and in would like for it to filter its search via the client ID. Any Suggestions?

Private Sub ProjectName_BeforeUpdate(Cancel As Integer)
Dim stLinkCriteria As String
stLinkCriteria = "ProjectName='" & Me!ProjectName & "'"
If (Not IsNull(DLookup("ProjectName", "Projects", _
"ProjectName ='" & Me!ProjectName & "'"))) Then
MsgBox "<<Warning!>> Debtor has already been entered in the database!"
End If
End Sub

Sincerely,

Charles

http://grahamenterprises.net

 

[PHV]

Something like this ?
DLookup("ProjectName", "Projects", _
"ProjectName='" & Me!ProjectName & "' AND ClientID='" & Me!ClientID & "'")
If ClientID is defined as numeric in the Projects table the get rid of the single quotes.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[gator9]

Thanks PHV! Got it! I took out the single quotes and this is the code for others that may need it.

Private Sub ProjectName_BeforeUpdate(Cancel As Integer)
If (Not IsNull(DLookup("ProjectName", "Projects", _
"ProjectName='" & Me!ProjectName & "' AND ClientID=" & Me!ClientID & ""))) Then
MsgBox "<<Warning!>> Debtor has already been entered in the database. Make sure this is not a duplicate account for this client."
End If
End Sub

Sincerely,

Charles

http://grahamenterprises.net