Code for check dupes not working

  • Thread starter Thread starter Lostguy
  • Start date Start date
L

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


Lostguy said:
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])))
Click to expand...
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
Click to expand...
 
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
 
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
 
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.

??
??
 
Sir,

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

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

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

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.
 
Back
Top