473,322 Members | 1,526 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Subform doesn't respond to main form

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)
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
  1. SELECT [AllContacts].[PersonID]
  2.    , [PhoneNumbers].[PhoneNumber]
  3.    , [ContactsAndPhoneNumbers].[PhoneDataID]
  4.    , [PhoneNumberType].[PhoneNumberTypeName] 
  5. FROM PhoneNumberType 
  6.    INNER JOIN (
  7.       (AllContacts 
  8.          INNER JOIN PhoneNumbers 
  9.             ON AllContacts.PersonID
  10.                =PhoneNumbers.PersonID) 
  11.       INNER JOIN ContactsAndPhoneNumbers 
  12.          ON AllContacts.PersonID
  13.             =ContactsAndPhoneNumbers.PersonID) 
  14.       ON PhoneNumberType.PhoneNumberTypeID
  15.          =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?
Jul 8 '13 #1
2 1242
zmbd
5,501 Expert Mod 4TB
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: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.
Jul 9 '13 #2
dsatino
393 256MB
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).
Jul 9 '13 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Bill Strass | last post by:
Problem: Access main form/subform as front end to SQL Server backend. Add/edit via subforms work fine. Not so with main form. Set up a master-detail form-subform using two views linked from SQL...
2
by: TDIOwa | last post by:
I have a form (frmAppointment) with a subform on it (frmAppointmentsub1). I have placed a command button on the main form which calls the function below from the module. The purpose of this...
2
by: prakashwadhwani | last post by:
I have a (Main) form named : Stocks_Main and a Subform in that named: Stocks_Data The Subform is a continuous form There is a field in the subform called QTY_IN Now ... on the main form i.e....
3
by: wideasleep | last post by:
Hi everyone, I have a subform that is a continuous form. It will have mutiple records that connect to a single record in the main form. I have set this up but running tests show only one record...
0
by: Richnep | last post by:
Hi all,. I have a small issue I would like to resolve with VBA. I have a form which it's fields are bound to a table. Within that form is a subform which is a subtable. Tables relationship...
1
by: ssr61 | last post by:
hi can u help me how to open a sub form with in a main form without opening a new window..... my main form (frm_searchby_Name)consists of text field to enter the address and button when...
2
reginaldmerritt
by: reginaldmerritt | last post by:
Not sure if this is even possible, but i want to select a field in a sub form from a main form. I want to populate a field in the main form with a sub forms field value. eg. me.name =...
1
by: Bob Alston | last post by:
I have a system where many subforms are used. Often the size of the subform had to be larger than could be displayed without scrolling. I set the height of the subform to the typical height...
2
by: ndeeley | last post by:
Hello, I've not had a lot of luck getting this to work, despite looking around lots of websites, so here goes... I have a main form frmProjectScheduler. It's a tabbed form and the front page is...
6
Fspinelli
by: Fspinelli | last post by:
I have a query by form set up. I coded and can get the fields on the form to clear by the click of a button, but how do I make the subform clear, too? The main form has a "Clear" button and in...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.