Cascading tables on one form

[goodguy]

In my db, I have a chain of 5 tables (1-Many relationship ) with cascading foreign keys on each table on the many side. Due to normalization, Table3 has a FK to Table2 only, not to Table1, and likewise down the chain.

I have made a form for my users to drill down from Table1-ID to Table4-ID using a series of combo boxes to pick subsequent foreign keys in order to add new records to Table5. However, when the record is saved, only the fields for Table5 should be saved, which includes the FK Table4-ID. The rest of the fields do not pertain to Table5 and so need not be saved.

I reckon I can achieve this by using one of these two approaches:
1. Use an unbound form with VBA code to save new records.
2. Use a compound query of all 5 tables as the form's record source.



A third way would be to have unbound combo boxes for the first three tables and bound controls for fields in Table5. But again I would have to write code to populate the unbound combos.

Is there a better way to get round this?

Regards,
GoodGuy

[June7]

Option 4? Subforms. Five levels is pretty deep. Most I have done is 3 (Form/SubForm/SubSubForm).

[goodguy]

Do I have to nest the subforms in each other?

[June7]

Apparently not. I worked with another poster who set Master/Child links on subforms that were at same level. It seemed to work.

[boblarson]

It is easier to deal with the linking if they are nested but they don't have to be. And seven (7) nested subforms is the limit. If they aren't nested, I don't think there is a limit.

[goodguy]

Thanks. I understand, and I would like to have all my subforms at one level but as I said earlier, the base tables are chained in a linear fashion, so each lower subform will depend on the preceding one.
Is this possible without nesting?
And, is it possible to have a subform on a split form?

[June7]

We have already verified that subforms do not have to be nested. This makes the Master/Child linking a little tricky.

I expect subform is possible on split form. Try it, let us know what happens. I checked out the split form when 2007 first came out, didn't like it, haven't used.

[goodguy]

The "tricky" bit is what I am dreading, as VBA syntax still plays tricks with my mind and memory.

[June7]

VBA is not involved. The Master/Child link properties would reference the key fields in the RecordSources. The complication with not nesting is that the reference has to go through the main form and the container control. I can't find the project I referenced to check this syntax. Maybe boblarsen will offer that.

[goodguy]

Thank you, June7. Does that mean that the Source Object will have to be something other than a table?

[boblarson]

The source object normally is a form. Also, there may be some VBA involved if you don't nest them, but most of the time I believe you would not need it.

[June7]

Doesn't matter if the SourceObject is a table, query, or form. No VBA should be needed. It is a matter of getting the reference in the Master link property correct. Something like: forms!formname.subformcontainername!fieldname

[ssanfu]

I have made a form for my users to drill down from Table1-ID to Table4-ID using a series of combo boxes to pick subsequent foreign keys in order to add new records to Table5. However, when the record is saved, only the fields for Table5 should be saved, which includes the FK Table4-ID. The rest of the fields do not pertain to Table5 and so need not be saved.

I once started a project along the same lines, but I had 7 levels. Attached is an A2K3 example. There are two forms: a data entry using combo boxes and a list form (using list boxes) that I used to have a better understanding of the relationships. At the bottom of the list boxes are text boxes that show the PK.

You could set the main form to a record source (table/query) and only bind the last combo/list box to save the Table5 selection to a field. No VBA required.

My 2cents......



(BTW, I never finished it. The project was superseded - it was made into a web database.)

[goodguy]

Thanks a lot to both of you, June7 and ssanfu. You have been of tremendous help.