Problem with limit on maximum number of textboxes on a form or report in MS-Access

[ian.davies52]

Is there anything I can do about the apparent limit on the number of
textboxes that have calculations as their control source on a form or
report in ms-access? I have a query that pulls together all the fields
from two tables, each with about 20 fields. I then have a statistics
form that has the query as its source and the form has 273 textboxes
on, each with a calculation based on the underlying query values. The
form looks like a spreadsheet with row 1 to 23 and columns A to M.

I have checked all the formulas used in the textboxes and they are
correct. The problem is that when I open the form I get a message in
the lower part of Access window that says "calculating" for a couple of
seconds and then the textboxes fill will "#error". I tried deleting
some of the textboxes to find out which ones were causing the error,
but what I found was that it wasn't particular textboxes formulas that
were the problem, but simply the quantity of them (I deleted half of
them and the form worked. I then deleted the other half and the form
worked again).

What can I do with a form that has 273 or more textboxes on?

Thanks,
Bemused.

[XMVP]

<ia**********@btopenworld.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...

What can I do with a form that has 273 or more textboxes on?

Thanks,
Bemused.

The only thing you can do is join Access Morons. Send your request for a
membership application to

ac***********@hotmail.com

and receive daily moronic database tips from famous Access Morons such as
Larry Linson, Tony Toews, and other MVPs.

[pietlinden]

Hoo... why do you have 273 controls on your form? My first guess is
you need to normalize your data

[Salad]

ia**********@btopenworld.com wrote:

Is there anything I can do about the apparent limit on the number of
textboxes that have calculations as their control source on a form or
report in ms-access? I have a query that pulls together all the fields
from two tables, each with about 20 fields. I then have a statistics
form that has the query as its source and the form has 273 textboxes
on, each with a calculation based on the underlying query values. The
form looks like a spreadsheet with row 1 to 23 and columns A to M.

I have checked all the formulas used in the textboxes and they are
correct. The problem is that when I open the form I get a message in
the lower part of Access window that says "calculating" for a couple of
seconds and then the textboxes fill will "#error". I tried deleting
some of the textboxes to find out which ones were causing the error,
but what I found was that it wasn't particular textboxes formulas that
were the problem, but simply the quantity of them (I deleted half of
them and the form worked. I then deleted the other half and the form
worked again).

What can I do with a form that has 273 or more textboxes on?

Thanks,
Bemused.

From Specifications in help on forms it states "Number of controls and
sections you can add over the lifetime of the form or report is 754"
With 273 textboxes, if you were cutting and pasting its possible you
exceeded the count. For example, I may drop 2 Textbox's, with label
onto a blank form. I would then have Text0/2 and Label1/3. If I cut
both of them out and drop a new box/label it is Label5/Text5.

It's possible that with cutting and pasting and who knows what else you
have exceeded the control count.

You might want to open a new form. Then open your current form. Drop
all controls in your current form into the new form. Then drop all the
code into the new form. This will reset the counter of the form back to
most likely a lower count.

[Larry Linson]

"Salad" <oi*@vinegar.com> wrote

From Specifications in help on forms
it states "Number of controls and
sections you can add over the lifetime
of the form or report is 754"
I think you got it, Salad... it's that "over the lifetime" part that is the
kicker. First time I encountered this was on an inherited form in Access 2.0
days -- at least a couple of people had worked on it and they must have done
some serious adding / deleting controls. The only other time was when I was
trying to exactly duplicate a paper form that was to go to a department
where it was to be scanned, and we were charged with making it identical.
Most of the controls on the form were lines and rectangles.

Larry Linson
Microsoft Access MVP

With 273 textboxes, if you were cutting
and pasting its possible you exceeded
the count. . . .
You might want to open a new form.
Then open your current form. Drop
all controls in your current form into
the new form. Then drop all the code
into the new form. This will reset the
counter of the form back to most likely
a lower count.

[ian.davies52]

Hmm, thanks for the suggestion Salad and Larry, but I tried cutting and
pasting into a new form and even tried just half of the textboxes and
it still didn't work. I really not sure what is going on. A decent
error message would be helpful.

Ian

[penguin732901]

Perhaps some of your calculations could be done in a query. Can you
post your table structures so we can understand a bit more what you're
trying to do?

[ian.davies52]

This is a golf stats database for a golf society. I have one table
called roundTable that has the following fields:
roundID (autonumber primary key),
courseref (integer)
date (date)
teeused (integer)
conditions (text)
hole1score (interger)
hole1fairwayhit (yes/no)
hole1greeninreg (yes/no)
hole1upndownatpmt (integer)
hole1upndownmade (integer)
hole1sandsaveatpmt (integer)
hole1sandsavemade (integer)
hole1putts (integer)....etc for the other 18 holes.

I have a second table called courseTable which has fields like:
courseID (autonumber primary key)
coursename (text)
hole1par (integer)
hole1distance (integer)
hole1handicap (integer)...etc for other 18 holes.

The form pulls in both tables linked by courseref to courseid and I
want it to show lowscore, averagescore, highscore, %fairways hit,
%greens in regulation, %greens after hitting fairway, average putts,
average putts after hitting green in regulation, %up and downs made, %
sandsaves made all for each hole, the first nine holes, the second nine
holes and the total 18 holes. This all adds up to lots of calculations
and lots of textboxes.

I tried doing the calculations in a query but had the same problem with
limitations on how many things a query can calculate.

Can I have different textboxes on the same form linked to different
queries?? That would solve it as I could use several queries, but I do
not know how to do this.

Thanks for the suggestions,
Ian

pe***********@aol.com wrote:

Perhaps some of your calculations could be done in a query. Can you
post your table structures so we can understand a bit more what you're
trying to do?

[ian.davies52]

This is a golf stats database for a golf society. I have one table
called roundTable that has the following fields:
roundID (autonumber primary key),
courseref (integer)
date (date)
teeused (integer)
conditions (text)
hole1score (interger)
hole1fairwayhit (yes/no)
hole1greeninreg (yes/no)
hole1upndownatpmt (integer)
hole1upndownmade (integer)
hole1sandsaveatpmt (integer)
hole1sandsavemade (integer)
hole1putts (integer)....etc for the other 18 holes.

I have a second table called courseTable which has fields like:
courseID (autonumber primary key)
coursename (text)
hole1par (integer)
hole1distance (integer)
hole1handicap (integer)...etc for other 18 holes.

The form pulls in both tables linked by courseref to courseid and I
want it to show lowscore, averagescore, highscore, %fairways hit,
%greens in regulation, %greens after hitting fairway, average putts,
average putts after hitting green in regulation, %up and downs made, %
sandsaves made all for each hole, the first nine holes, the second nine
holes and the total 18 holes. This all adds up to lots of calculations
and lots of textboxes.

I tried doing the calculations in a query but had the same problem with
limitations on how many things a query can calculate.

Can I have different textboxes on the same form linked to different
queries?? That would solve it as I could use several queries, but I do
not know how to do this.

Thanks for the suggestions,
Ian

pe***********@aol.com wrote:

Perhaps some of your calculations could be done in a query. Can you
post your table structures so we can understand a bit more what you're
trying to do?

[Justin Hoffman]

<ia**********@btopenworld.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

This is a golf stats database for a golf society. I have one table
called roundTable that has the following fields:
roundID (autonumber primary key),
courseref (integer)
date (date)
teeused (integer)
conditions (text)
hole1score (interger)
hole1fairwayhit (yes/no)
hole1greeninreg (yes/no)
hole1upndownatpmt (integer)
hole1upndownmade (integer)
hole1sandsaveatpmt (integer)
hole1sandsavemade (integer)
hole1putts (integer)....etc for the other 18 holes.

I have a second table called courseTable which has fields like:
courseID (autonumber primary key)
coursename (text)
hole1par (integer)
hole1distance (integer)
hole1handicap (integer)...etc for other 18 holes.

The form pulls in both tables linked by courseref to courseid and I
want it to show lowscore, averagescore, highscore, %fairways hit,
%greens in regulation, %greens after hitting fairway, average putts,
average putts after hitting green in regulation, %up and downs made, %
sandsaves made all for each hole, the first nine holes, the second nine
holes and the total 18 holes. This all adds up to lots of calculations
and lots of textboxes.

I tried doing the calculations in a query but had the same problem with
limitations on how many things a query can calculate.

Can I have different textboxes on the same form linked to different
queries?? That would solve it as I could use several queries, but I do
not know how to do this.

Thanks for the suggestions,
Ian

pe***********@aol.com wrote:

Perhaps some of your calculations could be done in a query. Can you
post your table structures so we can understand a bit more what you're
trying to do?

I don't have much idea about golf - so perhaps you will get a better answer.
But as others have pointed out, you have got far too many fields in your
tables and not enough rows. For example, roundTable (if I understand
correctly) has fields "hole1score", "hole2score", "hole3score", etc whereas
you should have fields "holenumber" and "holescore" with 18 rows for each
single row you currently have. In other words, you tables should be long
and skinny, not short and fat.

[ian.davies52]

Thanks for the suggestion. I might look into that if I come across any
further problems or for future databases, but for now I have got the
form working (I'm new to Access and databases in general, and the golf
stats project is how I'm learning).

Turned out there were a couple of problems that were causing the error.
I went back to the calculation in each textbox and when I broke it down
I found that I was using a table field that wasn't included my query. I
added the field to my query and it fixed that textbox, but the whole
form still wasn't working.

I tried reducing the number of textboxes on the form as suggested by
Salad and Larry and it worked, so I put half the textboxes in a new
subform and it all works just fine now.

Many thanks!

Ian

[penguin732901]

Dear Ian,

Changing your table isn't just a good idea if things don't work. It's
essential in order to maintain a workable database. Particularly if
this is a learning exercise, you would do well to learn proper database
structure.

To oversimplify:
If you have several fields with the same type of data (hole1 whatever,
hole2 whatever, etc.) this compels you to write the same code or query
functions over and over. If you had one table with the specific types
of info you need to know about a hole, (plus roundID) you could query
that table once with the calculations. Your form or report would have
a row for each hole with the calculations, and you could summarize
(add, average etc) at the bottom of the particular game.

You would also be able to compile statistics such as "what is the
average no of strokes on hole 1?" from the whole table, you could break
it down by conditions. etc.

You _might_ be able to have Access restructure the tables for you. I'd
strongly suggest you make a copy of your database and try
tools\analyze\table.

Although one might presume that everyone plays an entire round of golf
(although it must sometimes be otherwise), if you think about other
applications (donors, purchases) you would not want to have a table
with donation1, date1, type1, donation2, date2, type2 etc. What
happens when you run out of fields, and why should you have ten or
twenty empty fields for the guy who only uses one? Clearly this
structure makes not sense, but demands
personID Donationdate donationamt etc.

In other words, any time there can be zero, one or many of something,
it should get its own table. While you might say golf games are
finite, surely there could be 9 or 18 hole rounds?

Good luck!