Maximum # of Controls on a Form...

[Guest]

I'm using Access 2003.

Unbound Forms w/ everything, but a LOT of code.

All of a sudden, after inserting ONE line of code in a module..
a simple cmdABC.Enabled = True, when the code rund, I get
the "PurplE' "Encountered an error" which includes the "Don't
Send" button.

I have 860 Controls on the Form.

What is the Max, & can an error occur if there are "too many"
of a particular object (label, Button, textbox, etc)?

Also, is it possible I have exceeded the "module code limit" - whatever that
is??
Is there a way to use a MsgBox to display "space used" in a module?

I even imported the Form into another DB, compacted it, & then imported it
back into the Original DB w/ a Compact...still a problem.

TIA - Bob

 

[Albert D.Kallal]

I thought the limit was 760 controls...so you are beyond the limit.

The solution to is put parts of the form as "sub-forms"...preferably behind
tabs, and that will get you more then enough breathing space.

And, perhaps some of your forms can be "continues" forms to represent grids
and repeating data. Take a look at the following screen shots, and the
number of controls is VERY small, yet the amount of repeating data is large.

http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

I would most certainly re-design,and break this up into multiple forms. At
the very least, least start breaking parts out to sub-forms, and you
effective have no limit, but I think you are going WAY beyond what a user
can handle with such a huge number of controls. This also hints that you
have non normalized the data.

 

[Guest]

Albert - Thank you.

This App is quite complex & requires MANY fields in statistical analysis.

It has been working fine until tonite.

I like the info you attached. I'll try to add those techniques
to my designs.

Thanks again - Bob

 

[Larry Daugherty]

Hi Bob,

Like Albert, 760 controls is the max I remember.

Would you please explain your data design and your form design? I get
very uncomfortable when I see people claiming the need for over 100
fields in a table or 100 controls on a form. I fell asleep in
statistics so often that I dropped the course. Both the field of
statistics and I are the better for it. So my issues aren't to do
with statistics but with data; its definition, relationships, storage
and presentation.

Big numbers of fields or of controls cause me to suspect that
repeating data may be getting stored in separate fields and displayed
in separate controls. More often than not, that's the case.

If there is repeating data then there should be a child table with a
record for each data instance. It's surprising how often people post
into these newsgroups asking for a fix to a query or something. They
describe their table as: RecordID, RedA, RedB, RedC, RedD, BllueA,
BlueB, Bluec, BlueD, ... Those Red & Blue values belong in that child
table: ChildID, ParentID, Color, A, B, C, D ...

Once the new data structure is in place it is easy to create the Forms
and Reports with their underlying queries. Typically you use Forms
for data entry (I absolutely don't believe that you manually enter
data into 860 controls!) and you use Reports to display the data.
Given the data structure above, you can create a form/subform to
display the data for the main record at the top and the data from the
child table in the subform. The subform will display just part of the
data at any given time but can be scrolled through the entire data
set. I suspect that what Albert sent you was along the lines of the
above.

HTH

 

[Guest]

Larry - This App is for a Major manufacturer.

I have 193 fields in one of the Tables. Yes - 193. I TRIED to
reduce that but the Company does such detailed analysis that
my Mgr, who FULLY understands the need for "less fields", told
me the 193 fields WERE absolutely necessary. I have other
Tables in one-to-many relationships. Again, this is massive
statistical analysis data, & I was told to make it work, & except
for this problem last nite, it works very well.

I have NO repeating data. I am ultra-sensitive Programming so
that won't happen. The 860 Controls includes Labels, which were
needed to describe the different "tests" to obtain the data.

I wrote the whole thing unbound using SQL statements to populate
everything.

As for the 760, at one tine I thought it was 754. "Usually"
if adding more Controls to exceed the 760, the Programmer is
"warned" - I wasn't. That's one reason I asked the question,
thinking maybe the 760 had been increased.

I'll reduce the Controls & split the Form nicely into 2 Forms.
The design will allow that w/o using a Subform(s).

It should continue to work just fine. When I Program, I learned
from the Tab Control introduced in Access 97 how "efficient" &
User-friendly it can be.

Please let me know if you have any other questions.

Thank you - Bob