Replace Error 3022 VBA with specific field labels?

[Lostguy]

Hello! (I posted this to GettingStarted a few days ago, but no
response. So I deleted the post from there, and am trying here. I
appreciate your help!)

tblEmployee with EmployeeIDpk and LName (holds employee data)
tblEvent with EventIDpk and Event (holds event data) (Check-in,
orientation, CPR training, etc.)
tblEmpEvent with EmployeeIDfk and EventWhenIDfk (holds which
employees did which events)
tblEventWhen with EventWhenIDpk, EventIDfk, EventDate (holds when
each
event happened)


I use a mainform based on tblEventWhen where I enter the date and
then
select the event via a combobox tied to tblEvent.
On that mainform is a continouus subform where I select which
employees did that event


Some rules:
1) An event cannot happen more than once a day. (EventDate and Event
are the same)
2) An employee cannot do the same event more than once. (Event and
EmployeeIDpk are the same)


For #1, I use the following:


Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 3022 Then
MsgBox "This combination of date and event has already been entered.
"
& "Please use the previous entry to prevent duplication of data.",
vbExclamation, "Duplicate Date-Event Combination"
Response = acDataErrContinue
End If
End Sub


I am stumped about #2 and would like to do away with the whole Error
3022 route for #1, and go with some VBA like:


If the combination of EventDate and Event, or the combination of
Event
and EmployeeIDfk, contain duplicates, display a messagebox stating
"[LName] previously completed the [Event] event on [EventDate].
Please
update or delete the previous entry, then reenter the new event
data."


(I set up two duplicate records queries and that works, but it
catches
duplicates after entry instead of as they are being entered.)


??


VR/Lost

 

[Jeanette Cunningham]

Hi Lostguy,
you can catch the duplicate errors earlier by using the BeforeUpdate of the
controls involved - LName, Event and EventDate.
Your code would need to check each of those 3 controls.
If all 3 controls have a value you can popup the warning message, but if any
of the 3 are blank you would suppress the warning message.
I suggest you create a separate sub to check if the warning needs to be
displayed and call it from the Before Update event of each of those 3
controls.

You can then call the same sub in the form's before update event and cancel
the update if it would create a duplicate.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia