Multiple item subform showing fields from more than one table

Question

How do I create a subform based on a table linked to the main form's table, but showing fields from other tables?

Here are the table relationships:

This is my form based on the Titles table:

In that blank space I am trying to create a multiple item subform from the TitlePersonnel table, but it needs to show the PersonName from the Persons table and the RoleType from the RoleType table (rather than PersonID and RoleTypeID, which just show up as numbers).  I want both fields in the subform to be combo boxes.   I also need multiple rows, because each Title will have several different TitlePersonnel in it, such as Lead Actor, Director, etc.  And I want the Persons and RoleTypes I choose to populate the TitlePersonnel table.  Hope that makes sense.

I believe I have all the tables linked to each other correctly, but I can't figure out how to make this show up on the Titles form so that I can choose a RoleType and a PersonName for each row in the subform.

I do also have a form based on the Persons table, and am entering people using that form.  So I expect the Person combo box in my subform to have all those names listed.  RoleType is a Value List where I have already typed in all the possible values for that field.  I want that combo box to show all those values.

I hope this is enough info to explain what I am trying to do!  I've been puzzling about this for days, and it's driving me crazy trying to figure it out!  TIA

Answer 1

Your subform can/should use combo boxes to display related data from the Persons and RolesType tables.

I'm not sure why you are using a Value list when you can use the RolesType table.

Answer 2

Hi couture,

You do not need to pull data from multi-tables in your subform. You can use comboboxes to display the related value. In a combobox you can set the width for each column in the Rowsource. The combobox will display the first non zero width column, but use the data from the bound column.

So, using your PersonID as an example. you would add a combobox bound to the PersonID with a Rowsource of

SELECT PersonID, PersonName FROM Persons ORDER BY Personname;

with a Bound column of 1 and Column Widths of 0";2". The combobox wizard will walk you through setting up such a combo.

You would then do the same thing with the RoleTypeID. Value lists should only be used for combo where there is a short, static list. I would not use a Value list for Roletype, especially since you have a RoleType table.

Answer 3

Thank you for the replies, Duane and Scott.  Actually, I used a value list when I created the RoleTypes table, but I have changed that to create a record for each RoleType, as you both suggested.

I've tried doing what you suggested for the subform, but when I try to create the subform, it keeps asking me to relate fields in the subform to fields in the main form.  As you can see from my table relationships, there is no direct relationship from the Persons and RoleType tables to the Titles table.  The relationship has to come from the TitlePersonnel table, which in turn comes from the Persons_RoleTypes table.

I can't seem to figure out how to create combo box controls in a subform that will list the PersonName and RoleType in the combo boxes, then use my choices to populate the TitlePersonnel table.  Somehow, there has to be a connection between PersonName, RoleType, and Title.

Answer 4

The subform's RecordSource should be a query which joins TitlePersonnel and Persons.  We'll come back to the need for Persons in the RecordSource later.  The LinkMasterFields and LinkChildFields properties of the subform control should both be TitleID.

You need two correlated combo boxes in the subform, bound to PersonID and RoleTypeID respectively.  How you correlate them depends on whether you wish to select a role type first or a person first.  For the purposes of the following example I'll assume the former.

The RoleTypeID combo box's RowSource should be:

SELECT RoleTypeID, RoleType

FROM RoleType

ORDER BY RoleType;

It's BoundColumn property should be 1, its ColumnCount 2 and its ColumnWidths 0cm (or inches)

The PersonID combo box's RowSource property should be:

SELECT Persions.PersonID, PersonName

FROM Persons INNER JOIN Person_RoleTypes

ON Persons.PersonID = Person_RoleTypes.PersonID

WHERE RoleTypeID = Form!cboRoleType

ORDER BY PersonName

where cboRoleType is the name of the combo box bound to the RoleTypeID column.  Note the use of the Form property to reference the control, rather than a fully qualified reference.  In the AfterUpdate event procedure of that combo box, empty and requery the other combo box, e.g.

Me.cboPerson = Null

Me.cboPerson.Requery

In the subform's Current event procedure requery the combo box, e.g.

Me.cboPerson.Requery

However! Using correlated combo boxes in continuous forms view where the bound column of the second control is a hidden 'surrogate' key will not work on its own.  The control will appear empty in rows other than the current one.  No data will be lost, merely hidden.  The solution is to use a hybrid control in which a text box bound to the PersonName column from Persons is carefully superimposed on the combo box, leaving only its drop down arrow visible, to give the appearance of a single combo box control.

You'll find examples of the use of hybrid controls in this way in ComboDemo.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.

The example to *'Open Form with Simple 2-level Hierarchy in Continuous Forms View Using Hybrid Controls'*is the appropriate model to follow.  The form in the demo is not a subform, but that is immaterial in this context.

Answer 5

Thank you, Ken!  I think your instructions will give me exactly what I'm trying to achieve.  I'm going to take some time to go through what you have given me here and look at that sample database.  Then I'll come back and tell you if it worked!

Joan