Main Form
Person ID: ________
First Name: _________
Last Name: __________
Subform
Person ID: ________
First Name: _________
Last Name: __________
Phone No. Type: _________
Phone No.: __________
To do this I've built four tables:
AllContacts
PersonID (master key)
FirstName
LastName
PhoneNumbers
PhoneNumberID (master key)
PhoneNumberTypeID (foreign key)
PhoneNumber
PersonID (foreign key)
PhoneNumberType
PhoneNumberTypeID (master key)
PhoneNumberTypeName (like Home, Office, Mobile)
ContactsAndPhoneNumbers
PhoneDataID (master key)
PersonID (foreign key)
PhoneNumberTypeID (foreign key)
PhoneNumberID (foreign key)
and set all relationships among them. I've populated the AllContacts and PhoneNumbers table with test data.
The problem is that the subform shows the field headings but no records, and remains blank when the main form record changes; i.e., when I change the person in the main form. It's as if there's no connection between the main form and the subform.
The Link Master Fields and Link Child Fields of the subform are both PersonID. The subform's Record Source is:
Expand|Select|Wrap|Line Numbers
- SELECT [AllContacts].[PersonID]
- , [PhoneNumbers].[PhoneNumber]
- , [ContactsAndPhoneNumbers].[PhoneDataID]
- , [PhoneNumberType].[PhoneNumberTypeName]
- FROM PhoneNumberType
- INNER JOIN (
- (AllContacts
- INNER JOIN PhoneNumbers
- ON AllContacts.PersonID
- =PhoneNumbers.PersonID)
- INNER JOIN ContactsAndPhoneNumbers
- ON AllContacts.PersonID
- =ContactsAndPhoneNumbers.PersonID)
- ON PhoneNumberType.PhoneNumberTypeID
- =ContactsAndPhoneNumbers.PhoneNumberTypeID;