Switching subforms within forms (1 Viewer)

Zydeceltico

Registered User.
Local time
Today, 09:09
Joined
Dec 5, 2017
Messages
843
Hi All - to

I have a main form w/ general but critical info. I would like to add a subform control to this Main Form. Then I would like to add a combobox to the header of the Main Form.

I would like the user to make a selection from the combo box and a different subform appears in the subform control - and have everything linked back to PK_ID of the current record of the Main Form.

Is this possible?

Thanks!

Tim
 

essaytee

Need a good one-liner.
Local time
Tomorrow, 00:09
Joined
Oct 20, 2008
Messages
512
My initial thoughts are to include in your main form all the subforms required, I'm assuming not that many. Anyway, from your combobox when selecting an entry, make visible the relevant subform and hide all the others, this would be done from the AfterUpdate event of your combobox. I would create a form level function to achieve this as you would also need to address this during the On Load event, so might as well use the same function. Within the function you could also set the Master/Child fields, even recordsource of the subforms.

Not sure about the actual creation of a sub-form at runtime, but in the back of my mind I think there is a finite number of times controls can be added to a form. Once this limit is reached, you have problems. I think the number is in the 700's.
 

Zydeceltico

Registered User.
Local time
Today, 09:09
Joined
Dec 5, 2017
Messages
843
My initial thoughts are to include in your main form all the subforms required, .

I only have five subforms that I would want to occupy the same space.

Do you mean literally lay all of them one over the other in the subform control?

I'm cool with that just want to make sure I understand before heading down the rabbit hole.

:)

Thanks,

Tim
 

essaytee

Need a good one-liner.
Local time
Tomorrow, 00:09
Joined
Oct 20, 2008
Messages
512
I only have five subforms that I would want to occupy the same space.

Do you mean literally lay all of them one over the other in the subform control?

I'm cool with that just want to make sure I understand before heading down the rabbit hole.

:)

Thanks,

Tim

Yes, in design mode, it can appear to be a bit of a mess.

Another consideration is the use of a Tab Control, in your case 5 page tab control, each page containing a subform. In design mode it will appear tidier, but that is not the reason to adopt the Tab Control approach, who cares what the design mode looks like (within reason).

Other considerations: Having 5 sub-forms on the main form, by default, each subform will be looking up data in order to display said data every time a main form record changes (On Current event). The non visible subforms do not need to lookup data, only when you make them visible, set the recordsource at that point. Just a thought.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 13:09
Joined
Jul 9, 2003
Messages
16,244
Try This :-

See attachment
 

Attachments

  • Switching subforms within forms_1a.zip
    35.8 KB · Views: 37

Zydeceltico

Registered User.
Local time
Today, 09:09
Joined
Dec 5, 2017
Messages
843
Yes, in design mode, it can appear to be a bit of a mess.

Another consideration is the use of a Tab Control, in your case 5 page tab control, each page containing a subform. In design mode it will appear tidier, but that is not the reason to adopt the Tab Control approach, who cares what the design mode looks like (within reason).

Other considerations: Having 5 sub-forms on the main form, by default, each subform will be looking up data in order to display said data every time a main form record changes (On Current event). The non visible subforms do not need to lookup data, only when you make them visible, set the recordsource at that point. Just a thought.

Yeah - I thought about tabbed control - but decided against it for the reason you describe. I don't mind Design View being a mess if it works correctly on the outside in RL. I think - with our current need - that tabbed form would actually be more confusing - like TOO many options instead of forcing to make a choice.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 13:09
Joined
Jul 9, 2003
Messages
16,244
If you happen to be operating on it exactly the same data but different sets of the same data, in this case I show different sets of data denoted by the Numbers one through 5 representing the data sets. So in this case it's simpler in the sense that you don't have so many forms, but you need to be clear and careful on how you set your tables up and you need to set the default value of a field in the subform.

See Sample Attached

I have an example of using a single table, a single subform and a single main form to build the "Time Management Matrix", which looks like it holds 4 different forms with 4 different sets of data, however it's the same data from the same table (a single table) used in the same subform multiple times. It's quite an interesting example! You can find it on my website here:- http://www.niftyaccess.com/subform-time-management-matrix-example/ you are welcome to a copy of the database for free - contact me for details.
 

Attachments

  • Switching subforms within forms_2a.zip
    32.6 KB · Views: 28

Zydeceltico

Registered User.
Local time
Today, 09:09
Joined
Dec 5, 2017
Messages
843
Try This :-

See attachment

So - - - I am looking at this in design view and I am wondering how this code works:

Code:
Private Sub cboChooseFrm_AfterUpdate()
    Me.subFrmWinForMultiForms.SourceObject = Me.cboChooseFrm.Value
    Me.subFrmWinLabel.Caption = Me.cboChooseFrm.Value
End Sub

especially this code:

Code:
SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=-32768))
ORDER BY MSysObjects.Name;

How do I inculcate my own frmNames w/ this?

Explain?

:)

THX

Tim
 

Zydeceltico

Registered User.
Local time
Today, 09:09
Joined
Dec 5, 2017
Messages
843
If you happen to be operating on it exactly the same data but different sets of the same data, in this case I show different sets of data denoted by the Numbers one through 5 representing the data sets. So in this case it's simpler in the sense that you don't have so many forms, but you need to be clear and careful on how you set your tables up and you need to set the default value of a field in the subform.

See Sample Attached

I have an example of using a single table, a single subform and a single main form to build the "Time Management Matrix", which looks like it holds 4 different forms with 4 different sets of data, however it's the same data from the same table (a single table) used in the same subform multiple times. It's quite an interesting example! You can find it on my website here:- http://www.niftyaccess.com/subform-time-management-matrix-example/ you are welcome to a copy of the database for free - contact me for details.


It APPEARS that this is what I am after - yet - I am working with 5 different tables instead of just pigeon holes of a single large table.

Can I make it work the same way with multiple tables?
 

essaytee

Need a good one-liner.
Local time
Tomorrow, 00:09
Joined
Oct 20, 2008
Messages
512
Suck what? ...... and see what exactly?

Is this a riddle? hmmmmmmm...............muchas cervezas?

Or just...................

It's 4.15 am in the UK, so I suppose all my UK friends are sleeping, so I'll add my two bits in regarding Uncle Gizmo's example.

I think what @Uncle Gizmo is suggesting (suck it up) is import the code and try it out (if that's not what he's suggesting I'm sure he'll correct me).

That 32,768 value figure (?) obviously refers to all the forms, including the main form and is extracted from the system tables. That is simple enough to use as a record source for a combo box. Only one sub-form object is created and is re-assigned the SourceObject via the combobox.

For a full-blown application, there could be 100's of forms, so you would need a way to somehow filter to your subset of sub-forms. I'm sure UG has a method in mind, I can't think of an approach at the moment, using this method (I do, but I'll leave alone for the time being).

You mentioned that you need to account for different data sets in relation to UG's example, but the UG's sample I downloaded accounts for different tables already.

What UG's example does show is that, if you wanted to, you could create a table that contains your subforms to be opened, if you didn't want to query the system tables. There are many ways to skin this particular cat.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 13:09
Joined
Jul 9, 2003
Messages
16,244
Essaytee you hit the nail on the head in more ways than one.

Thank you.


Can I make it work the same way with multiple tables?

It depends....

Yes, if your tables are exactly the same format.

But..... if the tables are exactly the same format, then you have a design issue.

Sent from my SM-G925F using Tapatalk
 

Users who are viewing this thread

Top Bottom