Continuous Subform that retrieves data from one table and saves to a different table

[Wombat]

I have a Traits table that has fields TraitKey and Trait and I'm looking for the subform to show one record for each trait so that I can give the employee a score for each trait.

I'm trying to save that score in an EmployeeTraitScores table that has the fields EmpID, Trait, Score.



If I bind the subform to the traits field, the continuous form shows each one properly but then I don't know how to save to the scores to the EmployeeTraitScores table.

If I bind the subform to the EmployeeTraitScores table then it can save a record but I can only get the Dlookup to show the first trait.

Does anyone have any ideas?

[John_G]

Your subform has to be bound to the EmployeeTraitScores table. The Traits table is just a lookup.

How are you inserting the data records into the EmployeeTraitScores table? Is there a fixed number for each employee (i.e. one for each trait?), and how are the Scores being entered? From what you said, all you are using the DLookup for is to return the text equivalent of each Trait_ID, so the user can see it, but not to store it anywhere - is that correct?

Can you provide more info, please?

John

[Wombat]

There is one score and a Comment for each trait for each employee but some employees have more traits than others (I need to filter by supervisor or hourly). The score is a Value List (1-5).
I was attempting to use a Dlookup as the recordsource, which, of course, failed because it only returned the first one.

So binding to the EmployeeTraitScores table makes sense so that my score and comment can be saved, but then how do I list the traits? I guess I could just make a supervisor form and an hourly form and hard-wire it, but it seems like there must be a way to accommodate a varying number of traits on one form.

[John_G]

Hi -

Your EmployeeTraitScores table also needs to have a field for the Trait_ID, so that each record in it will tell you:

This Employee A received a score of B for Trait C. Your data entry sub-form could use a combo box to list the traits (filtered according to supervisor or hourly), and store the TraidID into the table. The users do not need to see the Trait_ID in the combo box list. Reports or other forms which need to display the trait text could have a query as the record source (You will amost certainly need an Employee-EmployeeTraitScores-Traits query somewhere).

HTH

John