Code for check dupes not working

[Lostguy]

Hello!

I put this into the AfterUpdate event of the EmployeeIDfk combobox on
the continuous subform.

(I have Date and Event on the mainform, and then select Employees on
the subform. Since Event and Employees are in different tables, I
could not set up an index, so I was using a Dupe Record query. I am
trying to run the query after entering each employee automatically as
soon as the subform employee field is updated.)


Here's the SQL for qryEventEmpDupes:

SELECT qryEventEmp.EmployeeIDpk, qryEventEmp.Event,
qryEventEmp.EventDate
FROM qryEventEmp
WHERE (((qryEventEmp.EmployeeIDpk) In (SELECT [EmployeeIDpk] FROM
[qryEventEmp] As Tmp GROUP BY [EmployeeIDpk],[Event] HAVING Count(*)

1 And [Event] = [qryEventEmp].[Event])))

ORDER BY qryEventEmp.EmployeeIDpk, qryEventEmp.Event;

Here's the code that doesn't seem to be doing anything:

Private Sub EmployeeIDfk_AfterUpdate()
If DCount("*", "qryEventEmpDupes") > 0 Then
MsgBox "Duplicate Records Found!"
Cancel = True
End If
End Sub

I appreciate any help!

VR/Lost

 

[Klatuu]

In this case, it means your query is returning no records. You don't have
any criteria specified in the DCount function, so it will return the number
of records in the Domain.
--
Dave Hargis, Microsoft Access MVP

Hello!

I put this into the AfterUpdate event of the EmployeeIDfk combobox on
the continuous subform.

(I have Date and Event on the mainform, and then select Employees on
the subform. Since Event and Employees are in different tables, I
could not set up an index, so I was using a Dupe Record query. I am
trying to run the query after entering each employee automatically as
soon as the subform employee field is updated.)


Here's the SQL for qryEventEmpDupes:

SELECT qryEventEmp.EmployeeIDpk, qryEventEmp.Event,
qryEventEmp.EventDate
FROM qryEventEmp
WHERE (((qryEventEmp.EmployeeIDpk) In (SELECT [EmployeeIDpk] FROM
[qryEventEmp] As Tmp GROUP BY [EmployeeIDpk],[Event] HAVING Count(*)

1 And [Event] = [qryEventEmp].[Event])))

ORDER BY qryEventEmp.EmployeeIDpk, qryEventEmp.Event;

Here's the code that doesn't seem to be doing anything:

Private Sub EmployeeIDfk_AfterUpdate()
If DCount("*", "qryEventEmpDupes") > 0 Then
MsgBox "Duplicate Records Found!"
Cancel = True
End If
End Sub

I appreciate any help!

VR/Lost

 

[KenSheridan via AccessMonster.com]

I assume that you want to check for an existing instance of the same event,
date and employee as the data currently being entered. Right? If so then a
duplicates query doesn't help as at this stage the duplicate does not yet
exist. Create a simple query which returns all current instances of
event/date/employees and then examine this in the control's BeforeUpdate
event procedure (not AfterUpdate) for an existing row with the same event,
date and EmployeeIDfk value currently being entered:

Private Sub EmployeeIDfk_BeforeUpdate()

Dim ctrl As Control
Dim strCriteria As String

Set ctrl = Me.ActiveControl

strCriteria = _
"EventDate = # " & Format(Me.Parent.EventDate,"yyyy-mm-dd") & "# And
" & _
"Event = """ & Me.Parent.Event & "" And " _
"EmployeeIDfk = " & ctrl

If Not IsNull(DLookup("EmployeeIDfk", "YourQueryName", strCriteria)) Then
MsgBox "Duplicate Record Found!", vbExclamation, "Invalid Operation"
Cancel = True
End If

End Sub

This assumes that EventDate is of date/time data type, Event is of Text data
type and EmployeeIDfk is of number data type.

Ken Sheridan
Stafford, England

Hello!

I put this into the AfterUpdate event of the EmployeeIDfk combobox on
the continuous subform.

(I have Date and Event on the mainform, and then select Employees on
the subform. Since Event and Employees are in different tables, I
could not set up an index, so I was using a Dupe Record query. I am
trying to run the query after entering each employee automatically as
soon as the subform employee field is updated.)

Here's the SQL for qryEventEmpDupes:

SELECT qryEventEmp.EmployeeIDpk, qryEventEmp.Event,
qryEventEmp.EventDate
FROM qryEventEmp
WHERE (((qryEventEmp.EmployeeIDpk) In (SELECT [EmployeeIDpk] FROM
[qryEventEmp] As Tmp GROUP BY [EmployeeIDpk],[Event] HAVING Count(*)

1 And [Event] = [qryEventEmp].[Event])))

ORDER BY qryEventEmp.EmployeeIDpk, qryEventEmp.Event;

Here's the code that doesn't seem to be doing anything:

Private Sub EmployeeIDfk_AfterUpdate()
If DCount("*", "qryEventEmpDupes") > 0 Then
MsgBox "Duplicate Records Found!"
Cancel = True
End If
End Sub

I appreciate any help!

VR/Lost

 

[KenSheridan via AccessMonster.com]

Correction. Should have been:

Private Sub EmployeeIDfk_BeforeUpdate()

Dim ctrl As Control
Dim strCriteria As String

Set ctrl = Me.ActiveControl

strCriteria = _
"EventDate = # " & Format(Me.Parent.EventDate,"yyyy-mm-dd") & _
"# And Event = """ & Me.Parent.Event & "" And " & _
"EmployeeIDfk = " & ctrl

If Not IsNull(DLookup("EmployeeIDfk", "YourQueryName", strCriteria)) Then
MsgBox "Duplicate Record Found!", vbExclamation, "Invalid Operation"
Cancel = True
End If

End Sub

Ken Sheridan
Stafford, England

 

[Lostguy]

Hello!
I keep getting Runtime error 2645 "Application-defined or object-
defined error" in the StrCriteria line below when I select an
intentionally duplicated employee name from the cboEmployeeID. (I am
testing the code.)

Here's the code courtesy of Mr. Sheridan (I changed the name of the
combobox to be a different name than that of the the control source,
so that may be where the problem is):

Private Sub cboEmployeeID_BeforeUpdate(Cancel As Integer)
Dim ctrl As Control
Dim strCriteria As String
Set ctrl = Me.ActiveControl
strCriteria = "EventDate = # " & Format(Me.Parent.EventDate, "yyyy-mm-
dd") & "# And Event = """ & Me.Parent.Event & """ And " &
"EmployeeIDfk = " & ctrl
If Not IsNull(DLookup("EmployeeIDfk", "qryEventEmp", strCriteria))
Then
MsgBox "Duplicate Record Found!", vbExclamation, "Invalid Operation"
Cancel = True
End If
End Sub

Setup:
Form based on tblEventWhen: EventWhen IDpk (number), EventIDfk
(number), EventDate (date)
Subform based on tblEmpEvent: EmpEventIDpk (number), EventWhenIDfk
(number), EmployeeIDfk (number)
tblEvent: EventIDpk, Event
tblEmployee: EmployeeIDpk,LName

On subform, cboEmployeeID with recordsource as EmployeeIDfk getting
info from SELECT EmployeeIDpk and LName from tblEMployee, column width
0,2", etc.
qryEventEmp: EmployeeIDfk, EmployeeIDpk, FName, LName, EventIDfk,
EVentDate, Event

I appreciate the help!

VR/Lost

 

[KenSheridan via AccessMonster.com]

Firstly I'm seeing the following as three separate lines here:

strCriteria = "EventDate = # " & Format(Me.Parent.EventDate, "yyyy-mm-
dd") & "# And Event = """ & Me.Parent.Event & """ And " &
"EmployeeIDfk = " & ctrl

I'm assuming it has simply been split over three here, and is entered as one
line in the original code. You'll have noticed that I'd used the underscore
continuation character in my original code for better readability.

1. There's a redundant space after the first # character.

2. It appears that your parent form includes an EventIDfk control rather than
an Event control. I imagine that EventIDfk is a number data type, in which
case the value does not need to be wrapped in quotes when building the string
expression.

3. You'd then reference the EventIDfk column in qryEventEmp rather than the
Event column, in which case the expression would be:

strCriteria = "EventDate = #" & Format(Me.Parent.EventDate, "yyyy-mm-dd") &
"# And EventIDfk = " & Me.Parent.EventIDfk & " And " & "EmployeeIDfk = " &
ctrl

I've left this as a single line rather than using continuation characters to
split it, but your newsreader will probably have split it over several lines
when you read it.

Ken Sheridan
Stafford, England

 

[Lostguy]

Hello,all!

I thought that we had this nipped, but there is still a problem:

In my database, I enter the date and event on the mainform, and then
select the employees who did that event on that date on the subform.
(The thread above has all the details of the tables and fields.)

So the first entry is: 1/1/00-Bowling-Smith-Jones

I try to make an entry: 1/1/00-Bowling and the multifield index
catches that and says that that Bowling already happened on 1/1/00.

So this is trapping "same event-same date"


I go back to the original entry and try to make: 1/1/00-Bowling-Smith-
Smith.

This code traps that (Smith entered twice) (So this is trapping "same
event-same date-same person"):

Private Sub cboEmployeeID_BeforeUpdate(Cancel As Integer)
Dim ctrl As Control
Dim strCriteria As String
Set ctrl = Me.ActiveControl
strCriteria = "EventDate = #" & Format(Me.Parent.EventDate, "yyyy-mm-
dd") & "# And EventIDfk = " & Me.Parent.EventIDfk & " And " &
"EmployeeIDfk = " & ctrl
If Not IsNull(DLookup("EmployeeIDfk", "qryEventEmp", strCriteria))
Then
MsgBox "Duplicate Record Found!", vbExclamation, "Invalid Operation"
Cancel = True
End If
End Sub


My problem is that there is still one duplicate situation that is not
being trapped: (same event-same person/people-different date)

1st entry: 1/1/00-Bowling-Smith-Jones
2nd entry: 1/2/00-Bowling-Smith-Jones

The message should be "Smith and Jones already went Bowling on 1/1/00.
We are only tracking the latest occurrence (not history) of events.
Please either edit this or the past event to avoid duplication."

Can the code above be altered to trap this situation as well?

Thanks!

VR/Lost

 

[KenSheridan via AccessMonster.com]

What you seem to be saying is that one person can only undertake the same
type of event once for all time? That sounds somewhat restrictive, but if
that's the constraint then you need to exclude the date from the criteria
completely:

strCriteria = "EventIDfk = " & Me.Parent.EventIDfk & " And " & "EmployeeIDfk
= " & ctrl

Ken Sheridan
Stafford, England

 

[Lost]

Sir,

Here is the code now ( I remarked out the original line):

Private Sub cboEmployeeID_BeforeUpdate(Cancel As Integer)
Dim ctrl As Control
Dim strCriteria As String
Set ctrl = Me.ActiveControl
'strCriteria = "EventDate = #" & Format(Me.Parent.EventDate, "yyyy-mm-dd") &
"# And EventIDfk = " & Me.Parent.EventIDfk & " And " & "EmployeeIDfk = " &
ctrl
strCriteria = "EventIDfk = " & Me.Parent.EventIDfk & " And " & "EmployeeIDfk
= " & ctrl
If Not IsNull(DLookup("EmployeeIDfk", "qryEventEmp", strCriteria)) Then
MsgBox "Duplicate Record Found!", vbExclamation, "Invalid Operation"
Cancel = True
End If
End Sub


So I tried a few tests.

My original entry:
7/16/2013
Driver's License Expiration
Smith

I enter 7-16-2013 and Driver's License Expiration and the multifield index
stops it.

I enter a new entry:
7-17-2013
Driver's License Expiration
<Select Smith and your code stops it. Now hit backspace to clear this.>
<Next employee line is blank. Accidentally click this and you get this error
"Syntax error (missing operator) in query expression 'EventIDfk= 57 AND
EmployeeIDfk='." and the DEBUGGER comes up.

??
??

 

[Lost]

Sir,

Forgot to add that it is this line that the debugger is catching:

If Not IsNull(DLookup("EmployeeIDfk", "qryEventEmp", strCriteria)) Then

VR/

 

[KenSheridan via AccessMonster.com]

Judging by the error message the cboEmployeeID control is Null, i.e. the
combo box is empty, which is odd as presumably you are selecting an employee
in it. Moreover, as all you've done is remove the date from the criteria, if
it was inserting the employee ID value in the criteria expression previously
then there should be no reason for it not to do so now. I don't think I can
say more than that without being there, but when debugging the code look for
any reason why the combo box should be Null.

Ken Sheridan
Stafford, England

 

[KenSheridan via AccessMonster.com]

Hang on, I've just noticed that you are deleting the entry in the control by
means of the backspace key, which explains the Null. Make the code
conditional on the control's value not being Null:

Private Sub cboEmployeeID_BeforeUpdate(Cancel As Integer)

Dim ctrl As Control
Dim strCriteria As String

Set ctrl = Me.ActiveControl

If Not IsNull(ctrl) Then
strCriteria = "EventIDfk = " & Me.Parent.EventIDfk & _
" And " & "EmployeeIDfk = " & ctrl

If Not IsNull(DLookup("EmployeeIDfk", "qryEventEmp", strCriteria))
Then
MsgBox "Duplicate Record Found!", vbExclamation, "Invalid
Operation"
Cancel = True
End If
End If

End Sub

Ken Sheridan
Stafford, England

 

[John W. Vinson]

Sir,

Here is the code now ( I remarked out the original line):

Private Sub cboEmployeeID_BeforeUpdate(Cancel As Integer)
Dim ctrl As Control
Dim strCriteria As String
Set ctrl = Me.ActiveControl
'strCriteria = "EventDate = #" & Format(Me.Parent.EventDate, "yyyy-mm-dd") &
"# And EventIDfk = " & Me.Parent.EventIDfk & " And " & "EmployeeIDfk = " &
ctrl
strCriteria = "EventIDfk = " & Me.Parent.EventIDfk & " And " & "EmployeeIDfk
= " & ctrl
If Not IsNull(DLookup("EmployeeIDfk", "qryEventEmp", strCriteria)) Then
MsgBox "Duplicate Record Found!", vbExclamation, "Invalid Operation"
Cancel = True
End If
End Sub

Step through the code and see what is actually being loaded into strCriteria.
My guess is that ctrl is not returning what you think it is.