Results 1 to 12 of 12

getting too many fields defined when I open my form

  1. #1
    chriswrcg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2018
    Posts
    140

    getting too many fields defined when I open my form

    I was trying to build a form for data entry but put to many fields in. I have compacted and repair the database but it still will not open the form in form view. I have manually counted and I have way less than 255 fields in the form. What can I do to open the form in form view again? I really don't want to have to rebuild it. It took 4 hours to get what I got now.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,278
    AFAIK, 255 is a table field limit. I believe the limit over the lifetime of a form is 754, so if you put 250 on, removed them all, recreated another 250 and did this until you exceeded the limit you would have this problem. I doubt you have done anything like that. Is there an error message and number? You might be misinterpreting the problem message.

    It may only be that the form is corrupt. You might be able to create a new blank one and copy/paste the controls from the problem form to the new one, or try a compact/repair on the db.
    Do you have Name Autocorrect turned on?

    EDIT - How many bound controls on the form? How many in total?
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.

  3. #3
    chriswrcg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2018
    Posts
    140
    The form is probably corrupt. I was rocking right along and all of a sudden the table I was pulling from stopped working. I got an error message that this table is not available for something. I forget exactly what it said but right after that I started getting the form has to many fields in it message. Ill try the copy and past thing and see if that helps. I have tried the compact/repair and that didn't work.

    Right now I have 95 bound controls on the form not including tags.

  4. #4
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,360
    If by tags you mean labels, then they too are controls, and need to be counted, so if you have cut and paste the whole lot a few times on the same form you may have approached the theoretical limit.

    (Still seems unlikely tbh)
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,278
    Everything gets counted - checkboxes, command buttons...
    Now I'm wondering if the underlying table or query is partly or wholly the problem. How many fields are in it?

  6. #6
    chriswrcg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2018
    Posts
    140
    Quote Originally Posted by Micron View Post
    Everything gets counted - checkboxes, command buttons...
    Now I'm wondering if the underlying table or query is partly or wholly the problem. How many fields are in it?
    4 tables
    1 with 12 fields
    1 with 77 fields
    1 with 98 fields
    1 with 86 fields

    grand total in all tables 273 fields

    I know about normalization and the principles behind it. I know that a lot of my fields are place holders and are not used most of the time but in my line of work I have to plan for the eventuality that it might.

    I track associates in a warehouse and most of them do only one or two different task during a day, but some do more. A truck loader can load up to 15 trucks in a day and I need to know about all of them. So I capped my task table at 15. I need to know task code, start time, stop time, # of trips taken, and the standard of the job (how long it takes to make one trip on average) So that is 5 data points times 15.
    If anyone has a suggestion about cutting that down I am all ears.

    I think in my data entry form I should be able to get by with 350 bound controls so I don't get why I am having the trouble.

  7. #7
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,360
    Your task table doesn't sound normalised, You shouldn't have task1, task2, etc as fields in a table.
    You should have tasks as a subset of the master records.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  8. #8
    chriswrcg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2018
    Posts
    140
    If I have the Possibility of an associate working 15 different jobs in a day how else should I do it than have task1 task2 and so on?

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,278
    Agree with Minty; plus I do believe that the tables which are part of an underlying recordsource with that many fields is too many, even if you're not using them all.
    If loading 15 trucks is 15 tasks, then you might get away with tblEmployee and tblEmplTask. In tblEmplTask, the employee data would be repeated in 15 records, one record per task with start and completion times. However, if each task might have 5 interruptions, I'd consider tblTaskInterrupt with fields for the taskID from tblEmplTask and the start & end times and reason for the interruption. You might have supporting (lookup) tables (not fields) for interruption reasons or a specific task list - whatever.

    If using supporting tables, and if the idea fits the business at hand, maybe tblTasks where loading trucks is a task. Then we're talking about 15 instances of a task.

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,224
    Regardless whether normalized or not, form RecordSource should not include all the related tables. Use form/subform arrangement.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    chriswrcg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2018
    Posts
    140
    Sorry for the delay getting back in touch but I was out of town. Can you point me to an example of a form that uses lookup tables or how to build a lookup table? If there is a better way of doing what I need I am all for it and want to learn.

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,224
    Lookup tables are used in comboboxes on form.

    Usually a form does data entry/edit for 1 table. Build your form bound to 1 table. Use comboxes for selecting values from lookup tables.

    Then there are dependent 'child' tables. Example: Orders is master table and OrderDetails is dependent table. Products table serves as lookup table for combobox on OrderDetails. Must have a record in Orders table before can enter associated records in OrderDetails where products are selected via combobox.

    It is a balancing act between normalization and ease of data entry/output. "Normalize until it hurts, denormalize until it works".
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Too many fields defined, Access 2010
    By khughes46 in forum Database Design
    Replies: 3
    Last Post: 04-04-2014, 01:41 PM
  2. Non-Saved user defined fields for reports
    By mrmims in forum Reports
    Replies: 3
    Last Post: 11-30-2013, 05:10 PM
  3. Replies: 3
    Last Post: 11-12-2013, 04:13 PM
  4. Replies: 5
    Last Post: 03-13-2012, 11:53 AM
  5. Too many fields defined??
    By stryder09 in forum Access
    Replies: 1
    Last Post: 04-08-2011, 11:00 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums