Navigation combobox on subform ignoring subform's master-child relationship

[DreamingSpires]

I'm in a bit of a pickle with navigation combo boxes; can anyone help?

I have a form, frmProjects, with a continuous subform, sfrPeopleOnThisProject. They have a master-child relationship. The form shows one Project record at a time, while the subform shows all the People records for that project. This works as expected - the correct records show up in the subform as you move through the form records.

To make it easier to glance through the list of subform records (as they can sometimes be quite long), I've added a navigation combobox to the header of the subform. If I understand correctly, as the combobox is within the subform, it should obey the master-child relationship: it should only show the subform records associated with the main form's current record. It does not. Instead, the combo box collects *all* of the records from the table that the subform is based on. So, choose the record for Project 1 in the main form. Up pops the 2 people on Project 1, but the combo box lists both the 2 people on Project 1 and the 3 people on Project 2.

The row source for the subform combo box is:
SELECT tblPersonOnProject.PersonOnProjectID, tblPerson.Fullname FROM tblPersonOnProject INNER JOIN tblPerson ON tblPersonOnProject.PersonFK=tblPerson.PersonID ORDER BY Fullname;

I can make the combo box perform as required by adding "WHERE Project=Forms!frmProject!ProjectID" to the row source and placing a hidden textbox on the main form for ProjectID (the autogenerated primary key) to sit. Have I misunderstood the point of the master-child relationship in the subform? This hidden textbox thing can't be correct.

Any help greatly appreciated!

 

[pbaldy]

The master/child relationship affects the records displayed on the subform. I'm not surprised an unbound combo is not affected. Your solution sounds like it will work, though I'm surprised there isn't already a textbox for it, both on the form and the subform.

 

[DreamingSpires]

Hi Paul, thanks for replying!

The primary key is autogenerated when a new record is recreated. I had understood that it was best practice to not have the PK be something that users filled in themselves. As the PK is fairly meaningless to the user, I then had no reason for it appear on the form. Every other field for the appropriate tables does appear on the forms, because those are to be user-completed. The master-child relationship is set through that PK, but it didn't need to be physically on the form for that to work. Why would I want the PK to then be on the form? Or is this a requirement for Access: hide your PK, but its going to have to be on the form?

If I understand you correctly, master-child relationships from form to subform only apply to the records that the subform itself shows, but will not automatically apply to the records included in a combobox embedded in that subform. I had incorrectly assumed they would apply to everything in that subform! Is there any way to bind the navigation combobox to the subform, so that it will also fall under the master-child relationship? I just felt like the solution I had was so inelegant, it *couldn't* be correct.