Too Many Controls

[Klatuu]

I know the lifetime limit of controls on a report is 754. During
developement and experimenting, I hit that limit.
I made a copy under a different name.
I deleted the original.
I created a new report.
I copied the existing controls to the new report.
I copied all the code by event and pasted into the events.
the controls.count returns 516
I still am getting error 3190 - To Many Fields Defined.
Is there a limit on exiting fields on a report?

 

[Allen Browne]

Did you try compacting the database?

Interesting that it says too many fields, not too many controls. Any chance
this report is based on a crosstab query, so there could be more than 255
fields or not, depending on the criteria?

The other limit is the 4000-char in all fields, but that should give a
"record to wide error."

 

[Klatuu]

I did a compact and repair both before and after.
The recordsource is a union query with an additional two tables joined to
get descriptions from. The total fields in the final quey is 34.

The report was working fine, but the users wanted an additional subtoal
(another grouplevel). Adding the additional grouplevel added an additional
53 controls (including header and footer sections, text boxes, labels, and a
line).

Is there possibly another limit? As you said, it is interesting the error is
Too Many Fields rather than too many controls.

The idea I am toying with now is doing the subtotals in sub reports. The
problem here is that the filtering is very complex. Currently, the form has
seven cascading multi select list boxes and one two selection option group to
allow the user to make filtering selections, an option group to allow seven
different sortings, and seven check boxes to select subtotaling.

Based on the user's selection, I build a Where string for the ReportOpen
method. What I am wondering is how I will be able to impose the filtering on
the subforms and pass them the value to subtotoal on.

The other option, I think is to break the reports down into various versions
with fewer subtotal options and retrain the users. My problem with that is I
hate having to maintain several versions of one report - for obvious reasons.

Any thoughts will be greatly appreciated.

 

[Allen Browne]

Klatuu, I honestly don't know what limit you are hitting here. If anyone
else does, please jump in.

516 controls is a lot, but you should not be pushing the limit there.

34 fields in the query is fine.

With the UNION, you are opening several tables, but we are not seeing the
"too many databases" or "too many tables" errors, so presumably it's not
those.

Access does using the Sortin'n'Grouping info to create another level of
query into the data. I suppose that it is possible that the number of fields
returned by this internal process exceeds 255. I don't know enough about how
that actually works to know if it is going to create additional fields (such
as SumOfAmount) to handle the aggregation you need in your groupings. You
have probably seen the "Multi-level group by not allowed" error, so it
certainly does perform another level of aggregation beyond the report's
RecordSource.

Subreports are an obvious option when a report gets too involved, but as you
say, the filtering can be a nightmare. One option I keep up my sleeve for
situations where I'm stuck is to programmatically assign the SQL property of
the QueryDef that the subreport is based on before we OpenReport. You can
filter just about anything that way.

Another option when you are stuck is to create a temp table. It can be as
unnormalized as you want, and might reduce some of the load from the report.

 

[Duane Hookom]

It would be interesting to find out if removing non-bound controls (and
copying to a new report) would remove the error. This migh identify if the
problem was actually control or field related.

I regularly use the line method to "draw" boxes/lines in report sections
rather than using the line or other controls. For instance to draw a line at
the bottom of a group header,

Private Sub GroupHeader0_Print(Cancel As Integer, PrintCount As Integer)
Me.Line (0, Me.Height)-Step(Me.Width, 0)
End Sub

 

[Klatuu]

Thanks, Allen. It seems I have a talent for driving development products to
the limit. I found bugs in Tandem's Screen COBOL and in a short lived
language called TPL 700 from TI trying to do complex things.

I think I will try the temporary table approach. Given that Sorting and
Grouping creates additional queries, I can see where that could be a problem.

The Union Query joins two tables of 40 fields and extracts a total of 20
fields. The select query that uses the union query and two tables, uses 1
field from one of the tables. The other table is used basically as a pointer
to the other. There are 5 calculated fields in the select query.

Thanks for having a look.

 

[Klatuu]

Interesting idea, Duane; however, there are not enought un bound controls on
the report to get it down much. The total of all labels, and lines is about
30.

It is interesting that before I added the last grouplevel, the number of
controls was 472 and it was working just fine.