How to get Access to ignore subform records when browsing form

[Icebox]

I have an Access 2007 form that has a primary record in the main form that
draws data from three separate tables. It's an asset database tracking photo
usage; each photo has a unique ID that's the main key. A fourth table, which
records uses of the photo, is on a subform, linked to the unique ID. This is
a 1 to many relationship. There are about 4200 unique photos in the
database, and about 8700 records in the table in the subform. I notice that
after adding the subform to the form, when I attempt to browse through
neighboring IDs, the table treats each subform record as a unique record, so
it can take six or seven tries before one can get to the next photo on the
db.

This is annoying because I often have to check records that I know are near
each other in the main table. Is there any way I can keep the subform in,
displaying the records that belong to each unique ID, but have movement go
from main record to record regardless of how many records are in the
subforum?

Hope this is clear. I'm relatively new to access, and trying to rebuild a
form I had in an old paradox database. Importing the tables went shockingly
smoothly (gave up on 2003 because it couldn't import memo fields correctly),
but I have to rebuilding the forms from scratch.

Thanks!
Mark

 

[John W. Vinson]

This is annoying because I often have to check records that I know are near
each other in the main table. Is there any way I can keep the subform in,
displaying the records that belong to each unique ID, but have movement go
from main record to record regardless of how many records are in the
subforum?

It should, if you have the form set up right! Are you perhaps mistakenly
basing the mainform on a Query joining the two tables? If so, don't; the
mainform should be based on the main table (only), and the subform on the
child table (only), using the unique ID as the master/child link field.

 

[Icebox]

It should, if you have the form set up right! Are you perhaps mistakenly
basing the mainform on a Query joining the two tables? If so, don't; the

I built the form from a blank form by dragging in fields from the main
tables. I created the subform with the control from the ribbon. I used the
relationship tool off the ribbon to establish the links.

Just to clarify, what I'm seeing is that the number of records in the main
form is the number in the subform; the number of records in the subform is
the number of those that correspond to that ID. When there are multiple
records in the subform for a given photo, then trying to use pg up and down
in any field on the main form takes as many button presses as records in the
subform to increment to a previous or subsequent record. I tried screwing
around with the join type, which didn't change anything. Is this just
inherent behavior in one-to-many joins?

 

[John W. Vinson]

I built the form from a blank form by dragging in fields from the main
tables. I created the subform with the control from the ribbon. I used the
relationship tool off the ribbon to establish the links.

That was the problem then. A2007 "Helpfully" created a two table query
containing all the fields from both tables. Check the Properties of the form,
and find the Recordsource property. My guess is that it is a query linking the
two tables. Change it to a query involving JUST the main table.

Just to clarify, what I'm seeing is that the number of records in the main
form is the number in the subform; the number of records in the subform is
the number of those that correspond to that ID. When there are multiple
records in the subform for a given photo, then trying to use pg up and down
in any field on the main form takes as many button presses as records in the
subform to increment to a previous or subsequent record. I tried screwing
around with the join type, which didn't change anything. Is this just
inherent behavior in one-to-many joins?

Yes, *IF* you make the mistake of basing the form on a one to many joined
query. So don't!

 

[Icebox]

That was the problem then. A2007 "Helpfully" created a two table query
containing all the fields from both tables. Check the Properties of the form,
and find the Recordsource property. My guess is that it is a query linking the
two tables. Change it to a query involving JUST the main table.

Excellent guess John, this worked. How "helpful" access was here. Wow.