Check for Duplicate

[Dave Elliott]

I have a form named BlankChecks which has a control on it that needs to be
checked for duplicates although duplicates are allowed.
The control is named ChkNo
I tried this code, but it does not work, what am I doing wrong?
It is on after update event.
Thanks,
Dave


DoCmd.SetWarnings False
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
If DCount("ChkNo", "BlankChecks", "BlankChecks=Forms!BlankChecks!") > 1 Then
MsgBox "Duplicate Check Number, Are You Sure?"
End If

 

[Cameron Sutherland]

Your DCOUNT is looking for the value of "BlankChecks=Forms!
BlankChecks!". Don't forget that dcount counts values in a
table not in a form. I suspect by your example you are
trying it wrong. Try this instead:

DCount("[ChkNo]", "BlankChecks", "[ChkNo]='"[Forms]!
[BlankChecks]![ChkNo]"'")

This is assuming that
-"[ChkNo]"
is the field you want to count (notice square braces)
-"BlankChecks"
is the name of the table where is resides
-"[ChkNo]='"[Forms]![BlankChecks]![ChkNo]"'"
is your condition. In this case where the field called
ChkNo equales the value in the textbox called ChkNo on the
open form called BlankChecks (You should really give them
different names.

-Cameron Sutherland

 

[Dave Elliott]

The table name is tblchecks
What would be the correct sytax then?

Your DCOUNT is looking for the value of "BlankChecks=Forms!
BlankChecks!". Don't forget that dcount counts values in a
table not in a form. I suspect by your example you are
trying it wrong. Try this instead:

DCount("[ChkNo]", "BlankChecks", "[ChkNo]='"[Forms]!
[BlankChecks]![ChkNo]"'")

This is assuming that
-"[ChkNo]"
is the field you want to count (notice square braces)
-"BlankChecks"
is the name of the table where is resides
-"[ChkNo]='"[Forms]![BlankChecks]![ChkNo]"'"
is your condition. In this case where the field called
ChkNo equales the value in the textbox called ChkNo on the
open form called BlankChecks (You should really give them
different names.

-Cameron Sutherland

-----Original Message-----
I have a form named BlankChecks which has a control on it that needs to be
checked for duplicates although duplicates are allowed.
The control is named ChkNo
I tried this code, but it does not work, what am I doing wrong?
It is on after update event.
Thanks,
Dave


DoCmd.SetWarnings False
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
If DCount("ChkNo", "BlankChecks", "BlankChecks=Forms! BlankChecks!") > 1 Then
MsgBox "Duplicate Check Number, Are You Sure?"
End If


.

 

[fredg]

I have a form named BlankChecks which has a control on it that needs to be
checked for duplicates although duplicates are allowed.
The control is named ChkNo
I tried this code, but it does not work, what am I doing wrong?
It is on after update event.
Thanks,
Dave

DoCmd.SetWarnings False
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
If DCount("ChkNo", "BlankChecks", "BlankChecks=Forms!BlankChecks!") > 1 Then
MsgBox "Duplicate Check Number, Are You Sure?"
End If+

Why are you setting Warnings false here?
There is no warning message generated from this code.

And what is the Table name, since DCount checks the table, not the
form?

If the control name on the form "BlankChecks" and in the table is
[ChkNo]:

If DCount("*","TableNameHere","[ChkNo] = " & Me![ChkNo]) > 1 Then

The above assumes [ChkNo] is a number datatype field. If it is a Text
datatype then use:

"[ChkNo] = '" & Me![ChkNo] & "'")

I think you should be using the BeforeUpdate event, in which case you
could use:

If DCount("*","TableName","[ChkNo] = " & Me![ChkNo]) > 1 Then
If MsgBox ("Duplicate Check Number, Are You Sure?",vbYesNo) = vbNo
Then
Cancel = True
End If

The above will return the focus to the control without saving the
record if No is clicked.

 

[John Vinson]

The table name is tblchecks
What would be the correct sytax then?

The first argument to DCount (or any domain function) is the name of
the field you want to count.

The second argument is the name of the Table (or Query) containing the
field.

The third argument is a valid SQL WHERE clause without the word WHERE
which limits the records to those you desire to count.

So,

=DCount("[ChkNo]", "tblChecks", "[ChkNo]='" &
[Forms]![BlankChecks]![ChkNo] & "'")

You need the & concatenation operator to generate a third argument
like

[ChkNo]='3125'

if that's what's in the form control.

 

[Dave Elliott]

NEW CODE: This does not give me the msgbox warning, why?

Private Sub ChkNo_BeforeUpdate(Cancel As Integer)
If DCount("*", "Tblchecks", "[ChkNo] = " & Me![ChkNo]) > 1 Then
MsgBox "Duplicate Check Number, Are You Sure?", vbOKOnly + vbInformation
Exit Sub
End If
End Sub

OLD STUFF BELOW

I have a form named BlankChecks which has a control on it that needs to be
checked for duplicates although duplicates are allowed.
The control is named ChkNo
I tried this code, but it does not work, what am I doing wrong?
It is on after update event.
Thanks,
Dave

DoCmd.SetWarnings False
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
If DCount("ChkNo", "BlankChecks", "BlankChecks=Forms!BlankChecks!") > 1 Then
MsgBox "Duplicate Check Number, Are You Sure?"
End If+

Why are you setting Warnings false here?
There is no warning message generated from this code.

And what is the Table name, since DCount checks the table, not the
form?

If the control name on the form "BlankChecks" and in the table is
[ChkNo]:

If DCount("*","TableNameHere","[ChkNo] = " & Me![ChkNo]) > 1 Then

The above assumes [ChkNo] is a number datatype field. If it is a Text
datatype then use:

"[ChkNo] = '" & Me![ChkNo] & "'")

I think you should be using the BeforeUpdate event, in which case you
could use:

If DCount("*","TableName","[ChkNo] = " & Me![ChkNo]) > 1 Then
If MsgBox ("Duplicate Check Number, Are You Sure?",vbYesNo) = vbNo
Then
Cancel = True
End If

The above will return the focus to the control without saving the
record if No is clicked.

 

[fredg]

NEW CODE: This does not give me the msgbox warning, why?

Private Sub ChkNo_BeforeUpdate(Cancel As Integer)
If DCount("*", "Tblchecks", "[ChkNo] = " & Me![ChkNo]) > 1 Then
MsgBox "Duplicate Check Number, Are You Sure?", vbOKOnly + vbInformation
Exit Sub
End If
End Sub

OLD STUFF BELOW

I have a form named BlankChecks which has a control on it that needs to be
checked for duplicates although duplicates are allowed.
The control is named ChkNo
I tried this code, but it does not work, what am I doing wrong?
It is on after update event.
Thanks,
Dave

DoCmd.SetWarnings False
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
If DCount("ChkNo", "BlankChecks", "BlankChecks=Forms!BlankChecks!") > 1 Then
MsgBox "Duplicate Check Number, Are You Sure?"
End If+

Why are you setting Warnings false here?
There is no warning message generated from this code.

And what is the Table name, since DCount checks the table, not the
form?

If the control name on the form "BlankChecks" and in the table is
[ChkNo]:

If DCount("*","TableNameHere","[ChkNo] = " & Me![ChkNo]) > 1 Then

The above assumes [ChkNo] is a number datatype field. If it is a Text
datatype then use:

"[ChkNo] = '" & Me![ChkNo] & "'")

I think you should be using the BeforeUpdate event, in which case you
could use:

If DCount("*","TableName","[ChkNo] = " & Me![ChkNo]) > 1 Then
If MsgBox ("Duplicate Check Number, Are You Sure?",vbYesNo) = vbNo
Then
Cancel = True
End If

The above will return the focus to the control without saving the
record if No is clicked.

Is the table field [ChkNo] a Number datatype?
If so you should get a message if there are at least 2 records (>1).
I think you want just one record (>0). So ....
If DeCount("*",etc...) >0 Then
should give you the message box if only one record is found with that
number.

Remember, while the ChkNo may be a number, it's the field's datatype
which is of concern here. If it is Text datatype use the second syntax
I gave in my previous message:
"[ChkNo] = '" & Me![ChkNo] & "'")

 

[Dave Elliott]

THANKS,
DAVE

NEW CODE: This does not give me the msgbox warning, why?

Private Sub ChkNo_BeforeUpdate(Cancel As Integer)
If DCount("*", "Tblchecks", "[ChkNo] = " & Me![ChkNo]) > 1 Then
MsgBox "Duplicate Check Number, Are You Sure?", vbOKOnly + vbInformation
Exit Sub
End If
End Sub

OLD STUFF BELOW

On Wed, 07 Jul 2004 18:29:26 GMT, Dave Elliott wrote:

I have a form named BlankChecks which has a control on it that needs

to
be

checked for duplicates although duplicates are allowed.
The control is named ChkNo
I tried this code, but it does not work, what am I doing wrong?
It is on after update event.
Thanks,
Dave

DoCmd.SetWarnings False
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
If DCount("ChkNo", "BlankChecks", "BlankChecks=Forms!BlankChecks!") >

1
Then

MsgBox "Duplicate Check Number, Are You Sure?"
End If+

Why are you setting Warnings false here?
There is no warning message generated from this code.

And what is the Table name, since DCount checks the table, not the
form?

If the control name on the form "BlankChecks" and in the table is
[ChkNo]:

If DCount("*","TableNameHere","[ChkNo] = " & Me![ChkNo]) > 1 Then

The above assumes [ChkNo] is a number datatype field. If it is a Text
datatype then use:

"[ChkNo] = '" & Me![ChkNo] & "'")

I think you should be using the BeforeUpdate event, in which case you
could use:

If DCount("*","TableName","[ChkNo] = " & Me![ChkNo]) > 1 Then
If MsgBox ("Duplicate Check Number, Are You Sure?",vbYesNo) = vbNo
Then
Cancel = True
End If

The above will return the focus to the control without saving the
record if No is clicked.

Is the table field [ChkNo] a Number datatype?
If so you should get a message if there are at least 2 records (>1).
I think you want just one record (>0). So ....
If DeCount("*",etc...) >0 Then
should give you the message box if only one record is found with that
number.

Remember, while the ChkNo may be a number, it's the field's datatype
which is of concern here. If it is Text datatype use the second syntax
I gave in my previous message:
"[ChkNo] = '" & Me![ChkNo] & "'")