Child/Master Links

[dee]

Hi,

I have a main form and a sub form. I can get things to work OK so far,
except:

The main form contains controls such as: MeetingID, FamilyID, FamilyName
combo box that is used to select a family name, then populate the FamilyID,
PhoneNumber, etc.

The sub form contains controls such as: MeetingID, FamilyID, ContactName,
ContactID, etc.

I can successfully link them via the MeetingID fields in the Child/Master.

I also, though, want to link them via the FamilyID field so that only the
contact in a certain family will show in the drop-down of the Contact combo
box in the sub form.

HELP!

 

[George Nicholson]

You are not limited to specifying a single field for Child/Master. Just
separate them with semi-colons:
MeetingID; FamilyID

HTH,

 

[dee]

Hi and thanks for your response.

I actually do have both MeetingID and FamilyID as child/masters. It linked
the meetingIDs with no problem; it doesn't,however, restrict the choices in
the drop-down of the combo box to just the contacts related to that specific
family.

I can't figure out why.

--
Thanks!

Dee

You are not limited to specifying a single field for Child/Master. Just
separate them with semi-colons:
MeetingID; FamilyID

HTH,

 

[Rick Brandt]

Hi and thanks for your response.

I actually do have both MeetingID and FamilyID as child/masters. It
linked the meetingIDs with no problem; it doesn't,however, restrict
the choices in the drop-down of the combo box to just the contacts
related to that specific family.

I can't figure out why.

Because Master/Child links have nothing to do with the RowSource for
ComboBoxes. You have to take care of that yourself.

Your ComboBox RowSource would need to be a query that references the family
value on your form and uses that as filter criteria. That is a completely
separate concept from the Master/Child links.

 

[dee]

Hi Rick,

Thanks for your response. I'm totally lost now, though.

My combo box (CboContactID) source is a query that concatenates the
ContactFirstName and ContactLast name into a singular ContactName. So, I
select the ContactID and ContactName fields from that query.

Then, the user selects the Contact name from the combo drop-down and the
Contact ID automatically fills in. However, the RoleID (the roles are
father, mother, etc.) does not.

Plus, the original problem of not restricting the combo to the FamilyID that
is indicated on the main form.

I appreciate your patience to someone who is quite new to this.

 

[Rick Brandt]

Hi Rick,

Thanks for your response. I'm totally lost now, though.

My combo box (CboContactID) source is a query that concatenates the
ContactFirstName and ContactLast name into a singular ContactName. So, I
select the ContactID and ContactName fields from that query.

Then, the user selects the Contact name from the combo drop-down and the
Contact ID automatically fills in. However, the RoleID (the roles are
father, mother, etc.) does not.

Plus, the original problem of not restricting the combo to the FamilyID that
is indicated on the main form.

I appreciate your patience to someone who is quite new to this.

You're talking about several concepts that really have nothing to do with each
other.

Concept 1) Master/Child Links
These properties cause a subform to display records that "match" with the record
in the parent form based on linking fields that are listed in those properties.

EXAMPLE: Orders form with a Line-Items subform. As you navigate from record to
record in the Orders form the subform automatically is filtered to show the
line-items for that particular order.

Concept 2) Synchronized ComboBoxes
A synchronized ComboBox has a RowSource query that "looks back" at a control on
the same form where the ComboBox resides and uses that value to filter the rows
shown in the ComboBox. Often the other control that the ComboBox uses as
criteria is another ComboBox. This is described as synchronized ComboBoxes.

Example: In ComboBox_1 the user selects from a list of Categories and then in
ComboBox_2 you can choose from items contained in the category chosen in
ComboBox_1.

In order for ComboBox_2 to "react" to changes in ComboBox_1 you have to issue a
Requery on the second ComboBox any time the value in ComboBox_1 changes. The
AfterUpdate event is typically used for this.

Concept 3) Grabbing multiple values from a ComboBox
Normally a ComboBox only holds one value even if it contains several columns of
data when "dropped". You can display or save additional columns by using
expressions that pull the data from the other columns...

=ComboBoxName.Column(n) (n being zero-based)

....or by using code to retrieve one of these values and "push" it into another
control on the form...

Me.TextBoxName = Me.ComboBoxName.Column(n)

 

[dee]

Hi Rick,

First of all, thank you for taking the time to answer my question in such a
detailed manner.

I have used the Child/Master links successfully in other forms, but it
doesn't seem to show me the contacts related to a particular family via
FamilyID matching FamilyID fields in this case and i can't figure out why.

The family ID controls in the main form and sub form match - in other words,
if I select a family from the combo box drop down on the form, it populates
the familyID in another control with the FamilyID PK of that FamilyInfo
table. It also shows that FamilyID in a control in the sub-form, but when I
then create a combo box to select a contact in that family, it still shows
ALL contacts in the database.

The synchronized combo boxes may be what I need on the sub-form. When the
FamilyID is automatically filled in, it will filter the Contact combo box to
show only the contact for that family.

Could you perhaps guide me in the right direction regarding the After Update
Requery?

Thanks again - I appreciate your time and your assistance.

 

[Tom Lake]

The family ID controls in the main form and sub form match - in other

words,
if I select a family from the combo box drop down on the form, it
populates
the familyID in another control with the FamilyID PK of that FamilyInfo
table. It also shows that FamilyID in a control in the sub-form, but when
I
then create a combo box to select a contact in that family, it still shows
ALL contacts in the database.

Are you sure the Combo box that's showing all records has a WHERE clause in
the record source
that limits it to the records where "[FamilyID]='" & [FamilyID] &"'"

Tom Lake

 

[Rick Brandt]

Hi Rick,

First of all, thank you for taking the time to answer my question in such a
detailed manner.

I have used the Child/Master links successfully in other forms, but it
doesn't seem to show me the contacts related to a particular family via
FamilyID matching FamilyID fields in this case and i can't figure out why.

The family ID controls in the main form and sub form match - in other words,
if I select a family from the combo box drop down on the form, it populates
the familyID in another control with the FamilyID PK of that FamilyInfo
table. It also shows that FamilyID in a control in the sub-form, but when I
then create a combo box to select a contact in that family, it still shows
ALL contacts in the database.

Post the SQL for your ComboBox RowSource. It should look something like...

SELECT Contact
FROM Contacts
WHERE FamilyID = Forms!NameOfForm!FamilyID