subsubform within tabcontrol (2002)

Topic

New Reply

i’ve got the following table model:
table1 table 2 table 3

for space saving features i don’t want to create a subform within a subform, the last subform should go into its own page.
i’ve been unable to achieve the following form setup:
– main form with main table (table 1)
– a page control
– on the first page a continuous form representing subtable 2 linked to table1 (no problem with this)
– on the second page a continuous form representing subsubtable 3 linked to subtable 2. here’s where i’m experiencing problem linking the fields. access, logically, presents in its subform wizard table 1 & 3 to link, but i want to link fields between 2 & 3.

in page 2 only the records of table 3 that are linked to the selected record of table 2 should be shown, how do i accomplish this?

Reply | Quote

Replies

If the records in the first subform (based on table 2) have a unique ID, for example an AutoNumber field:
– Place a text box somewhere on the main form.
– Name it txtLink.
– Set its Visible property to No.
– Set its Control Source to

=[SubFormName]![IDName]

where SubFormName is the name of the first subform as a control on the main form, and IDName is the name of the control on the subform that is bound to the unique ID (this control can be hidden).
– Use txtLink in the Link Master Fields property of the second subform.

If there is no single unique ID, but the records in table 2 are identified by a field linking to table 1 and another field, proceed as above, but let txtLink refer to “another field” in the first subform.
The Link Master Fields property of the second subform must contain two items: the field that links the main form to the first subform and txtLink.

Reply | Quote

thanks Hans!

that should do the trick. however, i am more versed in VBA than in form design unfortunately and i’m having difficulty applying the “=[SubFormName]![IDName]”-suggestion to the actual situation.
in vba it’s the control forms(0).Controls(“ctrlsubfrmGlobal”).controls(18).name i want to set as control source to the txtLink textbox you proposed.

for clarity i have given the forms and the subformcontrols in which they are contained different names.
so the subform for table2 named subfrmGlobal is contained in the control named ctrlsubfrmGlobal wich sits in page1 of the main form (=forms(0)).
i tried =subfrmGlobal!txtDetailID without avail (getting a #name? error in txtLink control)

should i explicitly reference both controls and forms in the source control?
but something like =[ctrlsubfrmGlobal]!Forms!subfrmGobal!txtDetailID does not work either.

Reply | Quote

You should use

=[ctrlsubfrmGlobal]![txtDetailID]

That is, the control name of the subform and the name of the control on the subform. It’s also OK to use

=[ctrlsubfrmGlobal].Form![txtDetailID]

Note that this uses .Form, not !Forms.

Reply | Quote

you’re a great help as always Hans!

i’ve got it working now, but only if the name property and source object property of the subfrom control are the same.
so if i set both name property and source object to subfrmGlobal, =[subfrmGlobal]![txtDetailID] in txtLink as source control works, and this can then be used as the master field link for my subsubform on page 2.

if i set name property to ctrlsubfrmGlobal and source object to to subfrmGlobal, neither suggestion seems to work.

thanks!

Reply | Quote