linked form/subform adding difficulties

[Guest]

Hello,

I am trying to make a nice form to capture employee evaluatons, based on a
set of 21 possible error codes and then a general comments box for supervisor
input. I have two main tables that keep track of an employee's evaluation -
Evals and Eval Details. Since not all error codes will be selected at one
time (maybe none at all), I decided to make the Eval Details only keep track
of the errors made... for efficiency purposes.

My Evals table has fields Eval ID, Employee ID, Date, and Comments. The
Eval Details table has fields Detail ID, Eval ID and Error code. From these
two tables, I built a form w/custom subform linked via an Eval ID (one to
many). How would I go about adding a 'valid' record to these two tables?
When I add a record now, info on the main form is saved once I tab into the
subform to select the error codes. I need a way to check that an evaluation
for an employee does not already exist for that date... I tried Before Update
event of Parent form, but was still able to add duplicate records. What
should my PK's be for the two tables (this may be one of my problems).

Thank you much!
Carrie

 

[PC Datasheet]

Carrie,

You did a real good job designing your tables! Your PKs are correct! The only
problem is that you need to change the field name of the Date field in your
Evals table. Date is a reserved word in Access and should not be used as a field
name or object name.

To answer your question ---
Create a query based on the Evals table and include only the Employee ID and
MyDate fields. Put the following expression in the criteria of the Employee ID
field:
Forms!MyForm!Employee ID
Then put the following code in the BeforeUpdate event of the MyDate field:
If DCount("*","MyQuery") >0 Then
MsgBox "An Evaluation Already Exists for This Employee On " & Me!MyDate
<<Code for what you want to do when an evaluation already exists >>
End If