Link Subforms to Main Form - 2 Linked Combo Boxes in Main Form

[John D]

My db has 5000 nonprofits. I have a Main Form [frmOrgMaster] with 6 subforms,
each on a tab. [frmOrgMaster] has 2 Combo Boxes. You select a County in the
first Combo Box [cboCounties]. The [cboCounties] AfterUpdate Event Procedure
then requeries the second Combo Box [cboOrgs] which produces a list of
nonprofits in that County - and you choose one of them.

The idea is to go through the two Combo Boxes, then view info about the
selected nonprofit on the 6 tabs - such as Key People, Locations, Services,
etc.

Here's how the Combo Boxes work -

I created a Query named qrycboOrgs that joins two Tables - tblOrgs and
tblCounties - and has these fields:

tblOrgs.OrgID
tblOrgs.OrgName
tblOrgs.CountyNum
tblCounties.CountyName

The two tables are joined on their common field - CountyNum(ber).

The two Combo Boxes have these Row Source properties:
cboCounties:
Row Source Type: Table/Query
Row Source:
SELECT DISTINCT qrycboOrgs.CountyName FROM qrycboOrgs;

cboOrgs:
Row Source Type: Table/Query
Row Source:
SELECT qrycboOrgs.OrgID, qrycboOrgs.OrgName
FROM qrycboOrgs
WHERE ((qrycboOrgs.CountyName) Like forms!OrgMaster!cboCounties)
ORDER BY qrycboOrgs.OrgName;

One subform is [frmsubOrgCorp] which is about corporate structure. It's data
comes from a query [qryCorpStatus] that has, among other fields - [OrgID] -
which is the the Link field. (The other 5 subforms have different queries but
all use OrgID as the Link field.)

Link Child and Master Fields for [fromsubOrgCorp] are set to [OrgID] - from
qrycboOrgs for the Master, and qryCorpStatus for the Child. However, after
the 2 Combo Boxes are selected, the subforms do not update to show info about
the nonprofit selected in cboOrgs (Combo Box 2).

The problem is that the value of OrgID in qrycboOrgs itself doesn't change
when the user selects the nonprofit in cboOrgs (Combo 2).

I think I should be linking with the OrgID value selected in Combo2, but
can't figure it out.

Can you tell me what I should do - thanks.

John D

 

[tina]

make sure that the BoundColumn property in the combo box control is 1, and
make sure that the first field in the RowSource is OrgID. then set the
subform's LinkMasterFields property is set to the *name of the combo box
control*.

hth

 

[John D]

Oh thank you, thank you, thank you - did I say "thank you" yet?

You know, I spent 4 hours looking at Access Help, OnLine Help, querying the
Discussion Groups, looking through Access 2003 In and Out, VBA for Access
2003 for Dummies, and no where did I see the simple direction of simply
putting the Combo Box control name in the LinkMasterFields property.

Thank you, thank you, thank you.

John D

 

[tina]

well, there's a reference to it in the "LinkChildFields, LinkMasterFields
Properties" topic in Access Help, but you have to be paying attention to
catch it.

"You can use the name of a control (including the name of a calculated
control) to set the LinkMasterFields property..."

you can open the main form in Design view, click on the subform control to
select it, then open the Properties box. then click in either Link...Fields
property and press F1. it takes you right to the topic in Access Help. (this
little trick works in a lot of places in form/report design view, as well as
in the VBE window). suggest you read up on the topic, it's really helpful in
mastering subforms.

and btw, you're very welcome.

hth