Adding subforms to form using VB

[mrlw00r]

Is it possible to add a subform to a form programmatically? I've been hunting around and I can't find anything that says that it's possible.
I'm trying to solve a problem which is causing the "3084 - Cannot open more databases" problem (thread: http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=54311 )

Mike

 

[mrlw00r]

I've tried to create a reference to the Controls collection in an attempt to add the subform directly, but:

Private Sub Form_Open(Cancel As Integer)
Dim c As Collection
set c = Me.Controls

End Sub

gives a type mismatch...

 

[WayneRyan]

Mike,

I can't fathom that creating a subform (or form) programmatically
would be the answer to this. I was amazed that in your other
thread you had over 100 tables open. Are they recordsets,
queries, or just tables feeding forms? I would think that this
would be the place to start.

Maybe this is the opposite of most other people's design
problems, instead of needing to create more tables you
need to consolidate some.

Just some thoughts.

Wayne

 

[mrlw00r]

My database has ~50 tables, and when I say I have over 100 open, I'm counting every occurrence of a table being referenced (ie through query, form, combos etc). This means that I'm really only looking at data in about 20 or so tables, but the references build up.
I agree I should rethink my design, but I like the tab pages - and really I want to be able to switch the content in the pages on and off when I move between them, so that I don't have such a silly number of table references! So far the only good idea I've had is to add/remove the forms on the tab pages...
Somebody said setting/clearing subform recordsources would work - but that hasn't appeared to have done much to help - I still get the 3084 error...

 

[Mile-O]

Why not have the subform's empty and, when the tab page is opened, set the SourceObject of the subform to the form you want as the child and remove the SourceObject of previous tabs?

 

[mrlw00r]

Ahhh!!!! Bingo...
Yes - that sounds ideal! I would need to reconnect child-parent fields - how can that be done?

 

[Mile-O]

    Me.SourceObjectName.LinkChildFields = [i]Field[/i]
    
    Me.SourceObjectName.LinkMasterFields = [i]Field[/i]

 

[mrlw00r]

Thanks! (Should have RTFM for that one really!!)
Will post another message pending success/failure