subform loses link to underlying record

[Guest]

Access 2000. I have a complex form with ten subforms, some of which also
contain subforms. After working properly for a long time, one subform loses
its link to its underlying table. I have not been able to trap it. The only
thing that helps is to close the form and reopen it. I have a suspicion that
it may be related to the maximum number of added controls to a form over its
lifetime. But, I have not been able to find any information regarding
determining if I have hit the limit and/or how to reset it.

 

[Allen Browne]

The limit is around 754 controls, and compacting should help reset it.

However, there are some other more likely causes.

Firstly, make sure Name AutoCorrect is turned off. Explanation:
http://members.iinet.net.au/~allenbrowne/bug-03.html

Seconly, is there anything that is changing the RecordSource property of any
form? If so, this can also affect the LinkMasterFields and LinkChildFields
property.

Finally, make sure you have the service patches applied, i.e. SP3 for Office
2000, and just as importantly SP8 for JET 4. Both are available from the
Downloads section at support.microsoft.com

 

[Guest]

Thanks for the help.

1. I do compact regularly (compact on close is checked), so that should not
be the cause. (Anyway, is there any documentation on this limit? How can I
see where it is up to?)

2. I had autocorrect partially off - while "perform" was off, "track" was
on. Is that enough to cause all that grief that you documented?

3. Yes. The main form has a where clause. I have a pop-up search box which
allows the user to select a manager. The code behind that selection executes
doCmd.Open of the main form with a where clause based on the selection. I
have a feeling that you have identified the source of the problem. (But why
would it affect only the tenth subform?) Is there anything that I can do
about it?

4. I have to check if the client has installed these service packs.

PS For some reason, I did not get an email indicating your response. Hence,
my second posting.

 

[Allen Browne]

Answers embedded.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

1. I do compact regularly (compact on close is checked), so that should
not
be the cause. (Anyway, is there any documentation on this limit? How can I
see where it is up to?)

From the main Access window, choose Help, and search for Specifications to
see the limits.

2. I had autocorrect partially off - while "perform" was off, "track" was
on. Is that enough to cause all that grief that you documented?

Just tracking Name AutoCorrect is enough to get Access confused.

3. Yes. The main form has a where clause. I have a pop-up search box which
allows the user to select a manager. The code behind that selection
executes
doCmd.Open of the main form with a where clause based on the selection. I
have a feeling that you have identified the source of the problem. (But
why
would it affect only the tenth subform?) Is there anything that I can do
about it?

I didn't follow that. Opening the main form again when it's already open
didn't compute for me.

 

[Guest]

Thanks again for the help.

Regarding the last item:
I wrote this a few years ago. But I think the flow is as I described. While
the main form is open, the user can click on a person search button. This
open two linked pop-up forms; in the top one the user can select a person and
when doing so, the bottom one displays the accounts related to that person.
This form gives me the value(s) of the key(s) I need to filter the main form.
At the time I found that what worked was using a doCmd.openform
"mainform",,,whereclause. This would reset the data source and display the
selected set of records. This has been working for a number of years.
My question is how to avoid losing the value of the linked field for the
10th subform.
Thanks.