Maximum number of controls in a form... (1 Viewer)

jonathanchye · Jun 12, 2011

Maximum number of controls in a form

Hi all,

I edited my original question as I've done some more reading and also experienced strange new issues so I hope someone more experienced could offer some advice.

Scenario : I was happily working away in my form one evening and while in the middle of a cut and paste session (I had to create 8x8 unbound textboxes) I had an error saying "Maximum number of controls exceeded"

Form design : Form is split into two main sections, one contains around 10 textboxes/comboxes and the other section contains a tab control with 6 tabs so far

Access version : MS Access 2010

Solution tried : I've created a simple macro that counts number of controls in a form. It shows 977. I've compacted and repaired database and it seems that I can create more controls fine now. I haven't tried to actually "use" the controls but I can create them and save fine. The count still shows 977.

I then create a completely new DB and only copied over the named form. I tried the macro again and it still showed the same number (977). I also seem to be able to add new controls fine.

Question : To my understanding the absolute maximum is 754 controls in a single form (over lifetime?) How do I find out exactly how many controls I have now? How would I possible know how many controls I will be able to create further?

And most important question of all, why can I have 977 controls in my form without any errors?

It seems someone I've hit the maximum number of controls in my form (most of it unbound textboxes). I am thinking of adding subforms inside my main form and try to put some of the controls inside the subform. Will this help?

edit: Sorry , to be more specific, I have a tab control with 5 tabs. If I created one subform in each tab and copy/paste my current controls inside the subform - will it help?

edit2: This is really weird! I've tried to create more controls again and I've no errors when saving. The last time it warned me that I have exceeded the number of controls on a form. I used the Forms.FRMNAME.controls.count and it shows 980!

My form structure is split into two main sections. I have around 20 controls in the top section and the bottom section is made up of 5-6 tabs with the rest of the controls.

Can anyone explain what is going on here

spikepl · Jun 12, 2011

The thing keeps lifetime history, and there is a limit of 754 http://office.microsoft.com/en-us/access-help/access-specifications-HP005186808.aspx

Backup your db. Then make a new db, from that db import your form, delete form in current db, import form back from new db.

jonathanchye · Jun 12, 2011

spikepl said:
The thing keeps lifetime history, and there is a limit of 754 http://office.microsoft.com/en-us/access-help/access-specifications-HP005186808.aspx

Make a new db, from that db import your form, delete form in current db, import form from new db.

Yeah, I've found that it let me add more controls now after I compact and repair (MS Access 2010). I guess to get an accurate description of exactly how many controls I have now I would have to follow your instructions...

edit: Wow...this is really strange now. I've created a new DB, imported the main form in and then created a macro to count the controls. It still says 977!

I am puzzled now. According to official documentation that absolute maximum is 754. How can I possibly have 977 controls in my form?

I am still able to create controls (textboxes, lines etc) and no errors came up when I try to save.

spikepl · Jun 12, 2011

This is above my experience. Hang in here, and some of the guys who know what the are talking about will have a good explanation

Update: I gave you wrong link (valid for 2003). For 2007/2010: http://office.microsoft.com/en-us/access-help/access-2010-specifications-HA010341462.aspx

But it still says 754

jonathanchye · Jun 12, 2011

spikepl said:
This is above my experience. Hang in here, and some of the guys who know what the are talking about will have a good explanation

No problems mate

Thanks for the quick reply anyways!

gemma-the-husky · Jun 12, 2011

I have to ask

what on earth are you doing that needs nearly 1000 controls?

missinglinq · Jun 12, 2011

And just to add to Dave's question:

What on earth are you doing that needs nearly 1000 controls?

Most experienced developers, including myself, will tell you that a Table with more than 25-35 Fields is almost certainly a sign of a poorly designed, non-normalized database. And since Forms are based directly, or indirectly, on Tables, a single Form with more than 25-35 Textboxes is sure to mean the same thing!

Your design smacks frankly of what someone, perhaps John Vinson, once called 'committing Spreadsheet-by-Access!'

You probably need to give us a plain-English explanation of what you're trying to do here. I expect we can give you guidance in a better approach to your project.

Linq ;0)>

jonathanchye · Jun 12, 2011

I know the question will be asked

80% of the controls are listboxes or unbound textboxes. The table the form is based on only has 15 fields. The rest are result of calculations based on these values.

For example user enters sheet dimensions (2 bound textboxes, width and length) I have 3 additional textboxes showing dimension of the box the sheet will produce.

The rest are Gross Margin calculations and KPIs.

Anyway, why is my controls count showing 997 even after I import form into new DB? Isn't 754 the max? I just don't understand why this is possible...

Edit: In all fairness, I did a quick manual count and I don't think I even have close to 700 textboxes... I do have lines and boxes etc though.

missinglinq · Jun 12, 2011

jonathanchye said:
Anyway, why is my controls count showing 997 even after I import form into new DB? Isn't 754 the max?

Yes, 754 is the limit.

My guess would be that the Macro you're using to do the count isn't designed properly. It must be counting additional things or counting some things more than once.

Of course, Access has a long history of being haunted! 'Ghost' Forms and Controls have been known to stay in Access memory, even after they have been deleted. But doing the 'create a new db and import everything' song-and-dance usually gets rid of them. Since you can continue to to create new controls and save the form, you've obviously haven't reached the limit. But I suspect that as long as you're dealing with a form with this many controls you're going to be chronically having this problem!

I suspect that you would be better off, as you yourself suggested, logically dividing your work into into smaller pieces and utilize either Subforms, Tabbed Controls or both.

Good luck!

Linq ;0)>

jonathanchye · Jun 12, 2011

missinglinq said:
I suspect that you would be better off, as you yourself suggested, logically dividing your work into into smaller pieces and utilize either Subforms, Tabbed Controls or both.

Good luck!

Linq ;0)>

Cheers

Tabbed Controls count as a Subform too? I've read somewhere by using subform you actually "gain" another 754 control limit...

This is interesting as I have most of my textboxes in my tab control. Does creating a new page in a tab control make a difference or you need a brand new tab control?

Reading back what I've typed it does some a bit silly to have nearly 1k controls but it sure doesn't look like that in my form ...

ChrisO · Jun 12, 2011

The Macro may be reporting an incorrect count of Controls.
Try this and see if you get the same result:-

Code:

Private Sub cmdCountControls_Click()
    Dim ctl      As Access.Control
    Dim lngCount As Long
    
    For Each ctl In Me
        lngCount = lngCount + 1
    Next ctl
    
    MsgBox lngCount [color=green]' << 219 on the Form I tested it on.[/color]
    
End Sub

Chris.

VilaRestal · Jun 12, 2011

I'm right on your side on this Jonathan. There's no need to explain why:

There are plenty of scenarios that require hundreds or even thousands of controls in applications for scientific, statistical or accounting purposes.

(Not all databases are for sales shipping tracking.)

Granted in a lot of those cases the data could be exported to a spreadsheet but that is the more complex solution not the least and the least user-friendly solution not the most: You have the information in the database - why not display it there?

The only issue as far as lots of controls is concerned is user-friendliness. Performance certianly isn't. Modern computers can handle thousands of controls with ease. So, it's just up to the designer to make sure it's necessary and, if so, then it's as user-friendly as it can be.

But that is the real reason for wanting lots of controls on a form: Having all the information in one place can be a lot more user-friendly than having to click through different menus to access different bits of it. In the case of helping the user analyse data, dividing the data amongst multiple forms is making life harder for them not easier. When crucial decisions need to be made based on that analysis then having as much detail in one place can be a matter of life or death (in medical databases for example).

The principal of keep controls to a minimum has its place. It depends on the application (e.g. try telling that to someone developing an IDE or music sequencing software). And, to some extent, it is a throwback to a time when computers were less powerful and screens were lower resolution.

People really shouldn't be asking why so many controls. There are many possible applications where it's perfectly appropriate.

VilaRestal · Jun 12, 2011

jonathanchye said:
Tabbed Controls count as a Subform too? I've read somewhere by using subform you actually "gain" another 754 control limit

The tab control and each of its pages count as controls and it doesn't give you anymore to the limit

Subforms are separate forms and each have the same limit.

In theory you could have billions of controls on a form with subforms: 750 subforms on the main form each with 750 subforms and each of them with 750 controls but there is a limit to the number of forms allowed in a database (about 3000 objects in total if memory serves) so the absolute max using subforms would be about 2.1 million. Screen redraws and form loading times would be painful at that stage though

missinglinq · Jun 12, 2011

jonathanchye said:
Cheers Tabbed Controls count as a Subform too? I've read somewhere by using subform you actually "gain" another 754 control limit...

No, a Tabbed Control is a Control. Other Controls, i.e. Textboxes, Comboboxes, etc, placed directly on a Page of a Tabbed Control, are actually considered to be part of the Main Form. They count towards the Main Form's limit of 754, and are referenced as if the were on the main body of the form.

You can 'gain' more Controls by creating independent Forms and then using them as the basis for Subforms, which can then, in turn, be placed on the Pages of the Tabbed Control.

jonathanchye said:
Cheers ...Does creating a new page in a tab control make a difference or you need a brand new tab control?

You can add as many Pages to a Tabbed Control as you need.

If lateral space becomes a problem, you can even have multiple rows of tabs across the top of the Tabbed Control. In Form Design View

Select the Tabbed Control
Go to Properties - Format
Set the Multi Row Property to Yes

Linq ;0)>

missinglinq · Jun 13, 2011

VilaRestal said:
There are plenty of scenarios that require hundreds or even thousands of controls in applications for scientific, statistical or accounting purposes.

True! An entire application may contain thousands of Controls! But not a single Form in a Relational Database app! If Access were designed to be used in this manner, it would not have been designed with the limits it has!

VilaRestal said:
...in a lot of those cases the data could be exported to a spreadsheet but that is the more complex solution

Simply not true! Apps are written every day that export data to Excel because it is designed as a numbers crunching program, and does it very well.

VilaRestal said:
You have the information in the database - why not display it there? ...The only issue as far as lots of controls is concerned is user-friendliness.

Again, absolutely correct! And having a huge number of Controls on a given Form is as user-unfriendly as you can get!

VilaRestal said:
People really shouldn't be asking why so many controls. There are many possible applications where it's perfectly appropriate.

Absolutely incorrect! This Forum is here to help people designing Relational Databases, not scientific apps or anything that requires thousands of Fields. And when someone appears to be using Access for things it was never intended for and is ill-suited for, part of our charge, here, is to inform them of that fact!

It all really comes down to picking the right tool for the job, and Access simply isn't designed to be used in this manner. It can, of course, do mathematical calculations, but it is not optimized to do hundreds or thousands of them, in a brief second, as other programs are!

Linq ;0)>

ChrisO · Jun 13, 2011

I think words like ‘absolutely’ have little chance of being proved. What’s worse is that words like that can limit imagination.

I remember some years ago a well respected person on another site made a similar comment. He said that ~150 check boxes on a Form meant that the database was certainly not designed correctly.
When I asked him if he knew for a fact if the check boxes were bound he could not say yes.
I then asked if the check boxes were unbound then how could he be certain that the database was incorrectly designed.

An example may be that a Form has, lets say, 300 Command buttons. Does it prove absolutely that the database is incorrectly designed? Let’s say the Command buttons are transparent and overlay something like a map. Let’s also say they are used to select a region of the map. Does the selection of a map region prove the database is incorrectly designed or is it simply a good visual way for the user to make the selection?

Another example is this chess game. The main Form has 219 controls on it. Do those controls reflect poor database design? Sure, the tables could have been designed better but is that design reflected in the Form Controls?

If we look at the Date Picker Form by Allen Browne does the number of Controls on that Form automatically mean that any database that uses it is poorly designed?

The problem with ‘absolutes’ is that they can be so easily disproved.
We may very well be in the business of relational databases but not to the point of limiting imagination.

Chris.

VilaRestal · Jun 13, 2011

missinglinq said:
An entire application may contain thousands of Controls! But not a single Form in a Relational Database app! If Access were designed to be used in this manner, it would not have been designed with the limits it has!

The limits it has are utterly arbitrary and not for functional reasons. Are you suggesting they clipped Access to stop it being adapted to other purposes? Very many relational databases do involve forms with hundreds of controls. Very many applications do too. And perfectly user-freindly in the process.

missinglinq said:
Apps are written every day that export data to Excel because it is designed as a numbers crunching program, and does it very well.

Indeed, but it is a more complex solution. Exporting to Excel is not simple code and it makes the whole application more complex and more prone to bugs because it depends on an external application behaving itself (and Excel at that - notoriously buggy software).

missinglinq said:
And having a huge number of Controls on a given Form is as user-unfriendly as you can get!

Not necessarily! Most of the programs you will use on a daily basis will have hundreds or thousands of controls on them. Unless you regard a toolbar as one control and don't regard the buttons on it as controls.

missinglinq said:
Absolutely incorrect! This Forum is here to help people designing Relational Databases, not scientific apps or anything that requires thousands of Fields. And when someone appears to be using Access for things it was never intended for and is ill-suited for, part of our charge, here, is to inform them of that fact!

It all really comes down to picking the right tool for the job, and Access simply isn't designed to be used in this manner. It can, of course, do mathematical calculations, but it is not optimized to do hundreds or thousands of them, in a brief second, as other programs are!

I couldn't disagree more. Are you really saying that people designing scientific apps (or anything that requires thousands of Fields) should be turned away at the home page? If so, why?

And Access can do millions of calculations in a brief second, same as any computer can. That comment astonished me. It's what computers do. Any programming language, however high level can manage that.

jonathanchye · Jun 13, 2011

Now I am really worried... I've used your code and it reports exactly the same numbers as mine!

This is my code :

Code:

Dim Temp As Double

Temp = Forms("frmEstimate").Controls.Count

MsgBox "No of Controls:" & Temp & ""

ChrisO said:
The Macro may be reporting an incorrect count of Controls.
Try this and see if you get the same result:-

Code:

Private Sub cmdCountControls_Click() Dim ctl As Access.Control Dim lngCount As Long For Each ctl In Me lngCount = lngCount + 1 Next ctl MsgBox lngCount [COLOR=green]' << 219 on the Form I tested it on.[/COLOR] End Sub

Chris.

jonathanchye · Jun 13, 2011

@VillaRestal : Thanks for your support

Indeed most of the controls on my forms are unbound so most textboxes actually displays results of calculations. I would've used a single form featuring datasheet view but the client wants a really specific layout which can't be done with Access Datasheet view...

@missinglinq : I do appreciate your advice a lot and I agree that the very foundation of Access is built upon Relational databases. However, I am designing something rather bespoke for the client and based on cost/development time considerations Access is the perfect tool. I have a balance of good UI and also some basic programming language built in (VBA) to support it. The client has full Office pro license so I don't see why not try to use Access to achieve this.

Again, the table the form is based/bound on "only" has 15 (maybe 1-2 more extra in the future) fields. The rest of the controls are checkboxes, tabs, boxes, lines, and bulk are textboxes designed in a layout the client is familiar with. I could load the forms with subforms but that will slow things down (loading times) and also I will have to modify a lot of code... that's a story for another day though. I just want to get to the bottom of the current issue : why is the control count report 900+ controls?

I am almost certain there are no 997 controls as reported by the macro. Something must be really wrong here. Let's say there are so many controls then it defies logic since Access (even 2010) only allows 754! I am really puzzled by this.

edit: I am able to create new controls (although just placeholder controls with no function) and save the form without any errors. I ran the macro again and it reports 998 controls which is correct at the time as I've added 1 unbound textbox.

jonathanchye · Jun 13, 2011

ChrisO said:
The problem with ‘absolutes’ is that they can be so easily disproved.
We may very well be in the business of relational databases but not to the point of limiting imagination.
Chris.

Hit the nail on the head there mate

Maximum number of controls in a form... (1 Viewer)

Registered User.

Eledittingent Beliped

Registered User.

Eledittingent Beliped

Registered User.

Super Moderator

AWF VIP

Registered User.

AWF VIP

Registered User.

Registered User.

';drop database master;--

';drop database master;--

AWF VIP

AWF VIP

Registered User.

';drop database master;--

Registered User.

Registered User.

Registered User.

Similar threads

Users who are viewing this thread