dynamic report exceeds the nr of allowed control elements

[kalakeli]

I am running some complex scripts that calculate and filter data into several tables. To finish the task, I want to create reports from these tables. The nr varies depending on the user input, so there can be anything from 1 to 1500 reports.
The header is put together from metadata in one table, the detail section gets its data from one or possible two queries on other tables. To help you understand, the data comes from waterbird census. I am determining whether and what standards a certain area is satisfying. And the resulting reports need to be written automatically and also saved as pdf.
Well, this seems to work alright but now I got the error message that there are too many control elements on a report. And I am at a loss how to avoid this. Maybe I am approaching from the wrong side so I hope that you can help me. Is there a proper way how to dyanmically set up reports? When I take the tabledata and create a single report for the same SPA that throws the error, it works, so I tried to figure out the code that Access uses to set together a report but I could not find it.
Any ideas?

 

[RainLover]

The following is the stats on reports.

MaximumNumber of characters in a label2,048Number of characters in a text box65,535Form or report width22 in. (55.87 cm)Section height22 in. (55.87 cm)Height of all sections plus section headers (in Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.))200 in. (508 cm)Number of levels of nested forms or reports7Number of fields or expressions you can sort or group on in a report10Number of headers and footers in a report1 report header/footer; 1 page header/footer; 10 group headers/footersNumber of printed pages in a report65,536Number of controls and sections you can add over the lifetime of the form or report754Number of characters in an SQL statement that serves as the Recordsource or Rowsource property of a form, report, or control (both .mdb and .adp)32,750

 

[kalakeli]

Thank you, but what I mean is something else.
When I iterate through the recordset, the system tells me that there are too many controls on the report.
The problem is that I have 9 columns and the number of rows varies with a maximum of 75. And that is apparently too much and I wonder how to make it work ..

 

[RainLover]

Thank you.

Now I understand the question.

Well for a start you can't do anything about the limitations of Access.

The only work around I can think of is to use a Report with a Sub Report. Similar to a Form Sub Form situation.

Perhaps there is also another way but I don't know the type of info you are reporting on so this may not be applicable. And that is to Concatenate some of the fields.

Please write back with your thoughts as someone else may glean some more information from that.

 

[RainLover]

The specs say:

Number of controls and sections you can add over the lifetime of the form or report 754.

If you have 9 * 75 then that is 675 which even with other things like headers, it falls within your limits.

So now I am even more confused.

Not sure what is meant by the lifetime of a report.

I will sleep on it.

 

[RainLover]

I posted too soon.

I only counted Text Boxes. I forgot the Labels.

 

[RainLover]

Are you there.

I must be on the wrong track. What I am reading that you want is not logiacl.

If you created a Temporary Table, instead of a Report, what would it look lke.

Especially, how many Fields and how many Records.

 

[Beetle]

@Rain

Not sure what is meant by the lifetime of a report.

Deleted controls count against the "lifetime" limit. So, if you redesigned a report over and over, adding new controls, deleting old ones, the old ones still count against the limit. I believe this is reset on a C&R but I don't know for sure (never really had to worry about it because I've never gotten anywhere near the limit for any object).

As for the OP, I'm with you. I can't figure out what they're doing here.

 

[RainLover]

Sean

I am glad you jumped in. I took a guess at the Lifetime meaning but was not sure.

This now complicates things a little more. If the user creates and destroys controls the limit can be reached much easier.

Thanks for the imput, and don't be a stranger with this thread.

 

[Beetle]

If the user creates and destroys controls the limit can be reached much easier.

True, but if the basic structure of the Record Source hasn't changed, just the data re-calculated, I wouldn't see a need to re-design the report on the fly.

 

[kalakeli]

Thanks a lot for the answers. I have not figured out how to do it yet. The idea with concatenating the output came to me, too, but then the table structure would be lost. Or - this question shows my lack of knowledge - is there a way to add tabs that would allow the data to look tabular?

@Beetle
I am not redesigning the same report. Instead, I am iterating through one table and for each dataset I am creating a new report. For each report I am then taking data from two newly created tables and am putting it in a table structure on the report.
It took me AGES to get to this point so I am really getting frustrated with this..

The idea with a sub-report sounds good, too. How would this work? I would create my report on the fly, then in the middle of this, create a new report?


best, karsten

 

[RainLover]

Thanks a lot for the answers. I have not figured out how to do it yet. The idea with concatenating the output came to me, too, but then the table structure would be lost. Or - this question shows my lack of knowledge - is there a way to add tabs that would allow the data to look tabular?

@Beetle
I am not redesigning the same report. Instead, I am iterating through one table and for each dataset I am creating a new report. For each report I am then taking data from two newly created tables and am putting it in a table structure on the report.
It took me AGES to get to this point so I am really getting frustrated with this..

The idea with a sub-report sounds good, too. How would this work? I would create my report on the fly, then in the middle of this, create a new report?


best, karsten

Yes you would create a sub Report and attach on the fly.

There is an option in Reports to create Columns. Search Help as I have not used this feature in Years.

A new thought. Could you do this with several reports, each showing different data.

You did not attach your Table Designs. This could help a lot in solving your problem.

 

[kalakeli]

OK, sorry the answer comes a bit late, different things to do as well..
I concatenated two labels together and, luckily, now the whole report gets written. But of course the issue remains and I am not very happy with the solution. It works now but it seems risky

So this is my table structure for the main resulting table that I use besides two others for the report. But the other two are not creating the problem.

tblAssessment

assessPKID (Number)
protectedSiteID (Text)
speciesID (Number)
populationID (Number)
maxCount (Number)
maxYear (Number)
maxAvg (Number)
international (Text)
national (Text)
land (Text)
regional (Text)
lokal (Text)
overallBest (Text)

I know there should be no repetitive data and with the SPA-ID there is. But there are quite a few tables that are created in the end and as I can use a select clause to get a distinct list of SPAs in the table, I also put them in here.
I then iterate through this list and for each SPA get all the species with the respective numbers. When I hit upon an entry in the populationID field, I create a new recordsource for the appropriate name. Hmm .. now that I am writing this, I see that I should put not the number in the assessment table but the name of the population and thus save me one recordsource. Hmm .. sometimes you just have to talk about it
But as I am iterating through the first list, I still need to create the reports dynamically and print them as PDF anyway. By the way, there are sometimes - not always - some blank pages in the printout. Any ideas besides the obvious, that the width of the report is wider than the page?

 

[Pat Hartman]

Have you tried creating a crosstab? If you have several fields to "pivot", you can create a crosstab for each column and then create a query that joins the several crosstab queries.

If the crosstab doesn't work for you, you could write the matrix you are building to a temp table and then create a report based on the temp table.

The last option (I'm sure there are more) is to automate Excel and write each cell of the matrix out to a worksheet which you can format later so that it looks pretty.

 

[kalakeli]

So, a week has passed, I have not figured out how to circumpass access' restriction on the number of labels. What did work, though, was this:
- I created a table into which I copied all my results, then added fields for all descriptive information
- I filled these fields with the appropriate data, thus breaking quite a few database development laws, e.g. in one column with 110 datasets it simply says 110 times "international".
- I prepared a report using that table as a reference
-> miraculously, no more complaints about too many controls!

I programmatically then deleted the table in the beginning of my calculations, then created it on the fly for each area, created a report, saved it as PDF, deleted it again and thus iterated through up to 75 areas.

Well, thanks again for your help and support!