use of subform

[Bruce Roberson]

I have a form that uses a subform to unite two tables where there is a one
to many relationship between the parent and child table if that is the right
term to use.

The subform appears to be linked propertly according to the three matching
keys I set up. If I click the record count arrow for the sub form it goes to
the next record as it should. However, if I click that arrow key while
giving focus to a field on the parent, what I am thinking is that it should
increment the record of the parent table not the child table. Does that make
sense? Example below:

Parent table, with five records in the database (just for illustration)
Child table, with many matching records for each of the five parent table
records

Records one and two of the parent are very similar. The linking I set up for
this form has three fields on both the parent and child that must match for
there to be a joined record. Record 1 and 2 in the parent table differ by
only one field in this case.

What seems to be happening as I scroll the records by clicking the arrow
keys for the parent fields is that record 1 shows first with its matching
child records in the subform. Then if I go to record 2, it also shows its
respective child records as it should. But what happens next if I click the
arrow key again is that the parent information for the next record matches
that of the first parent record because it is scrolling the entire set of
records. Basically the first two records had unique child records but the
third parent record is showing the same child records I had on the first
parent record because it is scanning the entire set.

If I understand what a subform is for, the detail should skip ahead to match
the parent, not the other way around. If I go to record 3 in the parent
table, then I should see the detail match for the third parent record not
the third record in the set which could be the 3rd record in a joined table
of over 1000 records.

I hope this description makes sense so you can make me understand how to
change this record scrolling behavior if possible in this situation.

Thanks,


Bruce

 

[Mike]

That is one long desc so I not sure I understand your
true question.

But, a subform has it's own record selectors and counts.
So does the main form (in this case you master records).
If you getting incorrect counts it's because

1) You don't understand record selectors OR
2) You foreign key is not set up correctly

 

[Bruce Roberson]

Well I think understand the record selectors just fine. There is one in the
subform for the subform records, and one at the bottom for the form to move
from record to record, right?

I think my key is set up, but I wish it had more than three linking fields
available to link parent and child tables, or at least an ability to combine
fields in expressions somehow.

The problem is that the form does not work effciently unless I can move
amongst the parent records (5 in my example) and see a new set of child
records in the subform each time I click the record selector for the form to
move forward one record.

I am trying to be open minded about Access, but very basic things like this
must work or have a workaround if I am going to be able to make use of this
product in my work.

Thanks,


Bruce