Check for Duplicate

  • Thread starter Thread starter Dave Elliott
  • Start date Start date
D

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
 
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
 
The table name is tblchecks
What would be the correct sytax then?

Cameron Sutherland said:
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


.
Click to expand...
Click to expand...
 
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+
Click to expand...

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.
 
The table name is tblchecks
What would be the correct sytax then?
Click to expand...

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.
 
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
fredg said:
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+
Click to expand...

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.
Click to expand...
 
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
fredg said:
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+
Click to expand...

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.
Click to expand...
Click to expand...

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] & "'")
 
THANKS,
DAVE

fredg said:
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
fredg said:
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
Click to expand...
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!") >
Click to expand...
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.
Click to expand...
Click to expand...

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] & "'")
Click to expand...
 
Back
Top