From: Datasmith on
I recently started converting an app to Access 2007. I have a main form with
lots of subforms. Both the main form and subforms are detached and
reattached to various recordsource queries as needed. After installing
Access 2007, even before converting the applicaion from 2003, the subforms
began exhibiting strange behaviour when the recordsource was changed. I
upgraded the app to 2007 and still had the problem. Even though the
underlying query was showing several hundred records, only one was showing.
Cut to the chase...

What I found was that when programmatically assigning the subform
recordsource it was also assigning values to the LinkMasterFields and
LinkChildFields properties, even though none existed to start. Now every
time I need to reassign the recordsource I also add two statements to clear
the relational linkage:

Forms!frmMain!fsubTemp.Form.Recordsource = "MyQuery"
Forms!frmMain!fsubTemp.LinkChildFields = ""
Forms!frmMain!fsubTemp.LinkMasterFields = ""

Seems to have solved this problem. Hope this helps you too.

Bruce


"Charles Tam" wrote:

> Thanks for suggestion, I've tried it. Unfortunately, it doesn't solve the
> problem, because the subform is already in the openning state.
>
> What we need is a solution such as the subform wouldn't be triggered to
> reopen when its parent form's recordsource has changed. FYI: Access 2003
> implemented this behaviour.
>
> "Marshall Barton" wrote:
> >
> > Instead of canceling the open event, try using Exit Sub
> >
> > --
> > Marsh
> > MVP [MS Access]
> >
From: Marshall Barton on
Datasmith wrote:
>I recently started converting an app to Access 2007. I have a main form with
>lots of subforms. Both the main form and subforms are detached and
>reattached to various recordsource queries as needed. After installing
>Access 2007, even before converting the applicaion from 2003, the subforms
>began exhibiting strange behaviour when the recordsource was changed. I
>upgraded the app to 2007 and still had the problem. Even though the
>underlying query was showing several hundred records, only one was showing.
>Cut to the chase...
>
>What I found was that when programmatically assigning the subform
>recordsource it was also assigning values to the LinkMasterFields and
>LinkChildFields properties, even though none existed to start. Now every
>time I need to reassign the recordsource I also add two statements to clear
>the relational linkage:
>
>Forms!frmMain!fsubTemp.Form.Recordsource = "MyQuery"
>Forms!frmMain!fsubTemp.LinkChildFields = ""
>Forms!frmMain!fsubTemp.LinkMasterFields = ""
>
>Seems to have solved this problem. Hope this helps you too.

Before doing that, see if clearing the Tools - Options -
Tables/Queries - Auto Index ... entry makes the problem go
away

--
Marsh
MVP [MS Access]