multiple subforms problem

[Diana Gard]

Perhaps this is a design flaw, please let me know.

I'm using Access 2000. I have a form with a tab control and 5
subforms within those tabs. The forms match with the tables: Client
main, Intake, Financial, Fees, Payments, Bills, Bill Line Items. The
design works thusly - Client Main relates to Intake. Intake relates to
financial. Financial relates Fees, Payments and Bills. Bills relates
to Bill Line Items.

On the form, I use the standard child and master links for Client Main
- which is the table the form is set for - and Intake. To link
financial to intake, I use the intake ID on the financial table as the
child and then I use the intake ID field on the intake subform as the
master. This is the methodology I use for all the subsequent subforms
- I relate them to each other. The syntax I use for the master link
is: [subfrmIntake-Discharge].form![IntakeID] (this is for the intake
to financial connection).

The Bills and Bill Line Items both appear on the same tab page, so I
use the onCurrent event for bills to requery the bill line items form.
That works just fine.

This all works just fine as long as there is no more than one intake
record. When there is more than one, I run into all kinds of
problems. First, I can't use the onCurrent to update the financial
form IF I then also use the onCurrent in the Financial form to update
fees, payments and bills - there is recursion and I have to end the
program. I've tried using onCurrent from Intake and just requery all
the subsequent forms, but that causes recursion as well.

I looked this up and found there was a bug that Microsoft is aware of
regarding this recursion issue when using onCurrent. So...I tried to
use the onChange event on the tab control so the forms would requery
when I clicked on the specified tab. This works for the Financial
form, but the bills and fees and payments don't seem to work. When I
set up a double-click button just to try to manually requery these
final subforms, I've found that it works, but it sends the Intake form
back to the first record. Yikes!

Here's what I'm thinking - there's something I'm missing in how I've
set up the fees, bills and payments forms that is different from how I
set up the financial form that creates a link in the wrong direction
to the intake? I've double-checked the master and child links and
they are correct. - OR this whole concept is just not doable and I
need to come up with a new way to show all this information for a
client.

I know this is wordy, but I wanted to be as clear as possible. Thanks
for any help you folks can pass my way.

Diana

[Allen Browne]

Hi Diana

What you are doing makes sense, but I think it is too much in one form. It
is very easy to trigger this endless loop issue.

You may find it helpful to put a text box on the main form, with
ControlSource of:
=[subfrmIntake-Discharge].form![IntakeID]
and nominate that text box in LinkMasterFields.

In case this database is ever updated, make sure you put text boxes in the
subform for the foreign key fields named in LinkChildFields; otherwise A2003
will crash (and I think A2002 as well).

Another workaround is to use nothing in the
LinkMasterFields/LinkChildFields, and just use the Current event of the form
to assign the RecordSource to the subform. Unfortunately, this triggers
another flaw in Access, where it decides to spontaneously assign the
LinkMasterFields/LinkChildFields, so you need to clear these properties
again after assigning the RecordSource. Provided you explicitly clear the
LinkMasterFields/LinkChildFields, I would expect that you could then call
the Current event of the lower-level subform without triggering the endless
loop.

If any of the subforms use Conditional Formatting, flaws in how that is
implemented can also trigger another endless calc loop, so you will want to
remove any conditional formatting until you get the rest of the design
working.

That's a fairly incomplete answer to your question. You seem to have a good
handle on what's going on, so I hope this gives you a few useful leads to
pulling apart the interacting ideas that affect your issue.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Diana Gard" <di*******@yahoo.com> wrote in message
news:1a**************************@posting.google.c om...

Perhaps this is a design flaw, please let me know.

I'm using Access 2000. I have a form with a tab control and 5
subforms within those tabs. The forms match with the tables: Client
main, Intake, Financial, Fees, Payments, Bills, Bill Line Items. The
design works thusly - Client Main relates to Intake. Intake relates to
financial. Financial relates Fees, Payments and Bills. Bills relates
to Bill Line Items.

On the form, I use the standard child and master links for Client Main
- which is the table the form is set for - and Intake. To link
financial to intake, I use the intake ID on the financial table as the
child and then I use the intake ID field on the intake subform as the
master. This is the methodology I use for all the subsequent subforms
- I relate them to each other. The syntax I use for the master link
is: [subfrmIntake-Discharge].form![IntakeID] (this is for the intake
to financial connection).

The Bills and Bill Line Items both appear on the same tab page, so I
use the onCurrent event for bills to requery the bill line items form.
That works just fine.

This all works just fine as long as there is no more than one intake
record. When there is more than one, I run into all kinds of
problems. First, I can't use the onCurrent to update the financial
form IF I then also use the onCurrent in the Financial form to update
fees, payments and bills - there is recursion and I have to end the
program. I've tried using onCurrent from Intake and just requery all
the subsequent forms, but that causes recursion as well.

I looked this up and found there was a bug that Microsoft is aware of
regarding this recursion issue when using onCurrent. So...I tried to
use the onChange event on the tab control so the forms would requery
when I clicked on the specified tab. This works for the Financial
form, but the bills and fees and payments don't seem to work. When I
set up a double-click button just to try to manually requery these
final subforms, I've found that it works, but it sends the Intake form
back to the first record. Yikes!

Here's what I'm thinking - there's something I'm missing in how I've
set up the fees, bills and payments forms that is different from how I
set up the financial form that creates a link in the wrong direction
to the intake? I've double-checked the master and child links and
they are correct. - OR this whole concept is just not doable and I
need to come up with a new way to show all this information for a
client.

I know this is wordy, but I wanted to be as clear as possible. Thanks
for any help you folks can pass my way.

Diana

[j.mandala]

I'm not sure if this would work , but I was able to load several
subforms on tab forms in these ways. Though probably not good design,
I think these techniques would work and avoid the aforementioned bugs.

Technique 1:

Don't use any direct linking between the subform and the master form,
instead, base each subform on a query that uses the prior form field
as a parameter for the query. After you select a new record, requery
the next subform with the OnCurrent for the record or AfterUpdate for
the control.
eg:

make [subfrmIntake-Discharge].form![IntakeID] the parameter for the
query that underlies the financial. In the OnCurrent for intake put
something like

Forms!FinancialSubform.Form.requery (not sure if the syntax is
correct)

If you find yourself dumped back to the first record on the prior
form(s) do something like:

lngTempIntake = [subfrmIntake-Discharge].form![IntakeID]
Forms!FinancialSubform.Form.requery
DoCmd.GoToControl "[subfrmIntake-Discharge].form![IntakeID]" (not
sure if you need to refer to the 'Page' here or the subform)
DoCmd.FindRecord lngTempIntake

Technique 2:

Another possibility (even less elegant, but pretty simple,) would be
to store all the parameters for each unlinked subform's queries
invisibly on the form or on an invisible global form. Again don't use
any direct linking between the subform and the master form, instead,
base each subform on a query that uses a field on the global form as a
parameter for the query.

You would need an unbound record selector (combo box or list box) on
each subform. On the AfterUpdate for the record selector, reset the
value for the matching field on your global form to the record
selector's value with something like:

Forms!Global!globalIntake= Me!IntakeID

then requery the forms.

I'm curious to see how you resolve this. I hope this helps, though I
am no Access expert.

[Diana Gard]

Thanks so much for your suggestions J. and Allen. I found that using
the global field on the main form with the control source referring to
the field in the subform worked wonders. I didn't have to do any
requeries once I set those up, and found that using Access's linking
procedure handled everything. The only problem I was having after
that was because I was doing some recalculating of total fields on the
onCurrent events on some of the subforms. I got rid of these and
everything was hunky dory. It made a huge difference just to know I
was on the right track - and then the suggestions finished it.
Thankyou thankyou thankyou!

So, for anyone reading - You can use numerous subforms - even subsub
forms on a main form - you just need to move the master link field to
the main form.

Diana

ma*****@rci.rutgers.edu (j.mandala) wrote in message news:<6c**************************@posting.google. com>...

I'm not sure if this would work , but I was able to load several
subforms on tab forms in these ways. Though probably not good design,
I think these techniques would work and avoid the aforementioned bugs.

Technique 1:

Don't use any direct linking between the subform and the master form,
instead, base each subform on a query that uses the prior form field
as a parameter for the query. After you select a new record, requery
the next subform with the OnCurrent for the record or AfterUpdate for
the control.
eg:

make [subfrmIntake-Discharge].form![IntakeID] the parameter for the
query that underlies the financial. In the OnCurrent for intake put
something like

Forms!FinancialSubform.Form.requery (not sure if the syntax is
correct)

If you find yourself dumped back to the first record on the prior
form(s) do something like:

lngTempIntake = [subfrmIntake-Discharge].form![IntakeID]
Forms!FinancialSubform.Form.requery
DoCmd.GoToControl "[subfrmIntake-Discharge].form![IntakeID]" (not
sure if you need to refer to the 'Page' here or the subform)
DoCmd.FindRecord lngTempIntake

Technique 2:

Another possibility (even less elegant, but pretty simple,) would be
to store all the parameters for each unlinked subform's queries
invisibly on the form or on an invisible global form. Again don't use
any direct linking between the subform and the master form, instead,
base each subform on a query that uses a field on the global form as a
parameter for the query.

You would need an unbound record selector (combo box or list box) on
each subform. On the AfterUpdate for the record selector, reset the
value for the matching field on your global form to the record
selector's value with something like:

Forms!Global!globalIntake= Me!IntakeID

then requery the forms.

I'm curious to see how you resolve this. I hope this helps, though I
am no Access expert.