Nested subforms and linking

[Darren Kozey via AccessMonster.com]

I'm having a problem linking a subform that sits on an unbound form (that
is also a subform), with the primary key field in the main form to allow
synchronizing of the records. I will try to illustrate the structure below:

Contacts - Main form bound to "Contacts" table with ContactID as key
|
|___frmEstimate - bound by "SELECT DISTINCTROW Contacts.* FROM Contacts
WHERE ((Contacts.ContactID=forms!Contacts!ContactID));"
|
|___subfrmEstimateSummary - unbound
|
|___subfrmConsummables - bound to "tblConsumables"

There are 3 levels of subforms: frmEstimate, subfrmEstimateSummary,
subfrmConsummables. The tblConsummables is related to Contacts via
ContactID as one-to-many.

I am trying to link the child field of the subfrmConsummables ContactID
with the main Contacts form ContactID, but because the parent subform
(subfrmEstimateSummary) is unbound, I can not link the child and master
fields. I know they can be typed in manually, but I'm not sure what to
type, and even if it will work with the structure I have above.

Appreciate the help,
Darren

 

[Joan Wild]

I'm having a problem linking a subform that sits on an unbound form (that
is also a subform), with the primary key field in the main form to allow
synchronizing of the records. I will try to illustrate the structure
below:

Contacts - Main form bound to "Contacts" table with ContactID as key
|
|___frmEstimate - bound by "SELECT DISTINCTROW Contacts.* FROM Contacts
WHERE ((Contacts.ContactID=forms!Contacts!ContactID));"
|
|___subfrmEstimateSummary - unbound
|
|___subfrmConsummables - bound to "tblConsumables"

There are 3 levels of subforms: frmEstimate, subfrmEstimateSummary,
subfrmConsummables. The tblConsummables is related to Contacts via
ContactID as one-to-many.

Then why is the sbufrmConsummables shown as a subform of
sbfrmEstimateSummary, rather than a subform of frmEstimate? i.e. have
adjacent subforms instead. Then the link master/child can be ContactID.

 

[Darren Kozey via AccessMonster.com]

Hi Joan...thanks for the reply.

Sorry, I should have been more succinct.

The subform called frmEstimate is a tabbed form, and on one of the tab
pages sits the unbound subfrmEstimateSummary. This form is used to display
a number of sumary fields that are from other tabs in frmEstimate. This is
why it is unbound, as it is simply a holding place for my text control
boxes that are reflective of calculated text boxes of each of the other
subforms on each tab page of frmEstimate. On subfrmEstimateSummary, there
sits the subfrmConsummables subform.

I am still a newbie I suppose, so there are probably better ways to do
this, but I have been struggling a little. The controls in the
subfrmConsummables could have been placed directly on the
subfrmEstimateSummary form, but I could not figure out how to link it that
way either. I thought I would have better luck if a subform was created for
it.

Hope this makes some sense, and of course your help is much appreciated.

Thanks,
Darren

 

[Joan Wild]

The subform called frmEstimate is a tabbed form, and on one of the tab
pages sits the unbound subfrmEstimateSummary. This form is used to display
a number of sumary fields that are from other tabs in frmEstimate.

But is this a subform, or is this a tab that has these textboxes on it? The
fact that things are sitting on tabs is not relevant to getting the name
correct.

On subfrmEstimateSummary, there
sits the subfrmConsummables subform.

OK, if I understand things, you have a frmEstimate with a number of tabs on
it.
On one tab is a subform called subfrmEstimateSummary.
On this subform is another subform called subfrmConsummables

subfrmConsummables has ContactID, which is linked to the ContactID of
frmEstimate.

I wouldn't make subfrmConsummables a subform of subfrmEstimateSummary. It
should just be a subform on frmEstimate. Then the link Master/linkChild
would be ContactID. It also sounds as though subfrmEstimateSummary
shouldn't be a subform at all. Since it just contains some textboxes with
calculations, these can simply be textboxes on the tab (there doesn't need
to be a subform).

A handy reference for referring to controls and properties is
http://www.mvps.org/access/forms/frm0031.htm

 

[Darren Kozey via AccessMonster.com]

Hi Joan.

You're absolutely right. I really didn't need the EstimateSummary page/tab
to have a subform on it. I deleted it and put all my controls on the
Summary page of frmEstimate, and now all the linking works perfect.

Thanks!
Darren