Error 3014 - Cannot open any more tables

[rafi2]

Can anyone provide assistance with the above error? It comes up when I try to open a report from my form. The form has 15 tabs and approximately 5 subforms on each tab, which I suspect is causing the problem.

I have found some help on the web, but I am not sure how to use it... http://www.mvps.org/access/bugs/bugs0010.htm ...Item number one describes my situation exactly, but I don't know where to set the "record source on click" for each tab. Any help would be greatly appreciated.

Thanks!

 

[llkhoutx]

I suspect a design problem. You form seems exceedingly complex. Is that complexity really necessary? Is the data on the various subform related. I suspect not. All you're subforms are obviously not visible at the same time.
Does one cause the modification of the data on another tabb? Again, I suspect not. Surely, all tabs and subforms are bound to different table/queries.

I had a similar complexity issue which I solved by dynamically setting subform object sources on a single form (tab ) only when actually used. That is, why can't you use one form with 5 subforms, the object source of each subform being set as required. Other "view" modifications based upon a given view (tab) could be handled via VBA recordsets.

 

[rafi2]

Hi,

Thanks for your reply. I'm using each subform for basically focusing in on specific data in from a single table. The table I'm looking at has the following fields:

ProdID# (primary key)
Heading
Code

Each subform is a datasheet view to enter records of Code for a unique ProdID# and Heading combination. The "master" form shows a record for each ProdID#. Each subform is based on a query to display only the Heading in question, and each new record in the subform is defaulted to having the same Heading. Essentially, each subform is the same, except for the query to focus on a different heading.

Can someone suggest a better design for this?

 

[llkhoutx]

I already have in this thread.

Create five empty subforms, no scroll bars, no navigation buttons, no headers or footers, etc. all of a size of to accommodate your subject subforms and just being gray (or what ever color) holes in each and place on you MainForm, setting the appropriate Master/child Links of each subform. The Master/Child Links can be set dynamically, if necessary, i.e.

FormsMainform!sform1.LinkMasterFields = "PropID"
FormsMainform!sform1.LinkChilds = "PropID"

On the OnOpen event of the form, change the Source Object of each form to the to the name of the appropriate 1st set of subform.

Subsequently, on some event, change them again as appropriate.

Example:

forms!Mainform!sform1.sourceobject = "sfrm1"
forms!Mainform!sform2.sourceobject = "sfrm2"
forms!Mainform!sform3.sourceobject = "sfrm3"
forms!Mainform!sform4.sourceobject = "sfrm4"
forms!Mainform!sform5.sourceobject = "sfrm5"

If your subforms names are substantially similar or can be computed or gang set, you can use the sform1, sform2, etc. form modules for all code, computing or gang specifying the specific subform name in once the VBA code.

If your VBA is not-generic, you have to use class modules, because code references are to

forms!MainForm!form1.form.(ctlName)

regardless to the subform actually loaded. And in which event use function calls for event procedures. The sourceobject name is not used for anything else in your VBA.

I use this method extensively and it is fast, even with complex subforms with lots of class module code.

Build a small test example and you'll see how good this method works.

 

[rafi2]

Yes, that worked great! It really speeds things up too.

Thanks!