Limited Fields in Forms? (Access 2000)

Topic

New Reply

This is my first post and I am a newby with Access, so bear with me. Is there a limited number of fields one can have in a form? A form I am trying to make is based on a query with over 300 fields. The form will not allow me to even begin the design. I get a message to the effect that there are too many fields. ??

Reply | Quote

Replies

>>A form I am trying to make is based on a query with over 300 fields. <<

I think you need to rethink your database design. Even a table can have no more than 255 fields. In all likelihood, much of the information you are trying to put on one form should be in subforms, and not even in the same record but in multiple records. I've never come close to putting 300 fields on a form; I bet 50 (and maybe even less) would be the max I've ever done.

Reply | Quote

I have been working with a similar problem, though at the moment, I only have 64 fields, but I am working on a 10 page long report where none of the data is repeated. Not your typical Access report showing multiple records under the same heading. I had to create three different reports, then make them subreports of a 4th main report. I agree with Mark that you need to rethink your design. How many tables are you pulling information from? What exactly is the form being used for? Strictly data entry or is it just to display information, or is it a combination? If you can’t figure out a way to break down the data into more digestible chunks on separate forms, then at the very least you will need to use subforms and probably set up tab controls to make the data accessible from one main form.

Reply | Quote

The limit for the number of fields in a table or query is 255. The limit for the number of controls on a form is 754.

As Mark said, you need to rethink what your doing.

Jim.

Reply | Quote

[indent]

---

The limit for the number of controls on a form is 754

---

[/indent]Any BTW, that’s controls *created*, not just the total number currently on it. Once you hit the limit, it doesn’t matter how many you delete, you can’t add any more.

Reply | Quote

You can compact the MDB, which removes the internal references and get up to the full 754. Same applies to tables and queries.

Jim.

Reply | Quote

[indent]

---

Same applies to tables and queries

---

[/indent]Sorry, I missed a reference somewhere. What do you mean the same applies to tables and queries? I know what compact does, although I had a 97 database where I had hundreds of controls on unbound survey subforms that I’ll swear compact did *not* work to reset the control counter.

Reply | Quote

The reference to the table limit was the fact that if you bump into the 255 limit, then delete some columns, you can’t add more until you compact (resetting the internal pointer).

As for the forms, that’s the way I remember it. Could be wrong though (old age is setting in). But keep in mind that there is a difference between the types of compacts you can use. The one that fully compacts a database is the compact from the tool bar in the UI. Calling compact from code or using JETCOMP doesn
t do the job. Only Access knows what’s in a form object and how to handle it. When you call the other two methods, your letting JET handle the compact.

This is very critical now with A2000 and up as the VBA project file is now stored as a single row. JET can’t do anything with that.

Jim.

Reply | Quote

Thanks for the clarification and for pointing out the differences in compacts. I generally use the built-in compact except on really stubborn databases where it won’t work, although I haven’t had any of those in Access 2000.

There have been numerous references to JetComp in this forum, and it’s certainly bailed me out any number of times, but it is a different compact from that called internally, and it’s worthwhile repeating that.

Reply | Quote