Solved

Access Report - Too Many Fields

Posted on 2008-10-16
13
1,110 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
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 65

Expert Comment

by:Jim Horn
ID: 22735659
I bet your report looks like it was designed by a cheetah with ADHD on meth.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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 65

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 65

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

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…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

832 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