One for the MVPs?

[Rob Parker]

I posted the following in the access.reports newsgroup over two days ago,
and there's not been a single response there. Perhaps someone here can help
me ...

Can anyone explain to me why I get a "Too many fields defined" error message
from a Report, which is based on the same recordsource as a form, and was
generated by saving the form as a report (with some subsequent tweaking to
remove form-only controls, change typeface, etc). The form works fine, the
report doesn't - it gives the error message. (Using Access 97)

The underlying query (based on a set of cross-tab queries, all based on a
single table but with a couple of fields from joined tables) contains 167
fields. The form (and report) present a table summarizing 15 years cost
data in 10 different categories fro each record, together with a total for
each row; the form/report footer section contain Sum fields for each of
these fields. In total, there are approx. 380 controls on both the form and
in the report (well under the 754 limit).

I've compacted (and repaired) the database - still the same error. I've cut
and pasted the controls from the report into a new report - same error. I've
re-generated the report from the form (via "Save as report"), and with
absolutely no tweaking at all it still gives the same error.

I finally got it to work - the application had to be delivered today - by
designing a separate subreport containing the Sum fields, and including that
in the report footer. Not pretty, but it worked.

What I really would like to know is why the form works, but the report
doesn't.

TIA,

Rob

 

[Bob Howard]

A shot in the dark...

Is it possible that each control is being counted twice in a report? It's
interesting that 380 is just over half of the magic 754. Make a duplicate
of the report and delete 25 or 30 controls. Now see what happens.

Like I said --- a shot in the dark!

 

[Rob Parker]

Thanks for the reply. I've just re-generated the report from the form,
deleted the selection combo-boxes etc, and I've got 370 controls in the
report (17 in the header, 181 in the detail, and 172 in the footer). That's
just under half of 754. And, sadly, the report still tells me "Too many
fields defined".

Sadly, the shot in the dark missed. As some of us say here in Oz ...
"Bugger!"

Rob

 

[Guest]

Does it make any difference if the header and footer controls are in the form
header/footer vs. the page header/footer before you save the form as a
report? Or on, the other side, if they are in report header/footer vs. page
header/footer?

 

[Sandra Daigle]

Hi Rob,

Are you including your labels in the control count? (Probably a silly
question but worth asking anyway)

One thing you might try is the undocumented Save

 

[Sandra Daigle]

Trying again, sorry -

Are you including your labels in the control count? (Probably a silly
question but worth asking anyway)

One thing you might try is the undocumented SaveAsText. I haven't used it
for reports but I have used it on a form to reset the internal control
counter (if there is such a thing).

Sub FormSaveRestore()
Application.SaveAsText acReport, "ReportA", "c:\temp\FormA.txt"
Application.LoadFromText acReport, "ReportA2", "c:\temp\FormA.txt"
End Sub

 

[Rob Parker]

The form header/footer controls were copied to the report header/footer
sections when I saved the form as a report (I never use page header/footers
on forms). In the report, if I move them from the report header/footer to
the page header/footer respectively, the report does open without the "Too
many fields" error. Unfortunately, it also leads to #Error in all the
textboxes with "=Sum(...)" entries in the page footer (and it's not where
the totals must be - but for the purpose of shedding some light, yes, it
does make a difference).

Rob

 

[Rob Parker]

Hi Sandra,

Yes, I'm including labels in the control count. There are 17 labels in the
header (all the controls in the header), 14 labels in the detail section,
and 13 labels in the footer. Why does (might?) it matter whether they are
labels or bound controls (all textboxes, in this case)?

I've tried playing with your SaveAsText suggestion, with marked lack of
success so far - it keeps crashing Access! But I have found that I can't
SaveAsText the original form, and LoadAsText it as a report - Access won't
let you change the object type in the operations. However, doing this with
the generated report has no effect - same old error message.

There's some new information in another reply of mine elsewhere in this
split thread, to a post by BruceM. In essence, moving the controls from the
Report Footer to the Page Footer allows the report to open, but with #Errors
because of the =Sum() expressions.

Rob

Trying again, sorry -

Are you including your labels in the control count? (Probably a silly
question but worth asking anyway)

One thing you might try is the undocumented SaveAsText. I haven't used it
for reports but I have used it on a form to reset the internal control
counter (if there is such a thing).

Sub FormSaveRestore()
Application.SaveAsText acReport, "ReportA", "c:\temp\FormA.txt"
Application.LoadFromText acReport, "ReportA2", "c:\temp\FormA.txt"
End Sub

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

I posted the following in the access.reports newsgroup over two days
ago, and there's not been a single response there. Perhaps someone
here can help me ...

Can anyone explain to me why I get a "Too many fields defined" error
message from a Report, which is based on the same recordsource as a
form, and was generated by saving the form as a report (with some
subsequent tweaking to remove form-only controls, change typeface,
etc). The form works fine, the report doesn't - it gives the error
message. (Using Access 97)

The underlying query (based on a set of cross-tab queries, all based
on a single table but with a couple of fields from joined tables)
contains 167 fields. The form (and report) present a table
summarizing 15 years cost data in 10 different categories fro each
record, together with a total for each row; the form/report footer
section contain Sum fields for each of these fields. In total, there
are approx. 380 controls on both the form and in the report (well
under the 754 limit).

I've compacted (and repaired) the database - still the same error.
I've cut and pasted the controls from the report into a new report -
same error. I've re-generated the report from the form (via "Save as
report"), and with absolutely no tweaking at all it still gives the
same error.

I finally got it to work - the application had to be delivered today
- by designing a separate subreport containing the Sum fields, and
including that in the report footer. Not pretty, but it worked.

What I really would like to know is why the form works, but the report
doesn't.

TIA,

Rob

 

[Sandra Daigle]

Well it was worth a shot - I'm not really sure what else might be going on.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Hi Sandra,

Yes, I'm including labels in the control count. There are 17 labels
in the header (all the controls in the header), 14 labels in the
detail section, and 13 labels in the footer. Why does (might?) it
matter whether they are labels or bound controls (all textboxes, in
this case)?

I've tried playing with your SaveAsText suggestion, with marked lack
of success so far - it keeps crashing Access! But I have found that
I can't SaveAsText the original form, and LoadAsText it as a report -
Access won't let you change the object type in the operations.
However, doing this with the generated report has no effect - same
old error message.

There's some new information in another reply of mine elsewhere in
this split thread, to a post by BruceM. In essence, moving the
controls from the Report Footer to the Page Footer allows the report
to open, but with #Errors because of the =Sum() expressions.

Rob

Trying again, sorry -

Are you including your labels in the control count? (Probably a silly
question but worth asking anyway)

One thing you might try is the undocumented SaveAsText. I haven't
used it for reports but I have used it on a form to reset the
internal control counter (if there is such a thing).

Sub FormSaveRestore()
Application.SaveAsText acReport, "ReportA", "c:\temp\FormA.txt"
Application.LoadFromText acReport, "ReportA2",
"c:\temp\FormA.txt" End Sub

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

I posted the following in the access.reports newsgroup over two days
ago, and there's not been a single response there. Perhaps someone
here can help me ...

Can anyone explain to me why I get a "Too many fields defined" error
message from a Report, which is based on the same recordsource as a
form, and was generated by saving the form as a report (with some
subsequent tweaking to remove form-only controls, change typeface,
etc). The form works fine, the report doesn't - it gives the error
message. (Using Access 97)

The underlying query (based on a set of cross-tab queries, all based
on a single table but with a couple of fields from joined tables)
contains 167 fields. The form (and report) present a table
summarizing 15 years cost data in 10 different categories fro each
record, together with a total for each row; the form/report footer
section contain Sum fields for each of these fields. In total,
there are approx. 380 controls on both the form and in the report
(well under the 754 limit).

I've compacted (and repaired) the database - still the same error.
I've cut and pasted the controls from the report into a new report -
same error. I've re-generated the report from the form (via "Save as
report"), and with absolutely no tweaking at all it still gives the
same error.

I finally got it to work - the application had to be delivered today
- by designing a separate subreport containing the Sum fields, and
including that in the report footer. Not pretty, but it worked.

What I really would like to know is why the form works, but the
report doesn't.

TIA,

Rob

 

[Rob Parker]

Thanks for the interest. Seems (from responses so far, in both this and the
reports newsgroup) that no-one knows what the problem is, or if there's an
alternative to my kludge subreport for totals workaround.

C'est la vie

BTW, have you seen my other question, re copy/paste of a record? I've not
got a useable answer to that one yet either ;-)

Where can I get a definitive answer, if no-one in the newsgroup(s) can help?

Rob

Well it was worth a shot - I'm not really sure what else might be going

on.
<snip>

 

[Guest]

Hmm. I wonder if there is a limit to the number of controls in a header or
footer. I know there is for a form or report, but I wonder if it matters
where they are. Have you tried saving the form as a form? Frankly, I'm just
fishing around here (as if I needed to explain that). One other thing I
might try if it were my project would be to haul all of the controls onto the
detail section, close the headers and footers, then try saving. If it works,
maybe they could be redistributed to the report header and footer.

 

[Guest]

Hi, Rob.

Where can I get a definitive answer, if no-one in the newsgroup(s) can help?

<Cough!> No one?!! Many of the people who answer questions in the
newsgroups can troubleshoot others' database problems most of the time with
just a few clues and after asking a few of the right questions -- without
ever laying eyes on the database itself. If you think about it, that takes
incredible talent. Many of these folks work with Access for a living, and
what assistance they offer to people in need in these newsgroups without
charge is only a fraction of the spectacular services they offer to their
paying clients.

If I needed a definitive answer but didn't already know how to troubleshoot
tricky problems in Access, I'd put my go-fasters on my feet and zoom over to
the reports newsgroup, do some research to find the consultants who can
obviously "walk the walk" and hire one of them for a fraction of what
Microsoft charges for tech support. These experts can give a lot more
valuable advice about your database by actually looking at it than they can
from reading short descriptions of what the problem is.

And remember that the data source for a report and a form can be the same,
but the resulting data set for the report can be a little different to
accommodate the formatting. Check to ensure that no formatted record exceeds
2,000 characters in the report (excluding Memo and OLE fields), because this
too will result in the "too many fields defined" error message in the report,
even with far fewer than 255 fields.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

 

[tina]

i heartily agree with Gunny. the deductive abilities of many of the MVPs and
other expert developers in these newsgroups is nothing short of amazing. and
that so many of them give so freely of their time and expertise in the same
field where they make their living, is incredibly generous. sometimes the
real head-breaker problems are due to underlying design issues that simply
can't be unraveled without hands-on examination. there are a number of
highly skilled developers here that i would recommend my company hire, in a
heartbeat, to troubleshoot a database problem that i was unable to solve
with the assistance of these volunteers.

 

[Mike Painter]

If this is a "Too many fields defined" problem the answer and solution can
be found on the MSFT knowledge base and has been covered in many Access
groups several time in the last few months.

A Google search will reveal the answers.

I suspect that sometimes everybody thinks to themselves "I answered that
twice in the last week, let somebody else do it this time."

A non descriptive subject is also a reason to ignore what *may* be, but
rarely is a problem for the smart guys.

 

[Rob Parker]

Hi Gunny and Tina,

I certainly didn't mean to imply that the quality of answers in these
newsgroups was poor - quite the contrary. I'm extremely impressed by the
knowledge (and patience!) of the many responders to problems posted here.
And there was an IF in my statement. It's looking like no-one is going to
give me a definitive answer to this particular problem; as I said in my
original post in this group, this problem was posted for over two days in
the Reports newsgroup with not a single response; although there have been a
couple of responses here to date, they've been suggestions rather than a
definitive answer.

Back to the technical stuff: the form and report are based on the same
query, and the dataset from that query contains almost all numeric fields;
to be precise, there are 2 long integers, 4 text fields (none of which
exceeds 255 chars with the current data, although 2 of these fields contain
concatenation expressions to join 15 other text fields (potentially this
could lead to over 2000 characters, but in practice it's extremely unlikely
to do so, since for any records only between 1 and three of these fields
will actually contain any data)), and the remaining 161 fields are either
single or double. The formatting of these fields for display is the same in
both the form and the report.

Rob

 

[Rob Parker]

Hi Mike,

Thanks for taking time to respond. Yes, this is a "Too many fields defined"
problem, and I've not found an answer to it in the MS Knowledge Base, or by
searching the Access groups (for this problem, I've tried the .reports,
..forms, and even the .formscoding newsgroups. Sure I've found problems (and
solutions) for some "Too many fields defined" problems - unfortunately, I
haven't found a soultion to my particular problem (the form works, the
report doesn't - unless I move a set of fields from the report footer to the
page footer (which is not an acceptable solution, as they are totals
fields). If you know of one I've missed, I'd appreciate a specific
reference.

And yes, my subject line here could have been more descriptive; however, I'd
had the same post in the .reports newsgroup for over two days, with a
definitive subject line, and received not a single response. I changed the
subject line here to (hopefully) provoke some curiousity. I have at least
received some replies here, even if no-one (to date) has been able to
provide a definitive answer.

Rob

 

['69 Camaro]

Hi, Rob.

It's looking like no-one is going to
give me a definitive answer to this particular problem . . . although
there have been a
couple of responses here to date, they've been suggestions rather than a
definitive answer.

I think you're expecting more from the newsgroup experience than it's
designed to give. Suggestions from experienced people on how to solve the
questioner's problem is the goal, not the consolation prize. No one who
uses this forum is an employee of a company providing free computer tech
support to everyone with Internet connections. UseNet is peer-to-peer
support from the volunteers in the community at large, so no one is required
to give definitive answers on any topic or to give free labor to fix the
database problems of those having trouble.

If you post a question in a newsgroup and receive a definitive answer, thank
the contributor and consider yourself lucky, because you could just have
easily received WAG's or no responses at all. Replies to your question
could come from a rocket scientist, a 15-year-old high school student, your
neighbor down the street, or someone who deleted the Internet this morning
while trying to get his E-mail to work, but has just discovered he can post
messages on UseNet and is wondering how "Y'all are getting along now that
the Internet is gone." Occasionally, a troll or Lord God King Bufu may stop
by to harrass or offer some advice, but thankfully that doesn't happen very
often in these Access newsgroups.

The Access newsgroups are fortunate in that there are enough experts to give
one or more competent responses, excellent advice, code samples, and links
to relevant Web sites to most of the questions posted here. I can't speak
for other newsgroups, but the folks who respond to questions in the Access
forums are genuinely interested in helping the questioner solve the problem.
This seems to be the case for most of Microsoft's technology-oriented
newsgroups. Unfortunately, the need for assistance is greater than the
voluntary manpower available. With an average of more than 250 new
questions posted daily to the Access newsgroups and ongoing discussions from
previous days, there's a limit to the amount of time any one expert can
devote to assisting with any particular database problem.

The system works fairly well because there are a good number of experts
willing to help others and who know that sharing their level of knowledge
will enrich the community. However, there's no guarantee that definitive
answers will be given for any particular question. If you want a guaranteed
answer, then consider hiring an expert because an expert _can_ guarantee
that you'll be satisfied with his advice -- or your money back.

although 2 of these fields contain
concatenation expressions to join 15 other text fields (potentially this
could lead to over 2000 characters, but in practice it's extremely
unlikely
to do so

"Extremely unlikely" won't cut it. It must be completely impossible. If
even one record exceeds the 2,000 character limit, then the "too many fields
defined" error message will appear.

The formatting of these fields for display is the same in
both the form and the report.

The page layout and spacing of controls may look the same in both the report
and in the form, but the formatting is different. The formatting
characteristics of a report distinguish it from a form. If the formatting
were the same in these two objects, then the two objects would be
interchangeable. They aren't.

BTW, have you seen my other question, re copy/paste of a
record? I've not got a useable answer to that one yet either ;-)

You didn't mention a subform or master/detail tables, but that would be the
easiest way outside of the bookmark bug to pull off the problem you are
encountering. I can think of a few ways for the subform to overwrite the
wrong record when the combo box is used to make selections for the new
record, either by using a fan trap database design, or by using a
misconfigured form/subform combination, or by setting up the relations
incorrectly. Reading current records in the table works fine, but updating
and/or adding new records (depending upon which one of the situations I
mentioned is the cause) causes record overwrite problems. In any case, the
record cursor is not pointing to the record where you think it's pointing
when the paste is made.

Check whether foreign keys are involved in this table and if so, find out
what the values of these foreign keys are before pasting the record, what
the values are for the copied record, and what the values are after pasting
the record. You'll make an interesting discovery, because the mysterious
random record isn't so random after all.

I changed the
subject line here to (hopefully) provoke some curiousity.

The advantage goes to descriptive subject lines. Your new subject line
probably didn't get as much attention as you had hoped for because people
often ignore these types of posts. Too often, they degenerate into
mud-slinging such as the following venom-spewing episode from one irate
poster who didn't get the answer she wanted:

http://groups.google.es/groups?hl=en&lr=&th=f4fb5dd6acb47156&rnum=1

If you want to increase your chances of getting a quick answer to your
question, then consider using Microsoft's Web portal to the newsgroups on
the following Web page to post questions:

http://www.microsoft.com/office/com...mspx?dg=microsoft.public.access&lang=en&cr=US

In general, the easy-to-answer questions and the clearly stated questions
are answered the quickest. The questions that usually take the longest to
receive the first answer are the questions posted from outside of
Microsoft's Web portal, such as from regular newsreaders (as you've used)
and AccessMonster.com. It seems that more people are willing to answer the
questions posted from Microsoft's Web portal, since a potential reward is
involved. Recognition is received by contributors who are members of the
Web portal community and who have a considerable number of their replies
marked as answers.

Very few questioners have caught on to this phenomenon yet, so questioners
who have a history of marking replies as answers and who post a question
from the Web portal between 10 A.M. and 4 P.M. Eastern time (Zulu - 4) on a
weekday will usually evoke the quickest responses, since the largest pool of
responders is answering these questions.

If you do use the Web portal, be aware that there are a number of bugs which
Microsoft has been working on, so a good number of outages have been
occurring regularly. One of the bugs that hasn't been fixed yet is the
badge indicating a contributor's expertise level (the number of replies
marked as answers by the person who posted the question), so no one in the
Access newsgroups has a badge yet, although several people have earned the
first level badge. The first Access contributor to earn the first level
badge was Kevin Sprinkel, so congratulations to him.

Of note is one particular contributor who started posting replies to
questions several weeks ago and is just about to earn the first level
badge -- which I find rather odd considering the rate of his replies being
marked as answers is twice as fast as our top contributors who have been
contributing their fantastic answers to the newsgroups for many years and
have earned the title of MVP. This would indicate that his answers are far
superior to these world class contributors, so as an Oracle DBA trying to
learn as much as I can about the problems my clients encounter with their
database applications, I did some research to see what more I could learn
about Access from his answers. Far too many of his replies indicate an
inexperienced Access developer, not someone who can "walk the walk," let
alone someone who is world class. So in the future when Microsoft gets the
bug fixed, if you see a badge next to the name of someone posting a reply in
Microsoft's Web portal, just think "caveat emptor."

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

 

[Rob Parker]

Hi Gunny,

Thanks for the reply, again. Some rather large snips below, and comments
(mostly general, rather than specific to my problems) in-line.

I think you're expecting more from the newsgroup experience than it's
designed to give.

Not expecting, just hoping ;-)

UseNet is peer-to-peer support from the volunteers in the community at

large,

I realise it's volunteer, peer-to-peer. Has been ever since I started using
it 15-20 years ago - can't remember exactly when, but about when gopher was
still around, and the internet (as it is today) was hardly much more than an
idea in Tim Berners-Lee's mind. News servers were more common that web
servers! And I really appreciate the almost complete absence of trolls,
flames and off-topics in the ms.public newsgroups.

"Extremely unlikely" won't cut it. It must be completely impossible. If
even one record exceeds the 2,000 character limit, then the "too many
fields defined" error message will appear.

Currently, there are no records for which this occurs. It's therefore not
what's causing the problem at the moment. And now that you've alerted me to
this particular problem, I can (and will) trap for it in the application.

The page layout and spacing of controls may look the same in both the report
and in the form, but the formatting is different. The formatting
characteristics of a report distinguish it from a form. If the formatting
were the same in these two objects, then the two objects would be
interchangeable. They aren't.

Sorry, I thought your comment about formatting was referring to the format
of the controls on the form/report, not the format of the form/report
itself; I do realise that they are not the same.

You didn't mention a subform or master/detail tables, but that would be the
easiest way outside of the bookmark bug to pull off the problem you are
encountering.

My copy/paste record problem is occurring in a main form, bound to a table,
with an inner join to another table which selects a subset of records for
the current month's orders. (I doubt that's the problem, but I'll check at
work tomorrow, by removing the join and putting a criteria directly on the
table in the form's recordsource.) There are no subform or master/detail
tables involved. If I can't find a way of trapping that the user is trying
to copy/paste an entire record - and neither I, with 3-4 years making a
living from this, after retiring from being a rocket-scientist ;-), nor the
experienced developer I'm working with have a way of doing so yet - then it
seems that the best I can do is educate the user(s).

Check whether foreign keys are involved in this table and if so, find out
what the values of these foreign keys are before pasting the record, what
the values are for the copied record, and what the values are after pasting
the record. You'll make an interesting discovery, because the mysterious
random record isn't so random after all.

No foreign keys involved. And there's a single primary key (autonumber) on
the table.

If you want to increase your chances of getting a quick answer to your
question, then consider using Microsoft's Web portal to the newsgroups on

The background info on the web portal was interesting; however, I'm not yet
prepared to forsake my plain vanilla newsreader - it's so much quicker and
easier. And I can't use the Web portal from work, since I can't register
for a MS Passport (or whatever it's called) from behind the firewall there.
I do use that site (or the Google groups) when I want to search (before
posting - for both my current problems).

I've seen the "Rate this answer" boxes while using the MS Web portal, but
haven't been able to use them, since I don't have a passport account. I
wasn't aware that they'll lead to "badging" of responders - that seems an
interesting approach, but one that's perhaps prone to manipulation. I've
got my own opinion of the worthiness of the comon responders, and agree with
your sentiment that some are better than others - and from my point of view
the MVPs lead the way, quite often by a long way ;-)

Again, thanks for the interest and comments,

Rob