DCount to check duplicates

S

Secret Squirrel

I'm trying to use a dcount function to check if there is a record that
already exists based on the 3 fields within my dcount function. Not sure I
have the syntax correct for the "DateAbsent". I think that's where my problem
is. Can anyone help clear this up?

If DCount("[LogID]", "[tblAttendance]", "[EmployeeID] = " & Me!EmployeeID &
" And [DateAbsent] = #" & Me!DateAbsent & "# And [TypeID] = " & Me!TypeID >
0) Then
MsgBox "Duplicate Entry. Click OK to cancel", vbExclamation
Me.Undo
Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
MsgBox "Record has been Saved", vbOKOnly
End If
 
A

Allen Browne

Presumably this code is in the Click event of a button? If so, you need to
move the code into the BeforeUpdate event of the *form* (not controls), if
you want it to run in all the circumstances that could trigger the saving of
the record (e.g. moving to another record, sorting, filtering,
menus/toolbar/ribbon, closing form, closing Access, pressing Shift+Enter,
....) The have your Save button save, which will run the Form_BeforeUpdate
code.

This kind of thing (error handling not shown):

Private Sub cmdSave_Click()
If Me.Dirty Then
Me.Dirty = False
MsgBox "Record has been Saved"
End If
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant

strWhere = "([EmployeeID] = " & Nz(Me.EmployeeID,0) & _
") AND ([DateAbsent] = #" & _
Format(Me.DateAbsent, "mm\/dd\/yyyy") & _
"#) AND ([TypeID] = " & Nz(Me.TypeID,0) & ")"
varResult = DLookup("[LogID]", "[tblAttendance]", strWhere)

If Not IsNull(varResult) Then
MsgBox "Duplicate Entry. Click OK to cancel", vbExclamation
Cancel = True
Me.Undo
End If
End Sub

Notes:
=====
1. If you are only looking to see if there are duplicates, DLookup() might
be quicker than asking for a count of duplictes.

2. Using a string for the criteria means you can check you got it right by
adding
Debug.Print strWhere

3. The criteria will be malformed if any of the controls are null, so we
added Nz().

4. The formatting of the date ensures it works correctly if the user's
regional settings are not the same as yours.

5. It will be important to add error handling to the button's click event
procedure to handle the case where the save fails.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
I'm trying to use a dcount function to check if there is a record that
already exists based on the 3 fields within my dcount function. Not sure I
have the syntax correct for the "DateAbsent". I think that's where my
problem
is. Can anyone help clear this up?

If DCount("[LogID]", "[tblAttendance]", "[EmployeeID] = " & Me!EmployeeID
&
" And [DateAbsent] = #" & Me!DateAbsent & "# And [TypeID] = " & Me!TypeID0) Then
MsgBox "Duplicate Entry. Click OK to cancel", vbExclamation
Me.Undo
Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
MsgBox "Record has been Saved", vbOKOnly
End If
Click to expand...
 
S

Secret Squirrel

Hi Allen,

Quick question for you...When it finds a duplicate record it gives me the
message "Duplicate Entry. Click OK to cancel". but then after you click ok I
get another message that says "The search key was not found in any record".
Why would I also get that message?

Allen Browne said:
Presumably this code is in the Click event of a button? If so, you need to
move the code into the BeforeUpdate event of the *form* (not controls), if
you want it to run in all the circumstances that could trigger the saving of
the record (e.g. moving to another record, sorting, filtering,
menus/toolbar/ribbon, closing form, closing Access, pressing Shift+Enter,
....) The have your Save button save, which will run the Form_BeforeUpdate
code.

This kind of thing (error handling not shown):

Private Sub cmdSave_Click()
If Me.Dirty Then
Me.Dirty = False
MsgBox "Record has been Saved"
End If
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant

strWhere = "([EmployeeID] = " & Nz(Me.EmployeeID,0) & _
") AND ([DateAbsent] = #" & _
Format(Me.DateAbsent, "mm\/dd\/yyyy") & _
"#) AND ([TypeID] = " & Nz(Me.TypeID,0) & ")"
varResult = DLookup("[LogID]", "[tblAttendance]", strWhere)

If Not IsNull(varResult) Then
MsgBox "Duplicate Entry. Click OK to cancel", vbExclamation
Cancel = True
Me.Undo
End If
End Sub

Notes:
=====
1. If you are only looking to see if there are duplicates, DLookup() might
be quicker than asking for a count of duplictes.

2. Using a string for the criteria means you can check you got it right by
adding
Debug.Print strWhere

3. The criteria will be malformed if any of the controls are null, so we
added Nz().

4. The formatting of the date ensures it works correctly if the user's
regional settings are not the same as yours.

5. It will be important to add error handling to the button's click event
procedure to handle the case where the save fails.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
I'm trying to use a dcount function to check if there is a record that
already exists based on the 3 fields within my dcount function. Not sure I
have the syntax correct for the "DateAbsent". I think that's where my
problem
is. Can anyone help clear this up?

If DCount("[LogID]", "[tblAttendance]", "[EmployeeID] = " & Me!EmployeeID
&
" And [DateAbsent] = #" & Me!DateAbsent & "# And [TypeID] = " & Me!TypeID0) Then
MsgBox "Duplicate Entry. Click OK to cancel", vbExclamation
Me.Undo
Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
MsgBox "Record has been Saved", vbOKOnly
End If
Click to expand...
Click to expand...
 
A

Allen Browne

Try a compact/repair.

It it still occurs, which line gives the error?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
Hi Allen,

Quick question for you...When it finds a duplicate record it gives me the
message "Duplicate Entry. Click OK to cancel". but then after you click ok
I
get another message that says "The search key was not found in any
record".
Why would I also get that message?

Allen Browne said:
Presumably this code is in the Click event of a button? If so, you need
to
move the code into the BeforeUpdate event of the *form* (not controls),
if
you want it to run in all the circumstances that could trigger the saving
of
the record (e.g. moving to another record, sorting, filtering,
menus/toolbar/ribbon, closing form, closing Access, pressing Shift+Enter,
....) The have your Save button save, which will run the
Form_BeforeUpdate
code.

This kind of thing (error handling not shown):

Private Sub cmdSave_Click()
If Me.Dirty Then
Me.Dirty = False
MsgBox "Record has been Saved"
End If
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant

strWhere = "([EmployeeID] = " & Nz(Me.EmployeeID,0) & _
") AND ([DateAbsent] = #" & _
Format(Me.DateAbsent, "mm\/dd\/yyyy") & _
"#) AND ([TypeID] = " & Nz(Me.TypeID,0) & ")"
varResult = DLookup("[LogID]", "[tblAttendance]", strWhere)

If Not IsNull(varResult) Then
MsgBox "Duplicate Entry. Click OK to cancel", vbExclamation
Cancel = True
Me.Undo
End If
End Sub

Notes:
=====
1. If you are only looking to see if there are duplicates, DLookup()
might
be quicker than asking for a count of duplictes.

2. Using a string for the criteria means you can check you got it right
by
adding
Debug.Print strWhere

3. The criteria will be malformed if any of the controls are null, so we
added Nz().

4. The formatting of the date ensures it works correctly if the user's
regional settings are not the same as yours.

5. It will be important to add error handling to the button's click event
procedure to handle the case where the save fails.

message
I'm trying to use a dcount function to check if there is a record that
already exists based on the 3 fields within my dcount function. Not
sure I
have the syntax correct for the "DateAbsent". I think that's where my
problem
is. Can anyone help clear this up?

If DCount("[LogID]", "[tblAttendance]", "[EmployeeID] = " &
Me!EmployeeID
&
" And [DateAbsent] = #" & Me!DateAbsent & "# And [TypeID] = " &
Me!TypeID

0) Then
MsgBox "Duplicate Entry. Click OK to cancel", vbExclamation
Me.Undo
Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
MsgBox "Record has been Saved", vbOKOnly
End If
Click to expand...
Click to expand...
Click to expand...
 
S

Secret Squirrel

I tried the compact/repair. Still gives me that message. Not sure what line
is causing it. I put error handling in the code but it won't show me the line
error.

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_BeforeUpdate
Dim strWhere As String
Dim varResult As Variant

strWhere = "([EmployeeID] = " & Nz(Me.EmployeeID, 0) & _
") AND ([DateAbsent] = #" & _
Format(Me.DateAbsent, "mm\/dd\/yyyy") & _
"#) AND ([TypeID] = " & Nz(Me.TypeID, 0) & ")"
varResult = DLookup("[LogID]", "[tblAttendance]", strWhere)

If Not IsNull(varResult) Then
MsgBox "Duplicate Entry. Click OK to cancel", vbExclamation
Cancel = True
Me.Undo
End If
Exit_BeforeUpdate:
Exit Sub

Err_BeforeUpdate:
MsgBox err.Number & err.Description
Resume Exit_BeforeUpdate
End Sub

Allen Browne said:
Try a compact/repair.

It it still occurs, which line gives the error?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
Hi Allen,

Quick question for you...When it finds a duplicate record it gives me the
message "Duplicate Entry. Click OK to cancel". but then after you click ok
I
get another message that says "The search key was not found in any
record".
Why would I also get that message?

Allen Browne said:
Presumably this code is in the Click event of a button? If so, you need
to
move the code into the BeforeUpdate event of the *form* (not controls),
if
you want it to run in all the circumstances that could trigger the saving
of
the record (e.g. moving to another record, sorting, filtering,
menus/toolbar/ribbon, closing form, closing Access, pressing Shift+Enter,
....) The have your Save button save, which will run the
Form_BeforeUpdate
code.

This kind of thing (error handling not shown):

Private Sub cmdSave_Click()
If Me.Dirty Then
Me.Dirty = False
MsgBox "Record has been Saved"
End If
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant

strWhere = "([EmployeeID] = " & Nz(Me.EmployeeID,0) & _
") AND ([DateAbsent] = #" & _
Format(Me.DateAbsent, "mm\/dd\/yyyy") & _
"#) AND ([TypeID] = " & Nz(Me.TypeID,0) & ")"
varResult = DLookup("[LogID]", "[tblAttendance]", strWhere)

If Not IsNull(varResult) Then
MsgBox "Duplicate Entry. Click OK to cancel", vbExclamation
Cancel = True
Me.Undo
End If
End Sub

Notes:
=====
1. If you are only looking to see if there are duplicates, DLookup()
might
be quicker than asking for a count of duplictes.

2. Using a string for the criteria means you can check you got it right
by
adding
Debug.Print strWhere

3. The criteria will be malformed if any of the controls are null, so we
added Nz().

4. The formatting of the date ensures it works correctly if the user's
regional settings are not the same as yours.

5. It will be important to add error handling to the button's click event
procedure to handle the case where the save fails.

message
I'm trying to use a dcount function to check if there is a record that
already exists based on the 3 fields within my dcount function. Not
sure I
have the syntax correct for the "DateAbsent". I think that's where my
problem
is. Can anyone help clear this up?

If DCount("[LogID]", "[tblAttendance]", "[EmployeeID] = " &
Me!EmployeeID
&
" And [DateAbsent] = #" & Me!DateAbsent & "# And [TypeID] = " &
Me!TypeID

0) Then
MsgBox "Duplicate Entry. Click OK to cancel", vbExclamation
Me.Undo
Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
MsgBox "Record has been Saved", vbOKOnly
End If
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

There must be something else going on here as well.

From what you say, the problem is not in this code, but in something else
that's happening after it runs. That could be anywhere, e.g. the code that
triggered the save, or the form's AfterUpdate or Current event, or the code
in some control's event, or even some other form's event (e.g. timer), or
....

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
I tried the compact/repair. Still gives me that message. Not sure what line
is causing it. I put error handling in the code but it won't show me the
line
error.

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_BeforeUpdate
Dim strWhere As String
Dim varResult As Variant

strWhere = "([EmployeeID] = " & Nz(Me.EmployeeID, 0) & _
") AND ([DateAbsent] = #" & _
Format(Me.DateAbsent, "mm\/dd\/yyyy") & _
"#) AND ([TypeID] = " & Nz(Me.TypeID, 0) & ")"
varResult = DLookup("[LogID]", "[tblAttendance]", strWhere)

If Not IsNull(varResult) Then
MsgBox "Duplicate Entry. Click OK to cancel", vbExclamation
Cancel = True
Me.Undo
End If
Exit_BeforeUpdate:
Exit Sub

Err_BeforeUpdate:
MsgBox err.Number & err.Description
Resume Exit_BeforeUpdate
End Sub

Allen Browne said:
Try a compact/repair.

It it still occurs, which line gives the error?

message
Hi Allen,

Quick question for you...When it finds a duplicate record it gives me
the
message "Duplicate Entry. Click OK to cancel". but then after you click
ok
I
get another message that says "The search key was not found in any
record".
Why would I also get that message?

:

Presumably this code is in the Click event of a button? If so, you
need
to
move the code into the BeforeUpdate event of the *form* (not
controls),
if
you want it to run in all the circumstances that could trigger the
saving
of
the record (e.g. moving to another record, sorting, filtering,
menus/toolbar/ribbon, closing form, closing Access, pressing
Shift+Enter,
....) The have your Save button save, which will run the
Form_BeforeUpdate
code.

This kind of thing (error handling not shown):

Private Sub cmdSave_Click()
If Me.Dirty Then
Me.Dirty = False
MsgBox "Record has been Saved"
End If
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant

strWhere = "([EmployeeID] = " & Nz(Me.EmployeeID,0) & _
") AND ([DateAbsent] = #" & _
Format(Me.DateAbsent, "mm\/dd\/yyyy") & _
"#) AND ([TypeID] = " & Nz(Me.TypeID,0) & ")"
varResult = DLookup("[LogID]", "[tblAttendance]", strWhere)

If Not IsNull(varResult) Then
MsgBox "Duplicate Entry. Click OK to cancel", vbExclamation
Cancel = True
Me.Undo
End If
End Sub

Notes:
=====
1. If you are only looking to see if there are duplicates, DLookup()
might
be quicker than asking for a count of duplictes.

2. Using a string for the criteria means you can check you got it
right
by
adding
Debug.Print strWhere

3. The criteria will be malformed if any of the controls are null, so
we
added Nz().

4. The formatting of the date ensures it works correctly if the user's
regional settings are not the same as yours.

5. It will be important to add error handling to the button's click
event
procedure to handle the case where the save fails.

message
I'm trying to use a dcount function to check if there is a record
that
already exists based on the 3 fields within my dcount function. Not
sure I
have the syntax correct for the "DateAbsent". I think that's where
my
problem
is. Can anyone help clear this up?

If DCount("[LogID]", "[tblAttendance]", "[EmployeeID] = " &
Me!EmployeeID
&
" And [DateAbsent] = #" & Me!DateAbsent & "# And [TypeID] = " &
Me!TypeID

0) Then
MsgBox "Duplicate Entry. Click OK to cancel", vbExclamation
Me.Undo
Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
MsgBox "Record has been Saved", vbOKOnly
End If
Click to expand...
Click to expand...
Click to expand...
 
S

Secret Squirrel

Ok I think I'm on to something. I checked the other code on my form and I
can't see anything else that would cause that message. Then I looked at the
structure of my table. I had the default value in my field "TypeID" as "0".
This must be the default when you select the datatype "number". I removed
that and then tested it again. Now I don't get that message anymore but I do
get this message "No Current Record" after I try and enter a duplicate record
and get my "Duplicate Entry. Click Ok to cancel" message. So one message
disappeared and now another appears. Any ideas why I would get "No Current
Record" now?

Allen Browne said:
There must be something else going on here as well.

From what you say, the problem is not in this code, but in something else
that's happening after it runs. That could be anywhere, e.g. the code that
triggered the save, or the form's AfterUpdate or Current event, or the code
in some control's event, or even some other form's event (e.g. timer), or
....

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
I tried the compact/repair. Still gives me that message. Not sure what line
is causing it. I put error handling in the code but it won't show me the
line
error.

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_BeforeUpdate
Dim strWhere As String
Dim varResult As Variant

strWhere = "([EmployeeID] = " & Nz(Me.EmployeeID, 0) & _
") AND ([DateAbsent] = #" & _
Format(Me.DateAbsent, "mm\/dd\/yyyy") & _
"#) AND ([TypeID] = " & Nz(Me.TypeID, 0) & ")"
varResult = DLookup("[LogID]", "[tblAttendance]", strWhere)

If Not IsNull(varResult) Then
MsgBox "Duplicate Entry. Click OK to cancel", vbExclamation
Cancel = True
Me.Undo
End If
Exit_BeforeUpdate:
Exit Sub

Err_BeforeUpdate:
MsgBox err.Number & err.Description
Resume Exit_BeforeUpdate
End Sub

Allen Browne said:
Try a compact/repair.

It it still occurs, which line gives the error?

message
Hi Allen,

Quick question for you...When it finds a duplicate record it gives me
the
message "Duplicate Entry. Click OK to cancel". but then after you click
ok
I
get another message that says "The search key was not found in any
record".
Why would I also get that message?

:

Presumably this code is in the Click event of a button? If so, you
need
to
move the code into the BeforeUpdate event of the *form* (not
controls),
if
you want it to run in all the circumstances that could trigger the
saving
of
the record (e.g. moving to another record, sorting, filtering,
menus/toolbar/ribbon, closing form, closing Access, pressing
Shift+Enter,
....) The have your Save button save, which will run the
Form_BeforeUpdate
code.

This kind of thing (error handling not shown):

Private Sub cmdSave_Click()
If Me.Dirty Then
Me.Dirty = False
MsgBox "Record has been Saved"
End If
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant

strWhere = "([EmployeeID] = " & Nz(Me.EmployeeID,0) & _
") AND ([DateAbsent] = #" & _
Format(Me.DateAbsent, "mm\/dd\/yyyy") & _
"#) AND ([TypeID] = " & Nz(Me.TypeID,0) & ")"
varResult = DLookup("[LogID]", "[tblAttendance]", strWhere)

If Not IsNull(varResult) Then
MsgBox "Duplicate Entry. Click OK to cancel", vbExclamation
Cancel = True
Me.Undo
End If
End Sub

Notes:
=====
1. If you are only looking to see if there are duplicates, DLookup()
might
be quicker than asking for a count of duplictes.

2. Using a string for the criteria means you can check you got it
right
by
adding
Debug.Print strWhere

3. The criteria will be malformed if any of the controls are null, so
we
added Nz().

4. The formatting of the date ensures it works correctly if the user's
regional settings are not the same as yours.

5. It will be important to add error handling to the button's click
event
procedure to handle the case where the save fails.

message
I'm trying to use a dcount function to check if there is a record
that
already exists based on the 3 fields within my dcount function. Not
sure I
have the syntax correct for the "DateAbsent". I think that's where
my
problem
is. Can anyone help clear this up?

If DCount("[LogID]", "[tblAttendance]", "[EmployeeID] = " &
Me!EmployeeID
&
" And [DateAbsent] = #" & Me!DateAbsent & "# And [TypeID] = " &
Me!TypeID

0) Then
MsgBox "Duplicate Entry. Click OK to cancel", vbExclamation
Me.Undo
Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
MsgBox "Record has been Saved", vbOKOnly
End If
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

message
... So one message disappeared and now another appears.
Any ideas why I would get "No Current Record" now?
Click to expand...

If the error is not being generated from the code, then we can't see the
cause of the error here.

There are myriads of scenarios that could generate errors; it would be
pointless to try to guess at all the possible causes.
 
K

Kari

The "No CUrrent Record" error can be triggered by trying to Refresh without a
valid record. This one is easy to miss. . . . . .
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Jet Engine Error Message 2
Close Form Event 4
Report Missing Data 2
check for two fields instead of one 4
Problems using the DCount Function 1
Run time error '2001' You cancelled the previous operation 2
if duplicate goto record 3
When saving, avoid prompting on cmdSave, show for everything else. 8

Top