Subform not updating Junction Table

  • Thread starter Thread starter Kristin
  • Start date Start date
K

Kristin

Main table: Projects with fields for Project Name, Project
Type, Priority, Due Date. Primary key is autonumber called
Project ID

Second table: Employee with fields for Name pieces, email,
etc., where Primary key employee ID is Autonumber.

Because the relationship between employee and project is
many to many, I created a Junction table called
ProjectAssignments contains Project ID, Employee ID, and
Employee role.

I'm now trying to create a main data entry form. The form
will be based on the project, as the list of employees
won't change often and can be done in datasheet view at
this point.

I've designed the project form to contain all of the
necessary descriptive pieces, but I'm now trying to create
a subform that will allow me to set the employees from
that form. Need to be able to assign multiple employees.
I created a subform that has the following
Record Source:

SELECT Employee.Last, ProjectAssignments.Role,
ProjectAssignments.[Project ID] FROM Employee INNER JOIN
ProjectAssignments ON
Employee.EmployeeID=ProjectAssignments.[Employee ID];

This has given two combo boxes on the subform that seem to
allow me to enter multiple individuals. However, when I
attempt to enter employee names, I get a message "Field
cannot be updated". It does let me add the individuals
but updates the employee table, not the Project
Assignments table.

I'd rather have the subform use the "Last" field (or
perhaps the initials field) because I don't know the
employee number.

Also, the role field will not accept entries.

I created the form and subform with a Wizard, which isn't
very helpful, as I'm sure you're aware.

Thanks,
Kristin
 
Start with only two tables, your project table and your
employee information table.

You then need to create an Employee_Role Table, with the
fields [Employee_Role_ID] as your primary, then a
[Project_ID] field as number (linked one to many to your
project table); [Employee_ID] as a number (Linked one to
many to the Employee table); then a field called something
like [Role] as a text field.

Once you have configured the relationships, then you go
into creating a form wizard with only your project table.
Once you have that, open the form in design view check to
see if your magic wand is highlighted, and insert a sub-
form directly into the details section. This will show a
wizard asking which table/query to submit. Select the
Employee_Role Table and click next and so on, it should
ask you if you would like to show only the data linked
base on Project_ID.

Once you have that in there, the default view for the new
subform is usually datasheet view, you should change that
in design view by right-clicking on the sub-form and
selecting form or continuos form. Note that this will also
show the Role_ID,Project_ID and the Employee_ID as a
number. You are able to indicate under design view those
fields as not visible. Also, the names will not be
displayed in your form.

This is just one way that I thought of, there is probably
more ways to get what you need. Give it a try, that's how
I learned to create proper relational databases. Just
trial and error. Eventually, I got the hang of it after
three or four new databases

Jonathan
-----Original Message-----
Main table: Projects with fields for Project Name, Project
Type, Priority, Due Date. Primary key is autonumber called
Project ID

Second table: Employee with fields for Name pieces, email,
etc., where Primary key employee ID is Autonumber.

Because the relationship between employee and project is
many to many, I created a Junction table called
ProjectAssignments contains Project ID, Employee ID, and
Employee role.

I'm now trying to create a main data entry form. The form
will be based on the project, as the list of employees
won't change often and can be done in datasheet view at
this point.

I've designed the project form to contain all of the
necessary descriptive pieces, but I'm now trying to create
a subform that will allow me to set the employees from
that form. Need to be able to assign multiple employees.
I created a subform that has the following
Record Source:

SELECT Employee.Last, ProjectAssignments.Role,
ProjectAssignments.[Project ID] FROM Employee INNER JOIN
ProjectAssignments ON
Employee.EmployeeID=ProjectAssignments.[Employee ID];

This has given two combo boxes on the subform that seem to
allow me to enter multiple individuals. However, when I
attempt to enter employee names, I get a message "Field
cannot be updated". It does let me add the individuals
but updates the employee table, not the Project
Assignments table.

I'd rather have the subform use the "Last" field (or
perhaps the initials field) because I don't know the
employee number.

Also, the role field will not accept entries.

I created the form and subform with a Wizard, which isn't
very helpful, as I'm sure you're aware.

Thanks,
Kristin

.
Click to expand...
 
Got through the first step and the first wizard. When I
go to create the subform, the subform wizard is apparently
not installed. Prior to getting Support's help to
reinstall, can I just create a separate form and
drag/drop, or create both together, and still get there?

-----Original Message-----
Start with only two tables, your project table and your
employee information table.

You then need to create an Employee_Role Table, with the
fields [Employee_Role_ID] as your primary, then a
[Project_ID] field as number (linked one to many to your
project table); [Employee_ID] as a number (Linked one to
many to the Employee table); then a field called something
like [Role] as a text field.

Once you have configured the relationships, then you go
into creating a form wizard with only your project table.
Once you have that, open the form in design view check to
see if your magic wand is highlighted, and insert a sub-
form directly into the details section. This will show a
wizard asking which table/query to submit. Select the
Employee_Role Table and click next and so on, it should
ask you if you would like to show only the data linked
base on Project_ID.

Once you have that in there, the default view for the new
subform is usually datasheet view, you should change that
in design view by right-clicking on the sub-form and
selecting form or continuos form. Note that this will also
show the Role_ID,Project_ID and the Employee_ID as a
number. You are able to indicate under design view those
fields as not visible. Also, the names will not be
displayed in your form.

This is just one way that I thought of, there is probably
more ways to get what you need. Give it a try, that's how
I learned to create proper relational databases. Just
trial and error. Eventually, I got the hang of it after
three or four new databases

Jonathan
-----Original Message-----
Main table: Projects with fields for Project Name, Project
Type, Priority, Due Date. Primary key is autonumber called
Project ID

Second table: Employee with fields for Name pieces, email,
etc., where Primary key employee ID is Autonumber.

Because the relationship between employee and project is
many to many, I created a Junction table called
ProjectAssignments contains Project ID, Employee ID, and
Employee role.

I'm now trying to create a main data entry form. The form
will be based on the project, as the list of employees
won't change often and can be done in datasheet view at
this point.

I've designed the project form to contain all of the
necessary descriptive pieces, but I'm now trying to create
a subform that will allow me to set the employees from
that form. Need to be able to assign multiple employees.
I created a subform that has the following
Record Source:

SELECT Employee.Last, ProjectAssignments.Role,
ProjectAssignments.[Project ID] FROM Employee INNER JOIN
ProjectAssignments ON
Employee.EmployeeID=ProjectAssignments.[Employee ID];

This has given two combo boxes on the subform that seem to
allow me to enter multiple individuals. However, when I
attempt to enter employee names, I get a message "Field
cannot be updated". It does let me add the individuals
but updates the employee table, not the Project
Assignments table.

I'd rather have the subform use the "Last" field (or
perhaps the initials field) because I don't know the
employee number.

Also, the role field will not accept entries.

I created the form and subform with a Wizard, which isn't
very helpful, as I'm sure you're aware.

Thanks,
Kristin

.
Click to expand...
.
Click to expand...
 
With your form in design view, you should be able to click
on the database window button (next to the magic wand) and
bring on top your main database window out in front of
your form.

Drag and drop your Employee Role Table in the details
window of your form in design view.

Hope this helps.

Jonathan
-----Original Message-----
Got through the first step and the first wizard. When I
go to create the subform, the subform wizard is apparently
not installed. Prior to getting Support's help to
reinstall, can I just create a separate form and
drag/drop, or create both together, and still get there?

-----Original Message-----
Start with only two tables, your project table and your
employee information table.

You then need to create an Employee_Role Table, with the
fields [Employee_Role_ID] as your primary, then a
[Project_ID] field as number (linked one to many to your
project table); [Employee_ID] as a number (Linked one to
many to the Employee table); then a field called something
like [Role] as a text field.

Once you have configured the relationships, then you go
into creating a form wizard with only your project table.
Once you have that, open the form in design view check to
see if your magic wand is highlighted, and insert a sub-
form directly into the details section. This will show a
wizard asking which table/query to submit. Select the
Employee_Role Table and click next and so on, it should
ask you if you would like to show only the data linked
base on Project_ID.

Once you have that in there, the default view for the new
subform is usually datasheet view, you should change that
in design view by right-clicking on the sub-form and
selecting form or continuos form. Note that this will also
show the Role_ID,Project_ID and the Employee_ID as a
number. You are able to indicate under design view those
fields as not visible. Also, the names will not be
displayed in your form.

This is just one way that I thought of, there is probably
more ways to get what you need. Give it a try, that's how
I learned to create proper relational databases. Just
trial and error. Eventually, I got the hang of it after
three or four new databases

Jonathan
-----Original Message-----
Main table: Projects with fields for Project Name, Project
Type, Priority, Due Date. Primary key is autonumber called
Project ID

Second table: Employee with fields for Name pieces, email,
etc., where Primary key employee ID is Autonumber.

Because the relationship between employee and project is
many to many, I created a Junction table called
ProjectAssignments contains Project ID, Employee ID, and
Employee role.

I'm now trying to create a main data entry form. The form
will be based on the project, as the list of employees
won't change often and can be done in datasheet view at
this point.

I've designed the project form to contain all of the
necessary descriptive pieces, but I'm now trying to create
a subform that will allow me to set the employees from
that form. Need to be able to assign multiple employees.
I created a subform that has the following
Record Source:

SELECT Employee.Last, ProjectAssignments.Role,
ProjectAssignments.[Project ID] FROM Employee INNER JOIN
ProjectAssignments ON
Employee.EmployeeID=ProjectAssignments.[Employee ID];

This has given two combo boxes on the subform that seem to
allow me to enter multiple individuals. However, when I
attempt to enter employee names, I get a message "Field
cannot be updated". It does let me add the individuals
but updates the employee table, not the Project
Assignments table.

I'd rather have the subform use the "Last" field (or
perhaps the initials field) because I don't know the
employee number.

Also, the role field will not accept entries.

I created the form and subform with a Wizard, which isn't
very helpful, as I'm sure you're aware.

Thanks,
Kristin

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Hi

Somehow I cant reproduce the error you're getting "Field cannot be updated".

- Make sure both ProjectID and EmployeeID are designated as primary key
(i.e. composite primary key)
- Make sure relationship Project -> ProjectAssignments (ie. one-many),
Employee ->ProjectAssignments (ie. one-many) are set up under
Tools>>Relationship.
- The recordsource of your subform :
SELECT Employee.Last, ProjectAssignments.Role,
ProjectAssignments.[Project ID] FROM Employee INNER JOIN
ProjectAssignments ON
Employee.EmployeeID=ProjectAssignments.[Employee ID];
Click to expand...

should include ProjectAssignments.EmployeeID in the Select statement, like
so:

SELECT Employee.Last, ProjectAssignments.Role,
ProjectAssignments.EmployeeID,
ProjectAssignments.[Project ID] FROM Employee INNER JOIN
ProjectAssignments ON
Employee.EmployeeID=ProjectAssignments.[Employee ID];

HTH
Immanuel Sibero




Kristin said:
Main table: Projects with fields for Project Name, Project
Type, Priority, Due Date. Primary key is autonumber called
Project ID

Second table: Employee with fields for Name pieces, email,
etc., where Primary key employee ID is Autonumber.

Because the relationship between employee and project is
many to many, I created a Junction table called
ProjectAssignments contains Project ID, Employee ID, and
Employee role.

I'm now trying to create a main data entry form. The form
will be based on the project, as the list of employees
won't change often and can be done in datasheet view at
this point.

I've designed the project form to contain all of the
necessary descriptive pieces, but I'm now trying to create
a subform that will allow me to set the employees from
that form. Need to be able to assign multiple employees.
I created a subform that has the following
Record Source:

SELECT Employee.Last, ProjectAssignments.Role,
ProjectAssignments.[Project ID] FROM Employee INNER JOIN
ProjectAssignments ON
Employee.EmployeeID=ProjectAssignments.[Employee ID];

This has given two combo boxes on the subform that seem to
allow me to enter multiple individuals. However, when I
attempt to enter employee names, I get a message "Field
cannot be updated". It does let me add the individuals
but updates the employee table, not the Project
Assignments table.

I'd rather have the subform use the "Last" field (or
perhaps the initials field) because I don't know the
employee number.

Also, the role field will not accept entries.

I created the form and subform with a Wizard, which isn't
very helpful, as I'm sure you're aware.

Thanks,
Kristin
Click to expand...
 
Back
Top