Move between records on a form (skipping subform entries)

[Guest]

Is there a way to step through specific records on a form, instead of every
record, when a subform is attached (a one-to-many relationship)?

I have a form (A) and a subform (B). Form/table A contains personal
information and subform/table B contains the vehicles that the individual
owns. The tables have a relationship of one-to-many (form/table A has one
value that links to multiple values in form/table B). I have created a form
that shows the table A "one" information at the top of the screen and the
table/subform B "many" information below it. I would like to find a way to
navigate through the "one" records, so that when I click on a navigation
button (or any coded command button) the form jumps to the next individual,
not the next record, which may be the second vehicle the same individual
owns.

OR...is there a way to make the individual's last name field a drop-down
list, so that when you select a specific individual's last name, the form and
subform populate with only that person's information?

Thank you so much for any help you can offer,
Sue

 

[Guest]

Sue:

The behaviour you want is already normal for a form containing a subform.
The parent form should be based on the referenced (one-side) table and the
subform on the referencing (many-side) table, or on sorted queries in each
case to control the order in which the records are displayed. In neither
case do you need to join the tables, just use one for each. The
LinkMasterFields and LinkChildFields properties of the subform control would
be the primary and foreign key columns from the respective tables, e.g.
OwnerID.

If you use the built in navigation buttons on the parent form the form
should move to the next owner record and the subform display their vehicles.
The same will apply if you use your own custom buttons for navigating.

If you are using a continuous form or datasheet view subform I would
normally do without any navigation buttons in the subform by setting the
subform's NavigationButtons property to False (No), relying on scrolling to
move between rows in the subform. This avoids any confusion between the
navigation buttons for the parent and subform. If you do leave it with
navigation buttons, however, these will navigate through the subform's
records.

As regards your second question don't try and use a bound control for
navigating. If you want a combo box for navigating to a selected owner use
an additional unbound combo box, cboFindOwner say, with a RowSource property
like this:

SELECT OwnerID, FirstName & " " & LastName AS FullName FROM Owners ORDER BY
LastName, FirstName;

BoundColumn 1
ColumnCount 2
ColumnWidths 0cm;8cm (or rough equivalent in inches but the first
dimension must be zero to hide the first, OwnerID, column, so only the name
shows.

Put code along these lines in its AfterUpdate event procedure:

Dim rst As Object

Set rst = Me.Recordset.Clone

rst.FindFirst "OwnerID = " & cboFindOwner
Me.Bookmark = rst.Bookmark

This assumes OwnerID is a number data type. If it were text you'd wrap the
value in quotes characters:

rst.FindFirst "OwnerID = """ & cboFindOwner & """"

To keep the combo box in sync with the form's current record if you navigate
via the navigation buttons or keyboard put the following code in the form's
Current event procedure:

Me.cboFindOwner = Me.OwnerID

Ken Sheridan
Stafford, England

 

[Guest]

Thank you for the response, but unfortunately I'm still in a bind (just so
you know, I decided not to go with the drop-down list idea). There are
navigation buttons on both the parent form and the subform, but no matter
which set of buttons I use, it goes through each record. I have 100
individuals with 2 cars each. The parent form navigation buttons show a
total of 200 records and each subform shows a total of 2 records. I tried
clicking the subform buttons and it stopped at 2, but when I clicked the
parent form button, it reverted to record 2, which was the second vehicle for
the first person.

Also, I have the parent form's Default View set to Single Form and the
subform's Default View set to Continuous Forms. I tried changing the
subform's navigation button's property to False (no), but that didn't
help...it still goes through each of the 200 records individually.

Thanks,
Sue

 

[Guest]

Sue:

It sounds very much like the parent form is based on a query which joins the
owners and cars tables. It should be based solely on the owners table, or on
a sorted query which uses that table, or possibly o a query which joins the
owners table to one or more 'look-up' tables such as a table of cities. What
it should not do is join it to the cars table; the cars table, or a sorted
query based on it should be the RecordSource of the subform.

Its theoretically possible that the parent form is based on a query which
joins the owners table to some other table than cars, which has two rows per
owner, but the fact that the parent form includes exactly 200 records
suggests to me that the cars table is incorrectly included in the parent
form's underlying RecordSource.

The fact that its in single form view (which it has to be to contain a
subform) or that both sets of navigation buttons are showing makes no
difference; I'm sure it’s the underlying RecordSource which is the problem.

Ken Sheridan
Stafford, England

 

[Guest]

Thank you for the response, but now I'm confused. When I open the form, I
want to see the individual and in the subform, see the cars they have
registered. I thought I had to create a query that linked the two tables and
their information, to pull into the form and subform. Are you saying that I
can simply just use the personnel table as the source for the parent form and
use the vehicle table as the source for the subform? I guess I never thought
of that...I'm making it too difficult

Thanks again,
Sue

 

[Guest]

Sue:

Yep. that's exactly what you need to do. The LinkMasterFields and
LinkChildFields properties of the subform control will ensure that you see
the matching rows from the cars table in the subform without you needing to
include it in the paren’t form's RecordSource. It would be even better,
however, to base the parent form on a query which sorts the rows from the
Personnel table, e.g. by LastName, e.g.

SELECT *
FROM Personnel
ORDER BY LastName;

That way the parent form's records will be arranged alphabetically by name.
If you simply open your existing query in design view, remove the cars table
from it and sort the query by the LastName column, or whatever you want it
sorted by, then you should not have to make any changes to the form for it to
work correctly.

Incidentally if you were designing a report to do the same thing you could
use a parent report and subreport in the same way as the form, but it would
in fact be better to use the query as you originally had it as the report's
RecordSource. In the report you could then group it on each person and put
all the data about the people in the group header, and the data about the
cars in the detail section. That way you'd see each person's data once and
below that all the cars registered to them, without any need for a subreport.

Ken Sheridan
Stafford, England

 

[Guest]

You're AWESOME! Thanks for showing me the correct (and simpler) way to pull
the form and subform information together.