Binding form to data not working properly - database schema given

[ruci1225]

I have a database with the following tables.

1. tblDOA (DOA is the promary field which is name of DOA)
2. tblEmployee (empID, empLastName, empFirstName, empDOA - used to link to tblDOA)
3. tblEvaluations (EvalID, evalEmpID(links to tblEmployees), evalDate,EvalComments)
4. tblEvalDetails (EvalDetaiID, EvalDetailID(link to tblevaluations), EvalDetailFactorID(link to tblEvalFactor, EvalDetailRating, EvalDetailComments
5. tblEvalFactors - table that will list actual Evaluation questions (FactorID, Factor, Factor Description)

I have created a form but it treats each new entry as a new evaluation OR overwrites the current evaluation. The form currently is working fine and updates the proper tables but it will not allow them to navigate through evaluations based on the advisor they choose it just overwrites the current record.

Ideally, I would like the following:

1. Combobox to select DOA
2. Once Item 1 is chosen combo 2 will update with advisors assigned to DOA in combo 1.

3. Subform will populate and allow them to review all evaluations for advisor assigned to item 2 above OR create a new record (evaldate will need to be different).

All other items are working fine. (Print Report button populates the report in printview based on the record).

Any help would be great, I am obviously having a hard time with binding the form to the tables based on DOA and Advisor (DOA and empID).

I can always send the db to someone to review as currently there is no actual data and just test data.

 

[MajP]

you can not use the same controls for data entry and for navigation. If you want a combo for navigation it needs to be unbound (no control source). The combo used for navigation needs to then have code in the afterupdate to move to the selected record. You may still have a combo for data entry, they just cannot be the same.

 

[ruci1225]

Attached is the database.

I am trying to get the FrmEvaluations to be bound by DOA, then have it populate Advisor and the sub form will show previous evaluations plus the ability to create new (new EvalID.