DCount to check duplicates

[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

 

[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

 

[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?

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

 

[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?

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

 

[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

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?

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

 

[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

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

 

[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?

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

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

 

[Allen Browne]

message

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

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.

 

[Kari]

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