Maximum fields in an access form

[Pettor]

Hello guys,

I am trying to create a form with data from around 10 tables. I want to present around 500 fields in total and I am facing a problem with the record source.
As I have seen, Access creates a query combining all these tables, but while adding more and more columns I eventually receive the message that there are "too many fields involved".
Is there any way t bypass this limitation?

Many thanks

 

[mole999]

can't tell you how to do it, but you can create more than one table and using identical referencing utilise data from more than one table, 20 plus years since i started doing that

 

[Pettor]

And how this can be done? Sorry not much of experience.

 

[mole999]

And how this can be done? Sorry not much of experience.

As I said i can't acutually guide you

 

[Pettor]

Oh, yes you are right, let's wait then if someone else knows.

Thanks

 

[JonXL]

I am not familiar with that limitation. Maybe it's related to this...

Too Many Fields Defined error - Microsoft 365 Apps

Explains that you may receive an unexpected error message when you try to save a table after adding a new field or changing the properties of an existing field since the internal column count to track the number of fields in the table has reached 255.

docs.microsoft.com

As a workaround, would you be able to split the form up into two or three separate forms (with the same record source) and then lay them out as subforms on another form to look like one big form?

 

[Norie]

500 fields?

What exactly are you storing in these tables?

 

[Pettor]

Thank you John, that is what I thought about and I am trying to do it now with 3 subforms.
If I have difficulties I will let you know.

Thanks

 

[Micron]

Sounds to me like you've designed your tables like you would spreadsheets. A table should represent one entity. Each field should represent one attribute (characteristic) of the entity. Your post implies that you have 10 entities and each one of them has an average of 50 attributes. Not impossible I guess, but I can't imagine a query for a form that needs to retrieve records from 10 entities and present them as one thing. If all of that is OK, then maybe what you need is subforms, but not if you've designed the tables totally wrong.