Hi Paul,
You must be using Access 2003, because I am not familiar with some of the
things that you're describing here...
I'm also assuming that hopefully each of these subforms are independant (one
main form with 3 independant subform controls) and that each subform is
based on it's own table, correct?
Based on what I think you are saying when you say "<100 records for each
parent"...
Is the LinkMaster property for all subforms solely dependant on the
SalesForceID field on the main form?
If so, I think that may be causing you some grief.
IMHO, you need to have "cascading" subform dependencies in place.
In order to explain, let me "rename" your forms (and likely your tables
also) in the interest of clarity.
Main form: "frmSalesForce" based on tblSalesForce
Subforms:
"sbfDistrict" based on "tblDistrict"
"sbfTerritory" based on "tblTerritory"
"sbfSalesRep" based on "tblSalesRep"
1.) Create 2 new unbound textboxes on the main form.
2.) Name them "txtDistrictID" and "txtTerritoryID"
3.) Use "=Me![sbfDistrict].Form![DistrictID]" and
"=Me![sbfTerritory].Form![TerritoryID]" , respectively, as the Control
Source for these text-boxes.
4.) I'd leave the .Visible property for both textboxes as "True" for now,
but they can be set "False" later, if you like.
5.) Now set the Link Master / Link Child thusly:
sbfDistrict: SalesForceID / SalesForceID
sbfTerritory: txtDistrictID / DistrictID
sbfSalesRep: txtTerritoryID / TerritoryID
Now navigating thru mainform records (or subform records in the first 2
subforms for that matter) will cause cascading refreshes of the related
records in the dependant subforms.
--
HTH,
Don
=============================
Use
My*****@Telus.Net for e-mail
Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)
I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.
Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop
================================
"Paul Malcomson" <pm**********@hotmail.com> wrote in message
news:23**************************@posting.google.c om...
Hi.
I'm having terrible trouble with a form that displays several
parent/child relationships at one time. It is a sales force hierarchy
- Sales force, district, territory, sales rep are the tables linked in
parent/child relationships.
I'm using a form to choose the main-parent and then several subforms
in datasheet view.
Sales force (20 records)
|==>District(subform) (<15 records for each parent)
|==>Territory(subform) (< 50 records for each parent)
|==>Sales Rep(subform) (<100 records for each parent)
Because all the subforms are in datasheet view, I have the (+) expand
and (-) collapse buttons at the left of each record. If I expand
several of the children (at any level) and start navigating through
the Sales Force, then the district subform still has any expanded
child records still expanded (territories).
If I continue to navigate through the sales forces with any
Territories expanded, I eventually get an error "You can't carry out
this action at this time". This disables any functions from working
and I have to kill MS Access to get out of it.
I've searched for "You can't carry out this action at this time" but
have not found anything promising.
What I am looking into is how to call the Subdatasheet => collapse all
from the format menu when in Datasheet view. I can't seem to find
that either. It's not available from the runcommand and what you
would think the "DoCmd.DoMenuItem acFormBar, 4, 9, 1" doesn't work
either. The 4,9,1 are the values from counting down on the format
menu (format = 4, Subdatasheet = 9, and Collapse all = 1)
Thanks in advance for any help.
Paul
