How to navigate records without navigating subtable records

[Adam Tippelt]

Scenario:
I've got a table for adding records, with a subtable for all data requirements related to that record.

I've built a 'view' form to navigate through the records, so that you can view the details of each. This includes a listbox to display the list of data requirements attached to each record.

I've made navigation buttons using the control wizard and converted them to VBA to allow more flexibility so that I can add code to them.

Problem:

I want the form to navigate through the primary table records one by one, and just display the subtable records in the listbox. Currently it navigates through each individual subtable record in the listbox before moving on to the next primary record.

For example, say I have:

Record 1
- Data Requirement a
- Data Requirement b
Record 2
- Data Requirement c
- Data Requirement d
- Data Requirement e
Record 3
- Data Requirement f

When I navigate through the records, it goes:

Record 1a
Record 1b
Record 2c
Record 2d
Record 2e
Record 3f

But what I want it to do is navigate just the primary records, so that it goes:

Record 1
Record 2
Record 3

Even if I take the listbox out, the form is still navigating the sub records off screen, as it takes 2 clicks to get through the first primary record, then 3 clicks to get through the second primary record.

Is it possible to achieve what I desire?

Please let me know if I've missed any information.

Thanks.

[mshmyob]

Sounds like to me you created the navigation buttons in the subform and not the Main form.

Can you attach the database so we can look at it?

cheers,

[Adam Tippelt]

Navigation buttons were created in the footer of the main form, not the subform.

I've now got the form/subform working so that the listbox itself isn't navigating through each sub-record, however it's still doing it invisibly in the background, as it still takes multiple clicks to get through each record. I've also noticed that if a primary record doesn't contain any sub-records, then the form won't even navigate to these primary records, and misses them out entirely.

I've attached the areas of the database concerning this issue. This is the first Access project I've done, so apologies if the code is a mess!

Let me know if you need me to explain anything.

Thanks.

[mshmyob]

Adam I didn't realise you were the same person who was having the problem with the delete key also until I saw both databases. I gave you the solution for the deletion in your other post but when I saw your form and table design I didn't say anything but now I think I should.

You are not really using subforms properly.

If you want I will help you with the table design as well as the form design to help with the problems you are having.

If you want help with the table design please tell me the relationship between the 2 tables supplied - testlibrary and datarequirement s.

The way you have it set you are saying that:

Each TestLibrary can have many datarequirement s
Each datarequirement has one testlibrary

This may be correct but I just want to make sure. I thought maybe requirements (datarequiremen ts) could have the same requirement for many tests (testlibrary) but you say no and create new requirements (datarequiremen ts) for each test (testlibrary)

Could you explain what the Data in the Datarequirement s table and the testlibrary are.

cheers,

[mshmyob]

If you change your "Record Source" for your form to just your table name it works.

cheers,

[Adam Tippelt]

Ah so obvious! Thank you!

[Adam Tippelt]

To answer your other post:

Yes the relationship is supposed to be one to many, as each test may have multiple data requirements.
For example, the 'test' may be to test a drop down menu box. The data requirement would be to test every possible option within that drop down list, so that you test all the options and not just one.

Given the situation it's highly unlikely that the same data requirements would apply to more than one test. If this happens then yes under the current design the data requirement would be 'duplicated'.
I may improve this further down the line to offer an "Add new or use existing from drop down list" option for the data requirements, but for now the data requirements are always added new by the user.

Was this the only reason you thought that I was using subforms incorrectly?

Thanks.

[mshmyob]

OK then you have the correct relationship type.

Since you are just using a List box with it's own query in the subform you actually don't need the subform at all.

cheers,

[Adam Tippelt]

No I didn't think I would, however it's in there because the page originally started out as a duplicate of another, and I've just not taken the subform out.

Thanks for all your help.



EDIT: Turns out I needed the subform for other functionality anyway.