Some subforms going out of sync, others not

I'm having problems with the interaction of a filter on a form and the
records displayed in a subform. I'm wondering if anyone has heard of
this sort of a problem, and what a fix might be.

For the sake of describing my problem: I've got three tables:
tblAccounts, tblContracts, and tblLocations.

An account can be made up of one or more contracts and one or more
locations.

The field [AccountNumber] is common to all three tables.

Hypothetical data in this database:

Account 1 contains contract 1 and locations 1, 2, 3.
Account 2 contains contracts 2 and 3, and location 4.
Account 3 contains contract 4, and locations 5-10.

I have four forms: frmSearch, frmAccount, frmContracts, and
frmLocations.

frmAccount contains a tab control. frmContracts and frmLocations are
subforms, each appearing on different pages of the tab control.

frmSearch is simply a combo box containing all of the [AccountName]'s
from tblAccounts, with a "Go" button tat calls frmAccount with a
filter on AccountName

frmAccount displays one record at a time frmContracts and
frmLocations are set up for continuous display.

When I open frmAccount directly, everything appears as it should:

Account 1 on screen:
subformContract displays contract 1 on its tabbed page
subformLocation displays locations 1, 2, and 3 on its tabbed page

Account 2 on screen:
subformContract displays contracts 2 & 3 on its tabbed page
subformLocation displays location 4 on its tabbed page

Account 3 on screen:
subformContract displays contract 4 on its tabbed page
subformLocation displays locations 5-10 on its tabbed page


However, lets say that I invoke frmAccount from the Go button on
frmSearch. Let's also say that the filter invoked causes account 2
to be displayed:

Account 2 on screen:
subformContract displays contracts 2 & 3 on its tabbed page
subformLocation displays location 4 on its tabbed page

If I remove the filter, and then use the record navigation buttons to
move among the accounts, I get the following:

Account 1 on screen:
subformContract displays contracts 2 & 3 on its tabbed page
subformLocation displays locations 1, 2, and 3 on its tabbed page

Account 2 on screen:
subformContract displays contracts 2 & 3 on its tabbed page
subformLocation displays location 4 on its tabbed page

Account 3 on screen:
subformContract displays contracts 2 & 3 on its tabbed page
subformLocation displays locations 5-10 on its tabbed page


In other words, subformContract remains as if I were displaying
account 2, even when I'm looking at accounts 1 or 3. However,
subformLocation behaves as I want.

Anybody have an idea as to what causes this behavior?

Thanks.



--
Michael D. Adams -- Windsor, Connecticut
-- http://www.triskele.com

Michael D. Adams wrote:
: I'm having problems with the interaction of a filter on a form and the
: records displayed in a subform. I'm wondering if anyone has heard of
: this sort of a problem, and what a fix might be.

Some additional trial-and-error cleared up the problem. The problem (just
in case anyone combs through the msnews or Google Groups server with
similar problems in the future) appears to have been caused by some
code associated with an entry field on the problematic subform:

Private Sub Policy_Number_Exit(Cancel As Integer)
[Sequence] = Right([Policy Number], 5)
End Sub

Removing this code takes care of the problem.


--
Michael D. Adams -- Windsor, Connecticut
-- http://www.triskele.com