Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Unhappy Unanswered: Too Many Controls on Report

    This problem may be unique to me, but I first saw it in Access 97 and now in Access 2000 as well.

    Each month 8 new fields are added to a table, and named for the month and year. History is save, but not used when it is older than 13 months. I have a series of reports that have controls (fields) with names that represent dated information, for example 1201R, 1201V...0102R 0102V...0105R 0105V, represents data from December 2001, January 2002 and May 2002. In the report, there are 13 months of these fields, in total 182 fields. There are several dozen labels and other fields at the top of the report that I do not edit. The 183 fields are deleted each month and I copy new ones from a temporary report file that I have edited for the current month.

    After so many months of doing this, when I attempt to paste the fields into a report, the error message says 'Microsoft Access can't create anymore controls on this form or report.' and offers the solution of renaming the file to solve the problem, which, after a half dozen trys, does not work.

    When this happened in Access 97, I rebuilt the reports from new report files, which took several hours. Now, several months later, I have the same problem. It appears there is a limit to the number of controls that can be added to a report and deleted controls are included in the count.
    I have looked in Access 97 technical books and did not run across the subject of control limitation.

    Someone may say 'rebuld the reports so they never have to be changed'. The database was inherited and set up so the field names identify the month and year of the data, and rebuilding will take a lot of work. Each month I have to delete the oldest month from reports and add the current month, and I find it easier to put all the new information into a temporary report and just copy and replace the fields in 10 reports.

    Some may suggest 'create and set aside 10 blank reports for use when the problem occurs again.'

    First, I am wondering if any have had a similar experience and have found a way to delete and add controls to the same report and work around the limits Access puts on the number of controls.

    Thanks for your advice and opinions.

  2. #2
    Join Date
    Jul 2002
    Location
    Australia
    Posts
    147
    If possible, I'd say speak to your DBA and get him to redesign your tables!

    Adding (date type) fields every month is pretty pointless. All you need is a generic field and an idenitifer field where you can input the value to keep the records unique.

    I know this is not an easy fix for your problem, but as soon as you correctly normalise the database the sooner you will rid yourself of these types of problems.

    Cheers,
    Andrew
    There have been many posts made throughout the world.
    This was one of them.

  3. #3
    Join Date
    Feb 2002
    Posts
    64

    Question ???

    This might be a ridiculous suggestion, but have you tried using a subreport?

  4. #4
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    FYI:
    I have come across similar problems when building forms to represent clinical questionnaires. Some forms had so many questions (like- check all the apply") that the form could no longer accept new controls. Deleting controls doesn't gain anything, since Access keeps track of deleted controls also. I was forced to delete, then copy to a new form then try to add. Even so, I would eventually hit the ceiling. I wrote a snippet of code to count the controls on the form and experimented by making forms with the various available controls. Nothing seemed intuitive about the number of controls a form would max out at. I'd be interested on any info in this area. Thanks
    Last edited by jpshay; 07-16-02 at 13:40.
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  5. #5
    Join Date
    Jul 2002
    Posts
    6
    most likly the number is 255. This is the magic number in most of microsofts applications. I think it has something to do with memory locations.

  6. #6
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    No the number ranged above 700 if I remember. It was not the same, it varied with different controls. Tables, however, can accept only 255 (or 256) fields. That is static.
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  7. #7
    Join Date
    Mar 2002
    Location
    Sacramento, CA
    Posts
    120

    Per help files

    Form and report specifications:

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

    Michael

  8. #8
    Join Date
    Jul 2002
    Posts
    22

    Smile

    You can place only 783 objects on your form/report as long as you haven’t imported that form/report.
    So in your problem all u need to do is to each time, before you start to modify fields on that report simply import (thus not copy!) it to your database. (and delete the old one if u don’t need).

    Why it works? Because Access does not take into consideration deleted fields while importing.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •