Junction table and associated forms

[Al_Foote]

I think I'm mostly there...

I've got three tables:
- tblStaff which has people (PK is an ID number)
- tblProj which has projects (PK is an autonumber)
- tblStaffProj which is the junction table.

I've got a form based on tblProject which has a subform using tblStaffProj
with a combo box pulling the staff's names -- all good so far. My remaining
problem is, I can't add additional staff -- I pre-populated the tables a bit
for testing and I have multiple people showing up on projects where
appropriate, and I can change the existing people, but adding a new staff
person doesn't work, the new line won't take input.

Thoughts?

Thanks,
Al

 

[Marshall Barton]

I think I'm mostly there...

I've got three tables:
- tblStaff which has people (PK is an ID number)
- tblProj which has projects (PK is an autonumber)
- tblStaffProj which is the junction table.

I've got a form based on tblProject which has a subform using tblStaffProj
with a combo box pulling the staff's names -- all good so far. My remaining
problem is, I can't add additional staff -- I pre-populated the tables a bit
for testing and I have multiple people showing up on projects where
appropriate, and I can change the existing people, but adding a new staff
person doesn't work, the new line won't take input.

You need to add new people to the staff table before you can
assign them to a project. If you want to do that from the
staffproj subform, then use the combo box's NotInList event
to open the staff form (using OpenArgs to pass the new
name).

Note that assumes you have more info about a person than
just an ID num. If you don't, there's no reason to have a
staff table because the ID num should be the PK and the
autonumber field is redundant.

 

[Al_Foote]

The staff table is filled from another form -- I'm only using extant names
for this process. The issue isn't adding new names to the staff list, it's
creating a new association of existing names to existing projects. I.e., Joe
and Bob are working on project apple. I want to add Carl to project apple as
well. The form, as I have it now, will allow me to change Joe or Bob to
Carl, but not add a third line item for Carl.

The combo box is pulling StaffID, FirstName and LastName from the staff
table and I've told it to store the resultant info (the chosen StaffID) in
the Junction table.

Al

 

[Marshall Barton]

The staff table is filled from another form -- I'm only using extant names
for this process. The issue isn't adding new names to the staff list, it's
creating a new association of existing names to existing projects. I.e., Joe
and Bob are working on project apple. I want to add Carl to project apple as
well. The form, as I have it now, will allow me to change Joe or Bob to
Carl, but not add a third line item for Carl.

The combo box is pulling StaffID, FirstName and LastName from the staff
table and I've told it to store the resultant info (the chosen StaffID) in
the Junction table.

Presumably the subform is displayed in continuous view.
Does it display a blank new record at the bottom? If not,
make sure the form object displayed in the subform control
has its AllowAdditions property set to Yes.

Also double check the subform control's LinkMaster/Child
properties to make sure they are set to the project id field
in their respective tables.

If none of that helps, please provide a more detailed
explanation of what happens when you try to select a person
in the combo box on a new record.

 

[Al_Foote]

I found the issue -- user error. I was somehow bound to a query not the
right table.

Thank you for your assistance, I got a better understanding of the process.

Thanks,
Al

 

[Marshall Barton]

Well, for sure, that can be a problem ;-)

Glad you got it sorted.