Dount in forms to avoid Duplicate record (1 Viewer)

[anishkgt]

Hi All,

how can I prevent duplicate records from being added from a form, the dcount in the text field property, trigged before update is where this should be used is what I know but having trouble with the syntax.

I've got the table tblInvnetoryDetail with InvID(AutoNumber), and SerialNumber. The form text field is txtSerialNumber. I've managed this far with the expression

DCount([InvID],"tblinventoryDetail","[txtSerialNumber]=&"'") but this did not work.

How can I get the expression to avoid duplicates.

 

[jdraw]

See this for Dcount info and examples.

If you want to ensure no duplicates, you would check

If DCount("InvID","tblinventoryDetail","SerialNumber= '" &  Me!txtSerialNumber & "'") = 0 Then
'--no duplicate
ELSE
'---record already exists
End If

Also you can research unique composite index to prevent duplicates.

 

[stopher]

You should set SerialNumber in your table as an index with duplicates not allowed. This is the cast iron way to maintain integrity of your data.

But in your expression I think it should be more like (assuming your serial number is a number and not text):

DCount([InvID],"tblinventoryDetail","[SerialNumber]=" & [txtSerialNumber])

If it is text then:

DCount([InvID],"tblinventoryDetail","[SerialNumber]='" & [txtSerialNumber] & "'")

hth
Chris

 

[stopher]

sorry I didnt see jdraw's post which reads better than mine.

 

[anishkgt]

thank stopher, it worked for me.