The tables are table 1 has a one to many relationship with table 2 and
3. Table 2 has a 1 to many relationship to table 4 and 5.
Would a sub form work in this fashion or would something else be
needed to make this work?
Subforms can be nested (an absurd seven layers deep, if you want to be that
cruel to your users). Two layers is easy... see below.
When looking at the sub form using the wizard it shows all data fields
basically on one form in form view. Is there away to set up a sub
form that looks like a single form type in form view and to tab to the
other forms?
The Wizard isn't very clever, frankly. You can start with the wizard's form
and then customize it.
I'd use:
- a main form (frmMain let's call it) based on Table1, or a query sorting the
data in Table1
- Two subforms on frmMain, based on Table2 (sub2) and Table3 (sub3)
respectively
- You can open the form used in sub2 and view its Properties. Change the
Default View from Datasheet to Single (and you may want to disallow Datasheet,
Pivot Table, and the other irrelevant views).
- Put two subforms on sub2 based on Tables 4 and 5.
If you need more screen real estate, you can put a Tab Control on frmMain; put
sub2 and its subforms on one page, sub3 on another.
Normally a subform is used for the "many" side of a relationship; when you
finish a record on the subform, it will by default go to a new record on that
subform so you can keep adding records. Typing Ctrl-Tab will tab out of the
subform to the next control in the main form's tab order (which may well be
another subform). There's tricks to make it tab automatically to the next
subform as soon as you finish a record - but that should only be done if you
very rarely have two or more child records.
John W. Vinson [MVP]