Form SubForm Display Updates

[Peter van der Goes]

First, I'm not an Access developer, so this may be an obviously dumba**
question.

I have two tables (lets' call them Pop and Kid) linked in a one to many
relationship. The primary key of Pop is the foreign key in Kid.
If I use the tables directly with the Form Wizard, I can create a
form/subform that will display each row in Pop and the associated rows in
Kid. I can use the supplied navigation buttons at the bottom of the form to
navigate through the Pop rows, and the linked Kid rows are properly
displayed in the subform.
So far, so good.
Rather than ask the potential user to use the navigation buttons at the
bottom, I want to change the textbox in the main form that displays the Pop
primary key field to a combo box that will allow the user to select the
desired Pop key and automatically display the associated data from both Pop
and Kid. I did the textbox to combobox change and created a query to
populate the combobox.
Result: Selection from the combobox correctly updates the rows displayed in
the subform, but *does not* update the other fields from Pop in the main
form.

I can easily write a query to retrieve the desired data from the two tables,
but when I try to create a form from the query, the option to create a
form/subform does not appear.

Question (finally): Is there an easy way to make selection from the combobox
replicate the desired behavior observed when the navigation buttons are used
(updating both form and subform), or do I need to use a completely different
approach?

Thanks for reading.

 

[Bas Cost Budde]

What you did *edits* the field, but causes no *navigation*.

For navigation you'd best use an unbound combobox, with as its rowsource
the same dataset as the main form it's in. Then, from the Click event, I
usually call

docmd.gotorecord , , acGoto, yourCombobox.ListIndex +1

-> to create an unbound combobox (or any control), leave or make its
Controlsource property blank;
-> to have the same datasource as the form, see the Recordsource
property for the form, and copy that into the Rowsource for the combobox;
-> to create a click event, select the control in the design view of the
form, view the properties, Events tab, at OnClick choose [Event
Procedure], click the three-dot button;

 

[Peter van der Goes]

What you did *edits* the field, but causes no *navigation*.

For navigation you'd best use an unbound combobox, with as its rowsource
the same dataset as the main form it's in. Then, from the Click event, I
usually call

docmd.gotorecord , , acGoto, yourCombobox.ListIndex +1

-> to create an unbound combobox (or any control), leave or make its
Controlsource property blank;
-> to have the same datasource as the form, see the Recordsource
property for the form, and copy that into the Rowsource for the combobox;
-> to create a click event, select the control in the design view of the
form, view the properties, Events tab, at OnClick choose [Event
Procedure], click the three-dot button;

Thanks very much for the concise and effective solution!
As you are obviously very good at this, would you care to recommend a good
book on intermediate Access techniques?
Thanks again for your help.

 

[Bas Cost Budde]

Grinnik. Thanks for the compliment, but I am by and large self-taught so
you cannot 'lend my book' I am afraid.

Rebecca Riordan wrote a very nice book about relational database design.
Maybe try that.

Having to solve real issues has been a very good training for me. Keep
posting!