Solved

Access Report - Too Many Fields

Posted on 2008-10-16
13
1,085 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
  • 7
  • 3
  • 3
13 Comments
 
LVL 26

Accepted Solution

by:
dannywareham earned 250 total points
Comment Utility
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
Comment Utility
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 65

Expert Comment

by:Jim Horn
Comment Utility
I bet your report looks like it was designed by a cheetah with ADHD on meth.
0
 
LVL 26

Expert Comment

by:dannywareham
Comment Utility
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
Comment Utility
lol @ Jim (howdo stranger!)
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:MFlach
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
A grade of "B"?
Could I ask what you did to resolve your issue?
0
 

Author Comment

by:MFlach
Comment Utility
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
Comment Utility
>>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 65

Expert Comment

by:Jim Horn
Comment Utility
(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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

763 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now