How? Form/subform with a listbox to choose child data to display.

[Ted]

I have the basic one-to-many relationship that I would like to display on a
form/subform using a listbox.

I would like a listbox to display all the child records on the subform
(basically only one key field). The user should be able to select the item
in the listbox and the other controls on the subform need to synchronize to
the correct child record.

I've tried the following query in the list box's row soruce to no avail:

SELECT ChildTable.ID, ChildTable.Title FROM ChildTable;

But it lists ALL the child records, and is not dependant upon the current
parent record in the main form. What I would like is only the child records
pertinent to the current parent record to show [in the list box] and all
subform fields to be blank if no child records exist.

I'm sure that this is a common scenario but I can't find any tutorials or
posts that are more explicit beyond a proper SQL statement.

- Do I need to use an SQL statement for both the subform AND the listbox?
- If the SQL statement returns all rows, how does the form know what to link
on?
- How can I filter to display only the child records for the parent
record displayed on the main form?

TIA

 

[Denny]

Hi

I made a quick DB with ChildTable and ParentTable. They
are One to Many from Parent to Child. ParentTable has 2
columns, ID (primary) and Title. Child has the same. I
made ChildTable.Title a Number and the foreign key to
ParentTable.

I created a Form whose RecordSource is ParentTable. I made
a Sub Form whose RecordSource is:

SELECT DISTINCTROW [ChildTable].[ID], [ChildTable].[Title]
FROM [ChildTable];

The Subform's Link Child Fields and Link Master Fields are
both set to ID.

Now, I right-clicked on the Parent's ID textbox, and
selected "Change-To" then "Listbox"

With the listbox, I deleted it's Control Source, I set Row
Source Type = Table/Query, I set Row Source = ChildTable,
I set the Bound Column = 1

This led to a form with a listbox on it, that included 3
records I entered. As I clicked on a record, the details
from the Child record would change in the Subform.

Hope this helps.

 

[Ted]

Denny,

Thanks for your suggestions, but I can't get it to work. Here are my
problems:

a) All the child records are being shown. If my parent record is "Homer
Simpson", I want the list box to only show the child table records "Bart"
and "Lisa", not all the child records. I can filter these out by changing
the list box's Row Source to "Select Child.ID from Child Where Child.Title =
ParentIDTextBox.Value". The problem here is that when I move to a different
Parent Record, the listbox does not update. Do I need a "requery"
somewhere?

b) Selecting different items in the list box doesn't change the data
displayed in my subform. I want to highlight "Bart" to see all the info on
Bart Simpson. Do I need a "requery" somewhere?
ID.

Your above statement seemed strange to me, so I tried setting the child link
fields to Child.Title, but that didn't make a difference either. But
shouldn't the "Link Child Fields" be the Foriegn Key?

I know Access is a powerful tool and that this must be possible (with some
filters and requieries?), but I can't figure out what is wrong.

I appreciate your help,
Ted.

Hi

I made a quick DB with ChildTable and ParentTable. They
are One to Many from Parent to Child. ParentTable has 2
columns, ID (primary) and Title. Child has the same. I
made ChildTable.Title a Number and the foreign key to
ParentTable.

I created a Form whose RecordSource is ParentTable. I made
a Sub Form whose RecordSource is:

SELECT DISTINCTROW [ChildTable].[ID], [ChildTable].[Title]
FROM [ChildTable];

The Subform's Link Child Fields and Link Master Fields are
both set to ID.

Now, I right-clicked on the Parent's ID textbox, and
selected "Change-To" then "Listbox"

With the listbox, I deleted it's Control Source, I set Row
Source Type = Table/Query, I set Row Source = ChildTable,
I set the Bound Column = 1

This led to a form with a listbox on it, that included 3
records I entered. As I clicked on a record, the details
from the Child record would change in the Subform.

Hope this helps.

-----Original Message-----
I have the basic one-to-many relationship that I would like to display on a
form/subform using a listbox.

I would like a listbox to display all the child records on the subform
(basically only one key field). The user should be able to select the item
in the listbox and the other controls on the subform need to synchronize to
the correct child record.

I've tried the following query in the list box's row soruce to no avail:

SELECT ChildTable.ID, ChildTable.Title FROM ChildTable;

But it lists ALL the child records, and is not dependant upon the current
parent record in the main form. What I would like is only the child records
pertinent to the current parent record to show [in the list box] and all
subform fields to be blank if no child records exist.

I'm sure that this is a common scenario but I can't find any tutorials or
posts that are more explicit beyond a proper SQL statement.

- Do I need to use an SQL statement for both the subform AND the listbox?
- If the SQL statement returns all rows, how does the form know what to link
on?
- How can I filter to display only the child records for the parent
record displayed on the main form?

TIA


.