Navigation Buttons on Main Form Not Pulling Records Completely

[seeya]

Hi,

I'm having trouble with the navigation buttons on a MainForm. I need it navigate records in the MainForm, but it keeps pulling the SubForm details. Here's an example:

Relationship: 1 record can have many subset data (= 1 Author can be tag to many Books)

On MainForm, I've 3 records:
Author ID 001


Author ID 002
Author ID 003

On SubForm, I've the following subset data:
Author ID 001

• Book A
• Book B

Author ID 002

• Book C

Author ID 003

• (Blank)

On MainForm, when Record = Author ID 001, the SubForm correctly shows 2 records (Book A and Book B) and SubForm navigation works fine.

However, on MainForm when I go to next record, instead of showing Author ID 002, it still shows Author ID 001 and Book B. It went from Author ID 001 and Book A to Author ID 001 and Book B.

How do I make the navigation button (next record) on MainForm scroll between records on MainForm and not the SubForm?

[aytee111]

The subform must be linked to the main form using Author, then it will display the records associated with that author. Are you saying that the main form is not scrolling thru the records correctly? How are you going from one record to the next?

[seeya]

Hi aytee111, thanks for your help. Yes I've created a relationship (1 Author : Many Books). I would like the MainForm to scroll through Authors and not Books, which it is currently stuck at. The SubForm is correctly scrolling through Books. The MainForm can't scroll to Author ID 003 because there's no Book tagged to it but I would like it to show Author ID 003 on MainForm and SubForm is blank.

[aytee111]

What do you mean by "created a relationship"? Is that the master link? What is being shown on the main form has nothing to do with the subform, records will show regardless of what is on the subform. The reverse is true, records on the subform will show only what is linked to the main form.

[seeya]

Hi aytee111,
Yes I've linked the Activity ID on both MainForm and SubForm.
What is shown on the MainForm shouldn't be impacted by SubForm, however, that's not the case I'm seeing - meaning Activity ID 003 doesn't appear on my MainForm because there's no Books tag to it.

What do you mean by "created a relationship"? Is that the master link? What is being shown on the main form has nothing to do with the subform, records will show regardless of what is on the subform. The reverse is true, records on the subform will show only what is linked to the main form.

[aytee111]

Activity ID 003 doesn't appear on my MainForm because there's no Books tag to it

Repeat - this is not the case. The record source of your main form decides which records to show, nothing else. Maybe you have a query that says don't show the author unless it has records on the books table as the record source for your main form.

[ssanfu]

PMFJI, but it sounds like the mainform record source is a query that has two tables: the authors and the books.

The mainform record source should be a query only on "tblAuthors". (I always use queries for form record sources)

So the mainform query would be something like:
qryAuthors
-------------
SELECT AuthorID_PK, AuthorFirstName, AuthorLastName FROM tblAuthors ORDER BY AuthorID_PK



The subform would have a record source of:
qryBooks
--------------
SELECT BookID_PK, AuthorID_FK, BookName FROM tblBooks ORDER By BookName

Note that the PK and FK fields should be on the forms, but hidden (not visible)!


In form design view, the linking fields between the main form and the sub form would be AuthorID_PK and AuthorID_FK.
Set the main form and the sub form to be in continuous form view. Access will complain, but make sure that both forms are in continuous form view.


Switch to normal view. ALL authors will be displayed. Clicking on each author will show the related books in the sub form...

[seeya]

Thanks, works great!