Is there a workaround for "too many fields defined"

[M G Henry]

I have a tabbed form that contains 12 different "pages" and when I try
and run the form I get the error message too many fields defined ---
which I believe is the 255 field limit in the record source.

I was wondering, that if I were to break the pages down to include
subforms... is the number of fields contained in the subforms added to
the field limit for the main form ???

Is there some other way to work around this error message ???

I am putting the pages of forms on a tabbed control to keep the form
syncronized during the add and edit phases.

[Allen Browne]

You can have up to 255 fields in a table or query, and up to 700-odd
controls on a form. A subform counts as one control on the main form, and
can have its own controls and RecordSource (so fields.)

However, a well designed table rarely has more than 50 fields. If you have
hundreds, there's a very high chance that you are designing something that
looks like a spreadsheet, i.e. it is not relational.

One dead giveaway is if you have any repeating fields - sequences such as:
Week1, Week2, ...
Employee1, Employee2, ...
Monday, Tuesday, ...
FirstQuarter, SecondQuarter, ...
Item1, Item2, ...
Whenever you see this kind of thing, it always means you need to create a
related table with lots of records instead of having lots of fields in your
table.

The table analyzer in Access may be able to give you some suggestions:
Tools | Analyze | Table

To learn more about this, search on "normalization." Here's some links:
http://home.bendbroadband.com/conrad...abaseDesign101

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"M G Henry" <mr*************@hotmail.comwrote in message
news:11**********************@f1g2000cwa.googlegro ups.com...

>I have a tabbed form that contains 12 different "pages" and when I try
and run the form I get the error message too many fields defined ---
which I believe is the 255 field limit in the record source.

I was wondering, that if I were to break the pages down to include
subforms... is the number of fields contained in the subforms added to
the field limit for the main form ???

Is there some other way to work around this error message ???

I am putting the pages of forms on a tabbed control to keep the form
syncronized during the add and edit phases.

[m.wanstall]

Surely the wait with a form of this magnitude would drive you bananas!
To answer your question, nesting subforms will seemingly give you
endless amounts of controls to have present but the control limit is
there for performance reasons, it's best not to try to circumvent it.

It would make more sense to logically separate your data better, you
can't forget that Access Forms and a windowed application are non
completely interchangeable. A form is made to present one set of data
in a meaningful way...the more you pile in, the less meaningful it
becomes.

You may be in an industry where you deal with an enormous variety of
variables (such as I do) and if this is the case normalisation is not
always the answer. For presentation purposes, logical separation of the
data is appropriate.

If you can give more specifics of your case I could probably point you
in a more specific direction.

Good luck!
-Mal W

M G Henry wrote:

I have a tabbed form that contains 12 different "pages" and when I try
and run the form I get the error message too many fields defined ---
which I believe is the 255 field limit in the record source.

I was wondering, that if I were to break the pages down to include
subforms... is the number of fields contained in the subforms added to
the field limit for the main form ???

Is there some other way to work around this error message ???

I am putting the pages of forms on a tabbed control to keep the form
syncronized during the add and edit phases.

[M G Henry]

Allen Browne wrote:

You can have up to 255 fields in a table or query, and up to 700-odd
controls on a form. A subform counts as one control on the main form, and
can have its own controls and RecordSource (so fields.)

However, a well designed table rarely has more than 50 fields. If you have
hundreds, there's a very high chance that you are designing something that
looks like a spreadsheet, i.e. it is not relational.

The structure that I am using has the individual pages based upon
tables that have sometimes as few as 10 to 15 fields, but no more than
60 or so fields...

The problem I am running into is that the record source for the tabbed
control has to have me declare the field names for all the tables
linked to all the pages, so my total number of fields exceeds 255 ( not
by much ). I have used some subforms in my design and so I now will
break down the more complicated forms into more subforms to get my
total number of fields on the record source below the 255 limit.

I can only imagine that the layering of the subforms inside of the
forms, would create performance issues, however, this application will
be used by a small number of users and I hope will not end up with
severe performance issues.

I do appreciate your input and will investigate the links you have
given me as a further resource of information.

[M G Henry]

Allen Browne wrote:

You can have up to 255 fields in a table or query, and up to 700-odd
controls on a form. A subform counts as one control on the main form, and
can have its own controls and RecordSource (so fields.)

However, a well designed table rarely has more than 50 fields. If you have
hundreds, there's a very high chance that you are designing something that
looks like a spreadsheet, i.e. it is not relational.

The structure that I am using has the individual pages based upon
tables that have sometimes as few as 10 to 15 fields, but no more than
60 or so fields...

The problem I am running into is that the record source for the tabbed
control has to have me declare the field names for all the tables
linked to all the pages, so my total number of fields exceeds 255 ( not
by much ). I have used some subforms in my design and so I now will
break down the more complicated forms into more subforms to get my
total number of fields on the record source below the 255 limit.

I can only imagine that the layering of the subforms inside of the
forms, would create performance issues, however, this application will
be used by a small number of users and I hope will not end up with
severe performance issues.

I do appreciate your input and will investigate the links you have
given me as a further resource of information.

[M G Henry]

Allen Browne wrote:

You can have up to 255 fields in a table or query, and up to 700-odd
controls on a form. A subform counts as one control on the main form, and
can have its own controls and RecordSource (so fields.)

However, a well designed table rarely has more than 50 fields. If you have
hundreds, there's a very high chance that you are designing something that
looks like a spreadsheet, i.e. it is not relational.

The structure that I am using has the individual pages based upon
tables that have sometimes as few as 10 to 15 fields, but no more than
60 or so fields...

The problem I am running into is that the record source for the tabbed
control has to have me declare the field names for all the tables
linked to all the pages, so my total number of fields exceeds 255 ( not
by much ). I have used some subforms in my design and so I now will
break down the more complicated forms into more subforms to get my
total number of fields on the record source below the 255 limit.

I can only imagine that the layering of the subforms inside of the
forms, would create performance issues, however, this application will
be used by a small number of users and I hope will not end up with
severe performance issues.

I do appreciate your input and will investigate the links you have
given me as a further resource of information.

[M G Henry]

Allen Browne wrote:

You can have up to 255 fields in a table or query, and up to 700-odd
controls on a form. A subform counts as one control on the main form, and
can have its own controls and RecordSource (so fields.)

However, a well designed table rarely has more than 50 fields. If you have
hundreds, there's a very high chance that you are designing something that
looks like a spreadsheet, i.e. it is not relational.

The structure that I am using has the individual pages based upon
tables that have sometimes as few as 10 to 15 fields, but no more than
60 or so fields...

The problem I am running into is that the record source for the tabbed
control has to have me declare the field names for all the tables
linked to all the pages, so my total number of fields exceeds 255 ( not
by much ). I have used some subforms in my design and so I now will
break down the more complicated forms into more subforms to get my
total number of fields on the record source below the 255 limit.

I can only imagine that the layering of the subforms inside of the
forms, would create performance issues, however, this application will
be used by a small number of users and I hope will not end up with
severe performance issues.

I do appreciate your input and will investigate the links you have
given me as a further resource of information.

[M G Henry]

Allen Browne wrote:

You can have up to 255 fields in a table or query, and up to 700-odd
controls on a form. A subform counts as one control on the main form, and
can have its own controls and RecordSource (so fields.)

However, a well designed table rarely has more than 50 fields. If you have
hundreds, there's a very high chance that you are designing something that
looks like a spreadsheet, i.e. it is not relational.

The structure that I am using has the individual pages based upon
tables that have sometimes as few as 10 to 15 fields, but no more than
60 or so fields...

The problem I am running into is that the record source for the tabbed
control has to have me declare the field names for all the tables
linked to all the pages, so my total number of fields exceeds 255 ( not
by much ). I have used some subforms in my design and so I now will
break down the more complicated forms into more subforms to get my
total number of fields on the record source below the 255 limit.

I can only imagine that the layering of the subforms inside of the
forms, would create performance issues, however, this application will
be used by a small number of users and I hope will not end up with
severe performance issues.

I do appreciate your input and will investigate the links you have
given me as a further resource of information.

[Rick Brandt]

"M G Henry" <mr*************@hotmail.comwrote in message
news:11**********************@73g2000cwn.googlegro ups.com...

Allen Browne wrote:

>You can have up to 255 fields in a table or query, and up to 700-odd
controls on a form. A subform counts as one control on the main form, and
can have its own controls and RecordSource (so fields.)

However, a well designed table rarely has more than 50 fields. If you have
hundreds, there's a very high chance that you are designing something that
looks like a spreadsheet, i.e. it is not relational.

The structure that I am using has the individual pages based upon
tables that have sometimes as few as 10 to 15 fields, but no more than
60 or so fields...

The problem I am running into is that the record source for the tabbed
control has to have me declare the field names for all the tables
linked to all the pages, so my total number of fields exceeds 255 ( not
by much ).

Access queries cannot have more than 255 fields. If you created such a query
then it is the query that is giving you the error, not the form.

Taking all of your properly designed and normalized tables and combining them
into one giant query is NOT the proper way to go about things. You need to use
a combination of forms, some with subforms, and some that are opened separately
as popups to show related data.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Lyle Fairfield]

"M G Henry" <mr*************@hotmail.comwrote in
news:11**********************@73g2000cwn.googlegro ups.com:

Allen Browne wrote:

>You can have up to 255 fields in a table or query, and up to 700-odd
controls on a form. A subform counts as one control on the main form,
and can have its own controls and RecordSource (so fields.)

However, a well designed table rarely has more than 50 fields. If you
have hundreds, there's a very high chance that you are designing
something that looks like a spreadsheet, i.e. it is not relational.

The structure that I am using has the individual pages based upon
tables that have sometimes as few as 10 to 15 fields, but no more than
60 or so fields...

The problem I am running into is that the record source for the tabbed
control has to have me declare the field names for all the tables
linked to all the pages, so my total number of fields exceeds 255 (
not by much ). I have used some subforms in my design and so I now
will break down the more complicated forms into more subforms to get
my total number of fields on the record source below the 255 limit.

I can only imagine that the layering of the subforms inside of the
forms, would create performance issues, however, this application will
be used by a small number of users and I hope will not end up with
severe performance issues.

I do appreciate your input and will investigate the links you have
given me as a further resource of information.

Databases are, of necessity, vertical. The problem you describe is
typical of a horizontal orientation (although your particular problem
itself may not be.) I could not deal conceptually with 250 fields
concurrently; I expect something less than twenty-five is my limit. So I
have difficulty imagining this form, comprised of subforms and tabs, and
its being necessary or useful. Nor have I ever seen such a form, after
almost twenty-five years as a Database Developer, either of my own
creation or that of another experienced Developer.
Simple, clean, efficient designs are almost always best.
This may give you cause to reconsider your design. On the other hand you
may be sure you are right and that this is the only or best way. If you
succeed in solving the 250 field problem what will you have? My guess is
that you will have a very slow and nasty animal which will bite you as
creator, and all its users every day until it is euthanized.

--
Lyle Fairfield

http://www.ffdba.com/toyota/BurlingtonToyotaLease.htm

(just a sad story - read if bored)

[David W. Fenton]

"Rick Brandt" <ri*********@hotmail.comwrote in
news:ZS*******************@newssvr14.news.prodigy. net:

Taking all of your properly designed and normalized tables and
combining them into one giant query is NOT the proper way to go
about things. You need to use a combination of forms, some with
subforms, and some that are opened separately as popups to show
related data.

Or use the OnChange event of the Tab control to change the
recordsource of the main form so that the controls on the particular
tab can be populated from the subset of all the fields needed for
it.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[M G Henry]

Allen Browne wrote:

You can have up to 255 fields in a table or query, and up to 700-odd
controls on a form. A subform counts as one control on the main form, and
can have its own controls and RecordSource (so fields.)

However, a well designed table rarely has more than 50 fields. If you have
hundreds, there's a very high chance that you are designing something that
looks like a spreadsheet, i.e. it is not relational.

The structure that I am using has the individual pages based upon
tables that have sometimes as few as 10 to 15 fields, but no more than
60 or so fields...

The problem I am running into is that the record source for the tabbed
control has to have me declare the field names for all the tables
linked to all the pages, so my total number of fields exceeds 255 ( not
by much ). I have used some subforms in my design and so I now will
break down the more complicated forms into more subforms to get my
total number of fields on the record source below the 255 limit.

I can only imagine that the layering of the subforms inside of the
forms, would create performance issues, however, this application will
be used by a small number of users and I hope will not end up with
severe performance issues.

I do appreciate your input and will investigate the links you have
given me as a further resource of information.

[M G Henry]

Allen Browne wrote:

You can have up to 255 fields in a table or query, and up to 700-odd
controls on a form. A subform counts as one control on the main form, and
can have its own controls and RecordSource (so fields.)

However, a well designed table rarely has more than 50 fields. If you have
hundreds, there's a very high chance that you are designing something that
looks like a spreadsheet, i.e. it is not relational.

The structure that I am using has the individual pages based upon
tables that have sometimes as few as 10 to 15 fields, but no more than
60 or so fields...

The problem I am running into is that the record source for the tabbed
control has to have me declare the field names for all the tables
linked to all the pages, so my total number of fields exceeds 255 ( not
by much ). I have used some subforms in my design and so I now will
break down the more complicated forms into more subforms to get my
total number of fields on the record source below the 255 limit.

I can only imagine that the layering of the subforms inside of the
forms, would create performance issues, however, this application will
be used by a small number of users and I hope will not end up with
severe performance issues.

I do appreciate your input and will investigate the links you have
given me as a further resource of information.