Subform doesn't respond to main form

[wardwebber]

I'm building a simple database (well, I thought it would be) to record personnel names and phone numbers. Each person could have multiple phone numbers (Home, Office, or Mobile). So to record these, I need a main form showing the first and last names of a person, and a subform showing the first/last names and their phone number(s) and type of number. So like this:

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)
PhoneNumberType ID (foreign key)
PhoneNumber
PersonID (foreign key)

PhoneNumberType
PhoneNumberType ID (master key)
PhoneNumberType Name (like Home, Office, Mobile)

ContactsAndPhon eNumbers
PhoneDataID (master key)
PersonID (foreign key)
PhoneNumberType ID (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:

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;

That SQL query was constructed by Access. How do I establish a connection between the forms so the subform changes along with the main form?

[zmbd]

Very well explained.
In the main form, you can either insert a subform control from the toolbox, or you can drag the subform from the Acesss Object Pane onto the main form. IN the properties you will set the related fields.

You might find that reading through the articles in our insights section dealing with filtering by form and the combobox helpful:
Form Filter and Cascasde:

• Bytes > Sitemap > Microsoft Access / VBA Insights
• 42.Cascaded Form Filtering
• 156.Cascading Combo/List Boxes
• 168.Example Filtering on a Form.

These can be a little heavy on the vba and a lot to read thru; however, fairly easy once you understand what's going on.

There is also this quick tutorial from MS:
Create a form that contains a subform (a one-to-many form)(v2007)

Ofcourse, if you still have questions on this then by all means post back.

[dsatino]

Well, you said you only populated the AllContacts and PhoneNumber tables.

But in your subform SQL statement you have inner joins to the other two tables which aren't populated. Change the joins so that the phone numbers are returned regardless if they have matching records in the other tables. (Also, you don't need to include AllContacts table in your subform query).