Compare new records to existing.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Shel - 19 Apr 2005 21:40 GMT

My database tracks employee training.

I have tblClasses, tblEmployees, and tblRegistrations. I have a registration
form with EmpName and subformClasses, which allows me to register one
employee for several classes at a time.

My registration form has an OK button. I need a message to appear (on btnOK
click) before adding a new record to the registrations table that indicates
if the employee has already had training for the one or more of the classes
selected in the subform. Then the option to "Yes" add the record anyway, or
"No" cancel the record.

I have everything working except, I can’t figure out how to do the
comparison with the subform and generate my "Yes/No" option. I’m hoping that
one of you wonderful access geniuses will have a relatively simple suggestion
for me.

Thanks!

Signature

Thanks in advance!
Shel

Reply to this Message

Graham Mandeno - 20 Apr 2005 06:05 GMT

Hi Shel

I think you'd be better to use the BeforeUpdate event of the subform. This
is because (assuming the subform is bound) the registration records will be
saved to the table as they are added to the subform, so by the time the user
clicks OK on the main form, all the new child records are already saved.

In the subform's BeforeUpdate event (or maybe better in the BeforeUpdate
event of the control (combobox?) where you select the course) put some code
like this:

If DCount("*", "tblRegistrations", "EmpID=" & Me.EmpID & " and ClassID="
_
& Me.ClassID & " and RegID<>" & Nz(Me.RegID)) <> 0 Then
If MsgBox ("some message", vbQuestion Or vbYesNo) <> vbYes Then
Cancel = True
End IF
End If

This assumes that tblRegistrations has at least three fields:
RegID, a primary key (autonumber)
EmpID, a foreign key linked to tblEmployees
ClassID, a FK linked to tblClasses

It basically checks if there are any records in tblRegistrations with the
same EmpID and ClassID, excluding the current one (identified by RegID).

Signature

Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

> My database tracks employee training.
>
[quoted text clipped - 21 lines]
>
> Thanks!

Reply to this Message

Shel - 20 Apr 2005 15:03 GMT

Awesome! This is exactly the help I needed. You're great!

> Hi Shel
>
[quoted text clipped - 48 lines]
> >
> > Thanks!

Reply to this Message