Prevent Duplicate Records

[cstuckey56]

I see this question is asked a number of time but I tried to use the code that is suggested but it doesn't work. Can someone please give me advise on the proper code to prevent duplicate records in my form?

I'm using:

Private Sub Ctl_Lname_BeforeUpdate(Cancel As Integer)
Dim dupCount As Long
dupCount = DCount("*", "Clients", "[LastName]= '" & Me.[LastName] & "'" & " And " & "[PreferredName] = '" & Me.[PreferredName] & "'")
If dupCount <> 0 Then
Beep
MsgBox "This name already exists in the database. Please check that you are not entering a duplicate person before continuing.", vbOKOnly, "Duplicate Value"
Cancel = True
End If
End Sub

Table = "Clients"
Field1 = "LastNeme"
Field2 = "PreferredName"

Thanks
Chris

 

[missinglinq]

Try this:

dupCount = DCount("*", " Clients ", "[ LastName]= '" & Me.[LastName] & "' And [PreferredName] = '" & Me.[PreferredName] & "'")

Linq ;0)>

 

[cstuckey56]

I tried your code and no such luck. Can you write it out the way it should be?

Thanks

 

[David R]

Can you be clearer about what "no such luck" means? Have you tried running it in the Immediate window to ensure you're actually DCount'ing what you think you're counting?

 

[ButtonMoon]

Add a uniqueness constraint to your table. Data integrity belongs in the database, not just in forms.

ALTER TABLE tbl ADD CONSTRAINT AK1_tbl UNIQUE (col1, col2, col3);

Are you sure you really want to enforce uniqueness of names? Duplicate names are very common.

 

[virusworld]

try to use DlookUp friend :

Dim rslt as String

rslt = DLookup("[lastName]", "Clients", "LastName='" & Me.lastName & "'")
If Me.NewRecord Then
If Not (IsNull(rslt)) Then
MsgBox ("The Client Name  is already registered Plz Choose Another ..")
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
End If
Else
If Not (IsNull(rslt)) Then
MsgBox ("The Client Name  is already registered Plz Choose Another ..")
lastName.value = lastname.oldvalue
end if
end if
end sub

 

[missinglinq]

Using DCount for this kind of thing, as the OP did, is fine, after all, you want to 'count' how many Records with identical names already exist. His problem was simply that he had his single/double quotes misplaced in his two-part 'Where' clause, which is very easy to do!

The line

rslt = DLookup("[lastName]", "Clients", "LastName='" & Me.lastName & "'")

is saying, in essence, 'Look up the LastName where the LastName = the LastName!'

Linq ;0)>

 

[cstuckey56]

Thanks for everyone's input.

To David R: When I say "No Such Luck," I meant when I entered a duplicate name to test, it did not warn me.

 

[virusworld]

what about the dlookup did it helped you ??

 

[cstuckey56]

what about the dlookup did it helped you ??

No, I was getting an error message. :banghead:

 

[virusworld]

Try This Friend
check on the form cus_name afterupdate event