How can I use 400 fields in a MS-Access form?

Question

I understand the maximum number of fields in a MS-Access form is 255. Is there any way to get around this limitation?

I am working with a mental health survey form that has approximately 400 fields.

I would like to create 1 data entry form to allow the mental health interviewer to enter all the responses on the one form.

I plan to use tabs to facilitate moving through the data entry.

Answer 1

I wonder about the reason of having that. Having such a large number of fields implies some weakness of the design (Definitely an improper design). Its better if you can reconsider the design instead of finding a way to go over the limit.

Answer 2

I'd first question whether any single form should have 400 fields on it

Answer 3

The maximum number of fields in an Access recordsource is 255, as is the case with any Jet SQL statement. The limitation on the number of controls on a form is 754 over the life of the form (i.e., deleting a control does not gain you an additional control).

The design is clearly not correct, but as is so often the case, we don't get to choose on these things!

In that case, the only solution I can think of is a main form that has one field, the PK, and then a bunch of subforms with subsets of fields, e.g., 100 each. This means you have to be very careful about making sure that your subform saves its edits in its OnExit event (otherwise you'll get write conflicts).

And, no, it's not a good design at all.

But it is, at least, a workaround if you're forced to have 400 fields because you have an outside datasource that you can't properly normalize.