Sub Forms

[Teri Bridges]

I am getting a better understanding of forms and sub-forms. I have found that I can hide sub-forms using a command button. My question is, is there a way to make the sub-form popup when the show hide button is selected?

I have 7 sub-forms, I was hoping to make navigation a bit more friendly, and limit scrolling. I was hoping there might be a way of using code or a command function to direct the user to the specific sub-form they need.

I played with the show hide command and stacking the sub-forms on top of each other but from a design perspective, this is not ideal, although it did give the form a better look and feel.

 

[CJ_London]

You could use a tab control with a subform on each tab

personally I would shy away from that many subforms - can have quite an negative impact on performance

you could have one subform control and simply change the source object to view a different subform

 

[Teri Bridges]

You could use a tab control with a subform on each tab

personally I would shy away from that many subforms - can have quite an negative impact on performance

you could have one subform control and simply change the source object to view a different subform

Could you expand on the thought of one subform control and simply change the source object to view a different subform? I am not familiar with this method.

 

[MarkK]

Here's a very simple example of how to programmatically change out subforms in a single subform control using a tab control change event ...

 

[Pat Hartman]

My question is, is there a way to make the sub-form popup when the show hide button is selected?

Subforms are forms that are embedded in a subform control ON another form. Popup forms are main forms and are not in any way controlled by the form that opened them unless YOU specifically write code to make things happen. Subforms are linked/sync'd with the form on which they are embedded by the use of the master/child link properties. This allows Access to populate the foreign key when you add a new record using a subform. This ensures that the record added by the subform is permanently linked to the record in the main form. You can do this with a popup but the difference is that YOU have to write the code to make it happen.

The Navigation form automatically works the way I think you are trying to make your form work. It uses a tab control and when you click on a button, the specific subform is loaded into the SINGLE subform control on the Navigation form. The upshot of this is efficiency BUT it means that no subform can ever refer to a different subform because only ONE subform is ever loaded at one time.

If you use a tab control and build this yourself, you can build the same kind of structure but you can have multiple subforms loaded at one time if that will make your app work more smoothly.

 

[Teri Bridges]

Here's a very simple example of how to programmatically change out subforms in a single subform control using a tab control change event ...

My problem is my main form is based on the Course table then my lesson table is related to my course table then my topic table is related to my lesson table then my event table is related to my topics table and finally the review cycle is related to my events. Using tabs I have to link forms to the course. I need more of a hierarchy. for example, I cannot put the event sub-form on a single tab in the main course sub-form there is no parent-child relationship. The parent-child for the event form is topic ID. So to use a tab control I have to put the sub-form in the topic form. I am using tab control on the main form for my Issue Log and my Change log.

Course > Lesson > Topic > Event > Event Review

 

[June7]

The most tiers I've ever needed in form/subform is 3 - 5 is getting darn deep. May be a limit of 7 tiers.

You said 7 subforms even though you show only 5 tables.

Want to provide your db for analysis?

Yes, you can have all kinds of code to manage user interaction. The more "user-friendly", the more code.

If you don't tier forms so Access manages pk/fk assignments when records are created, will need code to replicate this behavior.

 

[plog]

Course > Lesson > Topic > Event > Event Review

I explained this in your other thread. With a series of 1 to many relationships in your tables you use a series of main forms with subforms:

Form For All courses ->
-->Form for 1 course with subform for all related lessons ->
---->Form for 1 lesson with subform for all related topics ->
------>Form for 1 topic with subform for all related Events ->
-------->Form for 1 Event with subform for all related reviewevent status

 

[MajP]

Most likely I would do some version of what @plog suggests. Then you can work logically. I would assume when you are working on assigning events you do not need all the details for Courses, Lesson, Topics. However you should be able to navigate in between those forms.
You can nest subforms I think 3 levels deep (maybe more). However these can get ugly and confusing quick. You can synch suborms too. Which is less confusing, but still takes some time to get used to.
This image shows two synched subforms in continuous view. You pick a student on the left and get their data on the right. You unfortunately cannot nest subforms.

Help on how to handle this complex data entry task

I played with this a little. Here are some functions I found very reuseable Public Function GetTotalQuestions(ExamPaperID As Long) As Long GetTotalQuestions = DCount("*", "tblQuestions", "ExamPaperID = " & ExamPaperID) End Function Public Function GetTotalQuestions_2(ExamPaperRef As String)...

www.access-programmers.co.uk

One thing to consider is seperating add, edit, and navigation. A lot of people will allow you to add and edit main form records and subform records at the same time. I find this can get real confusing. I allow the user to navigate the main form records, but if they need to add then that is done from a seperate pop up.

 

[Teri Bridges]

I explained this in your other thread. With a series of 1 to many relationships in your tables you use a series of main forms with subforms:

Form For All courses ->
-->Form for 1 course with subform for all related lessons ->
---->Form for 1 lesson with subform for all related topics ->
------>Form for 1 topic with subform for all related Events ->
-------->Form for 1 Event with subform for all related reviewevent status

Yes, I have done this. This is currently what I have. It is a bit messy and not easy to navigate. I was just looking for different suggestions and options for working with sub-forms. Sorry for any trouble.

 

[Teri Bridges]

The most tiers I've ever needed in form/subform is 3 - 5 is getting darn deep. May be a limit of 7 tiers.

You said 7 subforms even though you show only 5 tables.

Want to provide your db for analysis?

Yes, you can have all kinds of code to manage user interaction. The more "user-friendly", the more code.

If you don't tier forms so Access manages pk/fk assignments when records are created, will need code to replicate this behavior.

frmCourse Details is the main form I was playing around with nesting the subforms. Trying to make sure everything rolls-up and links back to the correct course.

 

[Teri Bridges]

Subforms are forms that are embedded in a subform control ON another form. Popup forms are main forms and are not in any way controlled by the form that opened them unless YOU specifically write code to make things happen. Subforms are linked/sync'd with the form on which they are embedded by the use of the master/child link properties. This allows Access to populate the foreign key when you add a new record using a subform. This ensures that the record added by the subform is permanently linked to the record in the main form. You can do this with a popup but the difference is that YOU have to write the code to make it happen.

The Navigation form automatically works the way I think you are trying to make your form work. It uses a tab control and when you click on a button, the specific subform is loaded into the SINGLE subform control on the Navigation form. The upshot of this is efficiency BUT it means that no subform can ever refer to a different subform because only ONE subform is ever loaded at one time.

If you use a tab control and build this yourself, you can build the same kind of structure but you can have multiple subforms loaded at one time if that will make your app work more smoothly.

Thanks for this.

 

[June7]

Including tblChangeLog (many side of relationship) in main form RecordSource is not advised - tblChangeLog should be a subform. Unless there is something wrong about these links. tblCourse ScopeChangeID field is linked to tblIssueLog IssueID instead of tblChangeLog ScopeChangeID primary key. Both of these tables have fields for CourseID. Something is definitely off with this query.

Looking at Relationships window and seems ScopeChangeID should be removed from tblCourse.

Normally a form is used to enter/edit records for a single table. The main form is trying to edit 2 tables.

 

[MajP]

One thing I like if I have a lot of levels like this is to use a treeview as my navigation. Now this only works well when you tree is relatively "square". By that I mean lots of levels, but no level is super long. If for example there was 10k courses, it gets pretty hard to navigate without adding additional features to search and filter the tree (which is doable).

In this example whatever node you click is shown in the subform. If this is something that may work, I can help. You would likely need to modify existing forms to work better in this view.

 

[Pat Hartman]

I like @MajP 's treeview solution but here is a a different solution that has three subforms. The stuff across the top in pink (company color, not my choice) is used to filter the first subform which is a list. When you select an item in the list, the second subform populates with the details of the selected endorsement. And, the third subform populates with the list of individual data fields required for that endorsement. This app managed insurance policy data so that there would never be any inconsistency when we produced the Word documents at each stage in the issuing process.

 

[MajP]

@Teri Bridges,
The multiple subform issue is kind of an art in how you break it up and display it as @Pat Hartman shows. Sometimes more is less. I can look at Pat''s example and understand it immediately. That is 3 levels deep (State, admitted, Field) which is about as much as most people can absorb in a single form. If you need to go 7 related levels, then likely you need another similar form that you go to in order to drill down farther. Your example had too much on single mainform and hurt to look at. I doubt anyone is working at one time on all levels and needs visibility of all the data.

 

[Mike Krailo]

Pat, other than that magenta header, that form looks great.

 

[Pat Hartman]

That Magenta header is almost the exact color of the client's logo. THEY loved it