Compare new records to existing.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
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).
 
Awesome! This is exactly the help I needed. You're great!

Graham Mandeno said:
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).
Click to expand...
 
Back
Top