how to change focus of "next record" button

[Andyatwork]

Hi all,

I'm an access newb (with about one week's experience) and learning as I go. I've been trying to create a form that will show a master record at the top (a named and numbered Business Procedure) that can be navigated through with the standard First/Previous/Next/Last Record buttons.


Beneath that I want a subform that displays a list of teams that are affected by the business procedure.

I've created "tblBusProc" with about 100 records in it, "tblTeam" with 8 records and "tblTeam to BP Junction" with about 250+ records, established the one-to-many relationships and got the sub form to display the teams associated with the procedure.
e.g., Bus Proc 1 affects teams 2, 3 and 7, Bus Proc 2 affects Teams 1, 2 and 3 etc

Before I added the subform, the Next Record button scrolled through the 100 odd Business Procedures in number sequence (not the Bus Proc Primary Key but a seperate 'Section Number' field (showing chapter and paragraph, e.g., 1.0, 1.1, 1.2, 2.0, 2.1 etc).

After I added the subform, the Next Record button is now focused on the junction table referenced by the subform and scrolls through those 250+ records in sequence.

How do I change the focus of the Next Record button back to the Section Number field of the Bus Proc table and not whatever order the junction table happens to be sorted into when the db is opened?

I've spent an afternoon and a morning searching the interwebs and this forum and not found an answer yet. Any answers or advice would be much appreciated.

Many thanks,

Andy

[Evilferret]

It sounds like you may have a problem with your data on the form / your SQL joins.

You should not be seeing all 250 records of the child item for each parent item (Unless every single child item is linked to the parent item 250 times in the tblTeam to BP Junction table)

For example, let's say your dataset looked like this:

tblBusProc
BusProcID
1
2
3
4

tblTeam
TeamID
1
2
3
4
5

tblTeam to BP Junction
teamID busProcID
1 1
1 3
2 2
3 3
3 4
4 1
4 5

Then when you're on the record with TeamID = 1, you should only be seeing BusProcID = 1 and BusProcID = 3 in your child table (Subform) but it sounds like in your situation you're seeing all of the subform records instead.

Is the data in your child form linked to the parent form in any way (Link master fields / link child fields attribute in the property sheet)? This may be your problem.

Datasource on the parent form should contain [tblTeam] and [tblTeam to BP Junction]. Then set the Link Master Fields property of the subform to [tblTeam to BP Junction.BusProcID] and set the Link Child Fields property of the subform to [tblBusProc.BusProcID]

Hope this helps

[Andyatwork]

Hello Evil, (cue lightning)

Thanks for replying.

I don't think I explained myself clearly.

The subform is behaving as I want it to, it is only showing the subset of teams linked to the displayed Business Procedure. e.g., for Bus Proc 1 it is correctly displaying teams 1, 2 and 7, for Bus Proc 2 it is showing teams 2, 3 and 7 etc. The problem is that I want the Next Record button to scroll through the 100 odd Business Procedures in their section number order, not the 250+ junction records.

You've lost me a little bit with;

Datasource on the parent form should contain [tblTeam] and [tblTeam to BP Junction]. Then set the Link Master Fields property of the subform to [tblTeam to BP Junction.BusProcID] and set the Link Child Fields property of the subform to [tblBusProc.BusProcID]

I'm not sure what you mean by Data source on the parent form? (sorry, very newb of me i'm sure). The parent form is only referencing [tblBusiness Procedure] i think.

I set up a [qryBP to Team Name] on [tblTeam to BP Junction] to report which teams tie up with which Bus Procs and the subform references this query.
I checked the property sheet for the subform and both Link Master and Link Child Fields show "Bus Proc ID" with the source object being [qryBP to Team Name].

Are you saying I can get the subform to display the related team names without having to use a query on the junction table?

Andy