Solved

Access Report - Too Many Fields

Posted on 2008-10-16
13
1,131 Views
Last Modified: 2012-05-05
I have an access 2003 report that is ridiculously wide with a large amount of controls that's giving me a "Too Many Fields" error on preview.  Here is the breakdown:

Page Header - 109 Labels, 4 Textboxes
Detail - 56 Controls
Report Footer - 5 Labels, 240 Textboxes

All 240 of the fields in the report footer have a Sum() expression in the control Source.  If I remove 38 of the controls in the footer it runs fine.  If I add back one control and remove the formula from the Control Source it runs fine.  When I add back the formula, I get the error.

I've tried:
Compactin the DB
Rebuilding the report using cut and paste

I really need to get this report working.  Any thoughts?
0
Comment
Question by:MFlach
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
  • 3
13 Comments
 
LVL 26

Accepted Solution

by:
dannywareham earned 250 total points
ID: 22735520
Do you really need so many controls on one report!? That's a SHED load!
I believe the limit for controls on a form or report is 754.
However, this includes controls that have been added and then deleted (the count doesn't reset).

You normally get a message that says:
"The XYZ System can't create any more controls on this form or report. If you have deleted controls from this form or report in the past, you may be able to rename the form or report and add more controls to it."

Too many controls in this instance is probably because you have too many calculated fields (your Sum() fields are calculated).
I think the limit is 255 for calcualted - but you'll have massive resource drains with these.

It may be that you have to rename your report or import it into a new database. This will not fix the calculated problem - but will the first issue (if it is your issue).
But I would seriously reconsider having so many controls.
0
 

Author Comment

by:MFlach
ID: 22735634
Well, I haven't hit the 754 limit and I do need that many controls.  Is it documented that there is a limit to the number of calculated fields?
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 22735659
I bet your report looks like it was designed by a cheetah with ADHD on meth.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 26

Expert Comment

by:dannywareham
ID: 22735673
Is it documented?
No - I made that figure up to make you smile.

http://www.databasedev.co.uk/access_specifications.html
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 22735680
lol @ Jim (howdo stranger!)
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 22735743
On a serious note, danny is correct in that there is a limit to the number of controls you have on a single Access report.
Perhaps you can break up logical groupings into subreports to get around this.

>All 240 of the fields in the report footer have a Sum() expression in the control Source.
How do you go from 56 controls in the Detail section to 240 controls with =Sum([something]) in the footer?

{Hi Danny}
0
 

Author Comment

by:MFlach
ID: 22735753
Wow, your humor escapes me.  Now that your little reunion is over, let's get back to the issue at hand.  Is there a documented limit as to the number of calculated fields or did you just make that up to make me smile?  The link you gave me only mentions the limit on total controls.  I have not reached that limit as I can add more controls if I want to, just not ones with calculations.  Can someone with more knowledge on the subject answer the question?
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 22735850
MFlach
First, please remember that we are here as volunteers to assist you. We are not your staff.

Second of all, I have been away for almost a year and not said hi to a fellow Genius. I apologise if this interfered with your solution.

Thirdly, the solution has been posted in my first post:

You have Too Many Calculated Fields in your repot.
This is clear as when you remove them it solves the problem.
Also remember that if you have hundreds (literally) of controls on a report, it may not be the most robust and efficient design.

You should consider slimming it down, or, as Jim suggests, put some controls onto a subreport.
this will also correct your issue.

0
 
LVL 26

Expert Comment

by:dannywareham
ID: 22736099
I've had an idea.
It's a hack that you should never have to do, so I'll explain first.

When you have a report that's bound to a recordset and has calculated fields, Access always runs the query first.
If it's a dependent or nested query, this can take time. I the query(ies) also have calculated fields, this can take even longer. Plus, of course, if your dataset is big, it's longer still.

If you have calculated controls, these are calculatedon the results of the query during the On_Format() stage of the report opening.
For this reason, when you run your report, I'm guessing that you get a long lag time followed by what looks like the report opening, another wait and then it opens.

A query can handle 255 fields (calculated or otherwise). A report is only ever based on one (final) query output, so the limit to calculated fields is most likely to by 255 too (I've had a look on MSDN, but cannot find definitive answer).
255 "base" fields, plus 255 calculated gives you loads to work with.

if it's still not enough, you can step away from letting the report object do the work and use VBA.

Create a control on your report and name it "txtTest"
In VBA, in the On_Format event (which fires after the query has been loaded into your object), do your calculation there:

Me.txtTest = (Me.yourfield * me.yourotherfield)/Me.somethingelse  'etc

This will give you more flexibility

BUT - ideally, you should not have this many controls in the first place. Please look at your layout, because this will cause you performance issues as your data or database size increases.

0
 
LVL 26

Expert Comment

by:dannywareham
ID: 22767198
A grade of "B"?
Could I ask what you did to resolve your issue?
0
 

Author Comment

by:MFlach
ID: 22767345
I had to create a second report as a Summary Report.  While your answer was ultimately correct, I couldn't award an "A" for two reasons.  First, I was looking for some documentation to back up what I was observing.  Your conclusion of a maximum of 255 calculated fields was only based on observation, not on any documented limit.  I have learned during my years of programming that there is often more than one reason something does not work.  Finding a documented limit would ensure that I need not search for another cause.  Second, I believe the grade should reflect more than just the quality of the answer given but also the manner in which it is given.  You are clearly an intelligent person, but I felt you lacked an element of professionalism I have come to expect on this site.  
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 22767395
>>you lacked an element of professionalism I have come to expect on this site.  

The definition of "Professional" is "Performed by persons receiving pay"
All experts are volunteers. We are not paid and are under no obligation to provide users with an anwser.
We do so as to provide information and potential learnings for people that we like to help.

After reading some of your other question comments and acknowledging your feedback here, this is an option that I shall not be offering to you in future.

If this is what you "have come to expect on this site", I suggest that you either take a look at your own communication style, or chose another forum.

[unsub]
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 22767477
(Way off-topic)

danny - I'm going to propose a truce on this one.  The 'lack of professionalism' was clearly initiated by me, and the asker did nothing more than respond in kind, so I don't believe the future-question-option should be invoked here.  

-Jimbo
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question