Prompt if duplication

[esskaykay]

I’m trying to check if data already exists in my table. I have a couple fields that in rare instances could contain duplicate entries. However, when this is about to happen, I want to prompt the user of such. Any suggestions would be appreciated?

Thanks,
SKK

 

[pbaldy]

One way:

If DCount(...) > 0 Then
  MsgBox "There already is one"
End If

http://www.mvps.org/access/general/gen0018.htm

 

[esskaykay]

I'm still a bit confused. Where/how do I place in the value I'm looking for?

I tried:

If DCount("Notice_ID", "tblPermits", Me.txtNotice_ID = Me.txtNotice_ID) > 1 Then

 

[pbaldy]

From the link I posted, the correct syntax would be:

DLookup("FieldName", "TableName", "Criteria = " & forms!FormName!ControlName)

You don't have the quotes and concatenation in the criteria argument.

 

[esskaykay]

So is it coded as DLookup or DCount? I tried both but they bombed.

If DLookup("Notice_ID", "tblPermits", "Me.txtNotice_ID = " & Forms!frmPermits!txtNotice_ID) > 1 Then

or

if DLookup("Notice_ID", "tblPermits", "Me.txtNotice_ID = " & Forms!frmPermits!txtNotice_ID) > 1 Then

 

[pbaldy]

Sorry, you'd want DCount for this, but you just want a field name for the first part of the criteria. Presuming the data type of the field is numeric (and the field name is Notice_ID):

If DCount("*", "tblPermits", "Notice_ID = " & Forms!frmPermits!txtNotice_ID) > 1 Then

 

[esskaykay]

Maybe I'm asking for a bit much here but...

As a test a created a TEST command button with OnClick coded as:

If DCount("*", "tblPermits", "Notice_ID = " & Forms!frmPermits!txtNotice_ID) > 1 Then
MsgBox "EXISTS"
Else
MsgBox "NOPE"
End If

However, I'm receiving a Run-time error 2001 You canceld the previous operation. The field is a text field.

Sorry

 

[pbaldy]

Again using the link, for a text field:

If DCount("*", "tblPermits", "Notice_ID = '" & Forms!frmPermits!txtNotice_ID & "'") > 1 Then

 

[esskaykay]

Ahhh...
The infamous apostrophe mark -- that did it.
Thank you very much Paul. Sorry for being such a pest but this will help tremendously.

Thanks again,
SKK