Create a form with fields from 3 tables?

Question

When i create a form with fields from two tables, it comes out looking clean with a parent and a sub field, but when i add fields from a 3rd table, not so much. The difference I've noticed between adding 3rd table fields is that the "How do you want to view your data" page does not come up after selecting my fields.  Is there a way to create a form with 3 subforms?

Answer 1

Is there a way to create a form with 3 subforms?

That will depend on the relationships between the tables.  The most common use of a subform is to represent data from a table which is the referencing table in a one-to-many relationship type where the parent form is the referenced table in the relationship type.  The subform will usually, but not necessarily, be in continuous forms or datasheet view.

The subform's table will often be a table which models a many-to-many relationship type between the parent form's table and another table by resolving the relationship type into two one-to-many relationship types.   A very common example is a subform based on an OrderDetails table which models the many-to-many relationship type between Orders and Products tables.

Consequently if you have three tables, each of which is the referencing table in independent relationship types with the parent form's table,  three independent subforms can easily be added to the parent form, linking each on the appropriate primary/foreign keys.

If, on the other hand, the tables are related hierarchically, e.g. Countries----<Regions----<Cities, then you could nest the subforms, i.e. a Countries parent form could contain a Regions subform, linked to the parent on CountryID, and the Regions subform could contain a Cities subform, linked in this case on RegionID.  Both the Countries parent form and the Regions subform would have to be in single form view, however.  Only the Cities subform could be in continuous forms or datasheet view.

An alternative in the case of a hierarchy like this would be to use correlated subforms, e.g. with the above example, a Countries parent form could contain a regions subform in continuous forms or datasheet view, and a cities subform in continuous forms or datasheet view.  The two subforms would be correlated so that, as the user moves form region to region in the first subform, the cities subform is requeried so that it shows only the cities in the currently selected region.  Building correlated subforms does require some familiarity with writing VBA code and building queries with parameters.

You'll find examples of both nested and correlated subforms in CorrelatedSubs.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.  

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

In the zip archive the main CorrelatedSubs.accdb file illustrates how to build correlated or nested subforms in a 3-tier hierarchy of Customers---<Orders---<OrderDetails.  The  CorrelatedSubsWithShipments.accdb extends this to include shipments by the use of three correlated subforms.

Answer 2

I never create my forms that way. I will first create a mainform bound to the parent table. Then I will create subforms bound to the child tables. Then I will open the main form in Design mode and use the Subform control wizard to select the subform to be embedded and let the wizard prompt me for the linking fields.

Note, you can't embed a continuous form or datasheet subform within another continuous form or datasheet subform. So you may need to try to synchronize the forms.

I would need more info on the data and the relationships to advise further.