Field cannot update

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

Guest

I have a 3 table database: Employee, Objective, Objective Desc. The Employee
table is linked to the Objective table via the field EmployeeID in a
one-to-many relationship. The Objective Desc table is linked to the
Objective table via the ObjectiveDescID in a one-to-many relationship.

So, both the Employee table and the Objective Desc table link to the
Objective table via one-to-many relationships.

My goal is to allow fields in the Objective table to be updated after
selecting Employee and Objective. I'm using a third table for Objective Desc
so I can use a combo box that allows the user to select the objective.

In this configuration, when I select the Objective description the remaining
fields can't update. I get an error "Field cannot be updated."

They are not locked or set to read-only. And there are no default values.
I'm sure it has someting to do with the way the tables are related. But I
need a separate Objective Desc table for the combo box.
 
You do not need a separate table for the combo box. Create a query based the
table that is the row source for your form. I think the reason you can't
update it is because of the one to many relationship.
The technique of using a query as the row source for a combo to do look ups
 
Klatuu,

How do I keep the combo box from displaying redundant data if I use a query
as you suggest? For example, there are a fixed and defined number of
objectives, objective1, objective2, objective3, etc. I don't want the combo
box to show 10 objective1. Also, I just tried to populate the Objective
table with the fixed objectives but then I have to break the one-to-many
relationship back to the Employee table.

Thanks,

Lee
 
You need to redo your tables. You still need 3 tables, but used differently.
Employees Table
EMP_ID - Autonumber primary key
EMP_NUMBER - Employee's HR identification number
EMP_NAME
etc

Objectives Table
OBJ_ID - Autonumber primary key
OBJ_DESC - text - description of objective

Employee Objectives Table
EMPOBJ_ID - Autonumber primary key
EMP_ID - Long - foreign key to Employee table
OBJ_ID - Long - foreign key to Objectives table

The employee table should be related one to many to the Employee Objectives
table
The Employee Objectives table should be related one to many to the
Objectives Table.

Each Objective should be in the Objectives table one time.
Each Employee should be in the Employee table one time.

The Employee Objectives table will have a row for each objective for each
employee.

Base the combo box row source on the objectives table. The combo can have
two columns, OBJ_ID and OBJ_DESC. OBJ_ID should be the bound column. Make
the column width property of the combo 0";1.5"
This will make the OBJ_ID not show in the drop down if it is the first
column. OBJ_DESC will be 1 1/2 inches wide. You may need to adjust that
value depending on how long a description can be.

Change your form so that the employee info is displayed on a main form and
the objective info is displayed on the sub form. You could use a combo to
select the employee on the main form. The combo for selecting objectives
should be on the sub form.
 
Back
Top