using many subforms (1 Viewer)

I am looking for a discussion on using many, many subforms in one application. I have found that after opening one too many of these subforms, Access gives the error message, 3048 - Cannot open any more databases.
I'm not looking for a discussion on another approach to this database that does not use subforms, but any knowledge to how many subforms can be open before Access starts complaining (such as error 3048), and any work a rounds that might help.

Here is a link re some Access specifications.
Since subforms are controls on a form, the max number of controls on a form would seem to be the limit.

heres a helpful little item. Just import the one form into your project and open it. It shows you how many available connections you have.

Another constraint will be the complexity of the subforms, like if each one contains lots of lists and combos, then access implicitly opens lots of recordsets to supply data to those controls, and eventually you'll run out of connections.

I ran into this problem a lot when I was first writing systems, and when I look back at that work, I was not focused enough on the workflow of the user. The user has specific tasks to perform. It's tempting as a beginner to make a monolithic UI, one complex location where you can do anything and everything. Avoid this temptation. Consider what job the user is going to spend 80% of his time doing, and write a very simple UI to do that job. Then write a different UI for a different task. Don't write that monolithic UI that does everything.

my2c,
Mark

Do all the forms need to be available all the time? You might be able to use something like the navigation form interface where depending on what "tab" the user selects, one and only one subform is loaded into the subform control.

@Golferguy

Off topic but I just clicked on both links in your signature line.
Neither seem to be working.

Ridders,
Thank you for the reminder. I never got any response or work from my website in about 10 to 12 years so I finally just let it go. I have not even been on this site for 4 or 5 years. When I posted this morning and saw my signature line I said to myself, "I should remove those." So I'm going to see if I can find how as soon as I send this. Hey, I just saw the "Show your signature" check box. Let see if that works. (o:

@Pat Hartman, The overall design of this database is to have the look and feel of the new (2010) navigation of the main horizontal tabs and the buttons down the side, but with the added feature of being able to return to the screen where you have been without having to relocate the record you were active with. We have Estimating, Contracts, Forecasting, and my boss's favorite, Need Attention (Where all late, missing, upcoming information is located, and by user name if desired.). Therefore, many, many subforms so whatever someone was doing, it is still there when they return. Users and the boss have loved it. Only the boss and I have ever had this message happen to us, but I'm looking to make some changes that will prevent any user from seeing the message.

@moke123, I have downloaded that DB and put the form into my database. I think I will be using it to manage the open forms/subforms by recording in a local table when a form is opened/visited. then as the user approaches running out of connections, I will close the least used, and oldest visited form. this will not be too difficult as ALL forms opened in subforms of the one main form are all opened by one utility function.
Thank you for the link! I'm already using it. Interesting to see the "cost" of each form as it is opened, then do a "requery."

Do you and the boss have less memory than the others? Do you have more stuff open?

Sounds like you have reached the limits of Access. It might be right to rethink your method. Perhaps keeping a hidden form open that you can use to log the current record for various forms would work rather than keeping all the forms open at once. Or perhaps switching to the A2007 tabbed dialog rather than forms with subforms would work.

I'd need to know a lot more about what you are doing to offer anything specific.

@Pat Hardman, Actually the boss has a great memory, mine is fading cause I'm getting old. (Please note this was only humor, hope you enjoyed it.)

Both the boss and I have plenty of memory on our computers. But, we both use the whole database, not just one, two, or three of the tabs. It is not just the current record that the boss is interested in, but actually having the user to be able to return to the form that they left. Cursor position and everything. The way I "close" a form is to hide the subform and then open a new form by making the newly requested subform visible. No subforms except "Home" is opened with the database. Each subform and its SourceObject have the same name. So when I want to open "frmEstimates" my routines know to load this form name into the subform name "frmEstimates" and then make this subform visible.
I have done a version of this method before, except I only used one subform. Which meant the users did not have as quick of response when going from one tab to another.
In my current DB, about 1/2 the users are remote and the link into the main office is pathetically slow. Another reason to keep all these forms (and subforms) open.
Your hidden form idea is a good one. I personally have used that idea to maintain my global variables since I first started with Access in the 1990s with Access 1.1. In this case I'm planning on a local table to keep track of form name and time opened and last time used. That way I can, via a sort (yes, another drain), establish which form (and subform) to truly close to free up more resources to use for new forms being opened.
User Moke123 offered a DB that has one form in it that allows me to see how many available connections are left. The functions to run this form will also allow me, within the normal operation of my DB, to know when the DB needs to start closing the "unused" forms. Basically, a dynamic cleanup while maintaining as much of the "the form is where you left it" approach that my boss and the users have come to enjoy and expect.
BTW, this application is based on the "look and feel" of the A2007 tabbed dialog. But its implementation of a form having to reopen from the beginning just is not what we wanted in this application.
Thank you for your interest in helping!

You keep referring to subforms. Are these really subforms or are they just forms that are opened from other forms?

I use a similar technique. I don't like to have multiple forms visible at one time because it just confuses the user, so when formA opens formB, I hide formA and when formB opens formC, I hide formB. Each form passes to the next form its own name so that as a form closes, I can open the form that opened it and so go back up the chain. I may end up four or five levels deep using this but haven't had any problems with it. The only issue is that I have to make sure that any particular form can only be reached via a single path. Otherwise, the back out breaks.

If you really are using subforms, perhaps that is what is causing the problem. Having many subofrms on a main form makes the main form "heavy" and perhaps as a single form, the main form is getting too large. Maybe you wouldn't have a problem if you did something more like what I do using "main" forms rather than "sub" forms.

Pat, a subform is just that, a subform. It is a form that is embedded into another form. Not called by another form, but embedded in another form. Please re-read my previous posts again, don't try to second guess me, but listen to what I'm asking about and for. I do have 24 years of experience with Access, and this client wants this tailored solution, rather than the normal Access dictated solution.

@GolferGuy
Just out of interest, could you post a screenshot of the Access database window showing what the app looks like with lots of forms / subforms open simultaneously.
I'm interested to see what the experience is like for your end users

Colin, I really believe you will find this app very pleasing to the eyes. This is the Home screen, and there are 10 separate photos that are displayed here, completely random. Only one photo per opening of the DB. The horizontal tabs you see here are for me and the boss only. Everyone that signs into the DB only gets the tabs they are assigned in my security system. Just below the tabs is where all the subforms are. The first inch on the left is where all the vertical menu options are. A different subform of vertical menu options per horizontal tab. The rest of the area of the form is taken up by all the working area subforms. Those subforms are all the same size, 14.6042" wide and 6.0833 tall. I had to go with the smallest screen size of all the users. The second screen shot is the Contracts or Jobs screen, with sample data. This screen shot is only of the area that the subforms are in, the vertical menu options on the left (and for jobs, the different values of the displayed job below the vertical menu options). Where you see all that area for notes, can be replaced by the Contract Change Order form, or the Change Request form, or the Assign Change Requests to a Change Order form, Or a listing of all Change Orders and Change Requests. Double click any one and that one is displayed for the user. To get into this Single Record form in the first place, the Overview form, of all the contracts/jobs is displayed. And a double click on any job brings up the Single Record form you see here.
So, as you can see, the user only has one form in front of them at a time. I consider the Job form with one of it's other "helper" forms just one form. At least as far as a user is concerned.
Is this what you would call an acceptable experience for a user? Over the years, my users and clients have all liked this form of UI. This particular DB is, in my opinion, the best I have done so far.

Yes, its certainly very clear & well laid out.

What I can't tell from looking at this in form view is how many subforms it contains. There's one obvious subform on the right but from your description there are several more - its hard to tell what is a subform.
Obviously this is one of several tabs so the obvious question is how many subforms are there in total on this form?
And unless the number really is huge, why are you getting issues with error
"3048 - Cannot open any more databases"?

I'm asking as I've just been pushing one of my large schools databases (with around 16000 database objects in all) to the limit and failed to trigger the issue.
In my tests, I made use of a fairly complex form (see attached screenshots) with 10 subforms (not all visible).

The form itself is a staff home page designed to give instant access to most of the features staff need frequently. Because its complex, it takes a couple of seconds to load the first time.
When users click any item, the home page form is hidden but not closed so it can then be recalled instantly

There are 5 versions of the form for different types of staff.
Originally this was one combined form for all staff with a total of 21 subforms
I dumped that as it took about 7 seconds to load the first time.

I've just tested this combined form using the very useful available connections form kindly supplied by moke.
The connections dropped by 22 (form + 21 subforms) when I opened it leaving 227 available.

I then clicked on just about everything I could click (and there are a lot of things you can click) on this form / subforms and didn't close anything.
The database eventually became sluggish but I never got the available connections below 170 & no errors occurred.

Having said that, I have some months ago seen error 3048 in another of my databases.
If I can remember which and how/why it happened, I will test that to destruction ...!

So i'm even more intrigued by your database design.
Does that become increasingly sluggish?
Do the available connections recover fully as you close items?
Do you ever have issues with MaxLocksPerFile?

You may find these links worth reading
https://access-programmers.co.uk/forums/showthread.php?t=122973
https://social.msdn.microsoft.com/Forums/office/en-US/b018ec1e-0927-4e0c-b797-b539296465a1/docmdoutputto-error-3048-cannot-open-any-more-databases?forum=accessdev

Its been a long time since I researched the topic, and even then I didnt have the error but was responding to a poster who did. If i recall correctly there were some twists to the amount of connections. For instance I believe that combo boxes consume 2 connections and joins in queries consume more than 1. Check for yourself but I think I recall it correctly.

Thank you Moke. (o: Problem is, I need all the combo-boxes I have, and most of my queries have joins in them. I have found when you normalize data, joins in queries are a must.
After I finish my current addition to the application, we are going to change from having an Access DB backend to using SQL Server for our data storage. Then at least all those query joins will be in SQL Server and should only cost 1 or 2 connections within Access.

GolferGuy:
Something I didn't think to mention in our email exchange...

Just a follow up to your last comment, my setup has a SQL Server BE file.
That may be why my connections didn't 'fall through the floor'
I wouldn't have even attempted the home page example with an Access BE file over a network ....

Moke:
Thanks for providing that little utility
I know you didn't write it but its very useful for development purposes nevertheless

Pat, a subform is just that, a subform. It is a form that is embedded into another form. Not called by another form, but embedded in another form. Please re-read my previous posts again, don't try to second guess me, but listen to what I'm asking about and for. I do have 24 years of experience with Access, and this client wants this tailored solution, rather than the normal Access dictated solution.

Click to expand...

Sorry I offended you. I have no idea of what your level of experience is and many people refer to popup forms, incorrectly, as subforms. All I can tell you is that when MS added the Navigation Form to the types of forms, they specifically make it use a single subform control into which they loaded the specific form called for by the current tab. They did it that way for a reason.