Form or Subform?

[danbl]

I have an ever expanding database with now four different data entry forms. Each of these have 3 elements in common. I have tried to expand the initial form to include the second with no luck. I have also tried using page break to go to the next forms fields. The most common message I get is that there are too many fields??

What would be the best way to accomplish this? Should I merge all the fields into one new table? or Create relationships between the 3 common fields in each of the tables?

My goal is to have two data entry forms rather than four. In the best case, I really would like to have one.

Advice would be greatly appreciated.

 

[DCrake]

Have you got too many fields on the form or in the table? either way smacks of denormalisation.

 

[vbaInet]

There's a certain limit to the number of controls you can have on a form but I would doubt that you've reached that limit. How many controls (approximately) do you have on each form?

For layout, I would advise you look into using a Tab control.

 

[danbl]

First as I said there are four forms which on their own work fine.

Table/form 1 has 39 fields,Table/form 2 has 46 fields, Table/form 3 has 26 fields, and Table/form 4 has 20 fields. Total fields 131; To amny??

I did look at Tab Control but could seem to get it to work. Not sure why but can revisit this.

 

[vbaInet]

If at the time you got the message was when you only tried merging forms 1 and 2, then I suspect corruption. However, if it happened when you had successfully merged 1, 2 & 3 but failed in 4, then you've most likely reached the limit.

You would need to create a new form and move those controls to that form. Having 500+ controls on a form isn't as appealing as opening 4 forms for data entry.

 

[DCrake]

Would love to know what schema requires 500+ controls for data entry. How long does it take to complete 1 record?

 

[danbl]

Not sure how you arrive at 500? The total number of fields which I enter data, mostly Yes/No fields, is 131. Can you explain?
The message came when I tried to copy the fields from the second form to the first form and went to save the new design.
How would tab control be any different?

 

[DCrake]

When you "copied" the controls from form 1 to form 2 did you use Copy or Cut? This can make a difference.

Make a copy of form 1
Compact and Repair

Design copy of form 1 and select the fields using "Cut" or Ctrl+X
Close form do not save changes

Open form 2 and paste. Save form and close
Compact and repair

 

[vbaInet]

Ah, I see. I was adding 131 to it to (plus I had to multiply by 2 because each control would have a label attached to it and labels count too).

It's not about the number of controls you have present on the form, it also counts those controls you have previously deleted. So yeap, a new form needs to be created.

As mentioned, the tab control is just for layout.

 

[danbl]

I did as you suggested and was able to cut the fields from form 2 and paste them in form 1.
However several of the data entry fields, not all, that were cut and pasted to the first form have "green" corner and when you click on it the following is the message:
Invalid Control Property: Control Source
No such field in Field List

Thoughts?

 

[vbaInet]

Select from the list in the Control Source property for each of the controls.

 

[danbl]

I found the problem, thanks for the help again.

 

[vbaInet]

Glad we could help.