Forms and to many fields

[Ken]

I have two tables that contain 150 fields each. I have established a
relationship between the two also. When I try to create the form with both
tables Access states that I have to many fields. Is there a limit on the
number of fields that you can have on a form? If so is there a way around
this?

 

[Bob Quintal]

I have two tables that contain 150 fields each. I have
established a relationship between the two also. When I try
to create the form with both tables Access states that I have
to many fields. Is there a limit on the number of fields that
you can have on a form? If so is there a way around this?

There is a limit on the number of fields in a table, 255. That
limit is carried over into the limit of fields you can have in a
query.

There is a lifetime limit on the number of controls on a form,
iirc 762 or thereabouts, 255 of which can be bound to fields in
the form's underlying table or query.

But stating you have 300 fields tells me you have something
messed up in your table design. In 35 years of database
programming and design, I've never seen a requirement for so
many fields. Proper relational design usually results in tables
having no more that 30 or 40 fields.

Do you have fields with titles like Week1, Week2, Week3 or
measurement1 measurement2....?

This is a definite sign of commiting Spreadsheet on a database.

 

[George Nicholson]

Forms & Reports have a limitation of 754 controls/Sections "...over their
lifetimes".

"Lifetimes" points to a key difference in how deleted fields or
controls/sections are handled versus their 'limitations'. In both cases
deleted objects count against the limitation *until* a compact is done. At
that point the 255 field counter for tables/queries is reset with the
current (correct) field count. However, the 754 control counter does not
get reset: any deleted controls/sections will be counted against the 754
limit forever. AFAIK, the only way to get a Form/Report to reset its counter
is to create an entirely new report.

While not arguing against the observation that the design structure probably
needs serious rethinking, *if* the OP really does want to pursue their
present course, they might consider having a main form with 150 fields and a
linked subform with 150 fields (>shudder<). But that's not a workaround I
would seriously recommend.

HTH,

 

[Ken]

I am not a experiecned Access person. I am a Storage Administrator that is
trying to migrate from a spreadsheet tracking system to a database. These
tables track the ports and what hosts are connected to those ports on each
switch. Each switch can have up to 255 ports each. I also need to include
other information that to identify the individual switches. This is the
reason for the number of fields.

Forms & Reports have a limitation of 754 controls/Sections "...over their
lifetimes".

"Lifetimes" points to a key difference in how deleted fields or
controls/sections are handled versus their 'limitations'. In both cases
deleted objects count against the limitation *until* a compact is done. At
that point the 255 field counter for tables/queries is reset with the
current (correct) field count. However, the 754 control counter does not
get reset: any deleted controls/sections will be counted against the 754
limit forever. AFAIK, the only way to get a Form/Report to reset its counter
is to create an entirely new report.

While not arguing against the observation that the design structure probably
needs serious rethinking, *if* the OP really does want to pursue their
present course, they might consider having a main form with 150 fields and a
linked subform with 150 fields (>shudder<). But that's not a workaround I
would seriously recommend.

HTH,

 

[John W. Vinson]

I am not a experiecned Access person. I am a Storage Administrator that is
trying to migrate from a spreadsheet tracking system to a database. These
tables track the ports and what hosts are connected to those ports on each
switch. Each switch can have up to 255 ports each. I also need to include
other information that to identify the individual switches. This is the
reason for the number of fields.

That's a very common problem - applying spreadsheet logic to a relational
database! As you can see, it's fraught with problems. Tables are NOT
spreadsheets and require different logic.

If you have a one to many relationship from Switches to Ports, the properly
normalized structure would have two tables:

Switches
SwitchID <some unique identifier for the switch>
Location
<other general information about this switch as an entity>

Ports
SwitchID <link to the Switches table>
PortNo <uniquely identifies the port>
<other fields about this particular port>

Rather than one *field* per port, you would then have one *record* per port;
this lets you add as many ports as you wish, even beyond 255.

John W. Vinson [MVP]

 

[Bob Quintal]

I am not a experiecned Access person. I am a Storage
Administrator that is trying to migrate from a spreadsheet
tracking system to a database. These tables track the ports
and what hosts are connected to those ports on each switch.
Each switch can have up to 255 ports each. I also need to
include other information that to identify the individual
switches. This is the reason for the number of fields.

You need to redesign so that the switch table contains
information about the switch, and add a port table which
contains information on one port. One piece of info for each
port is the id number of the switch.

Forms & Reports have a limitation of 754 controls/Sections
"...over their lifetimes".

"Lifetimes" points to a key difference in how deleted fields
or controls/sections are handled versus their 'limitations'.
In both cases deleted objects count against the limitation
*until* a compact is done. At that point the 255 field
counter for tables/queries is reset with the current
(correct) field count. However, the 754 control counter does
not get reset: any deleted controls/sections will be counted
against the 754 limit forever. AFAIK, the only way to get a
Form/Report to reset its counter is to create an entirely new
report.

While not arguing against the observation that the design
structure probably needs serious rethinking, *if* the OP
really does want to pursue their present course, they might
consider having a main form with 150 fields and a linked
subform with 150 fields (>shudder<). But that's not a
workaround I would seriously recommend.

HTH,