Can't create any more controls on form

[jhob]

I've come up with a bit of a show-stopper issue. I have a form with a large number of controls on it and when I try and add new controls I get the error:

'Microsoft Office Access can't create any more controls on this form or report.

If you have deleted controls from this form or report in the past, you may be able to rename the form or report and then add more controls to it'

Well, I have tried as suggested and I can add a couple more controls but then come up against the same error again. I'm also guessing that there is no way around this error.

I do have one avenue of hope, but I need a little advice on how best to implement it, being something of an access novice...

A good number of the controls are used as temporary storage for values pulled from the database as this seemed the easiest way to handle them. Now what I'm wondering is if there's an easy way I could just pick them up from the database as and when needed by a calculation rather than reading them all into controls when the form loads.

I have had a bit of a play round trying to do this before but with no success. Basically what I would like to be able to do is reference a db field something like:

tbl_Houses!h_DataField

The problem I have is how to speicify which row to look at. The primary key is a simple id field.

I hope I've explained that well enough and I hope that someone can help me out here else I'm a bit screwed!

[lagbolt]

Say more about what you are trying to do. Controls are made for users to interface with data in a table. If you need to do computations on table values there are a number of much faster and simpler approaches that using a million controls on a form.

[jhob]

Basically there are a number of components for a house, the quantity for each component is a calculation based on various dimensions of the house.

The dimensions are inputted on a previous form and written to the database. I currently have it that they are read into controls and then used in the calculations. I realise that this is a slightly perverse way to go about achieve my ends and hence want to cut out the reading into controls stage.

What I am having trouble with is how best to reference and use the values from the database. The values only need to come from one table to which the form is linked (if that's the right terminology).

Does that help to outline the situation a little more?

[Rabbie]

Why not do your calculations in a query rather than on the form.

[WayneRyan]

jhob,

Access limits the number of form controls to a number around 750. Even if
you delete them, their slot is gone forever.

Instead of reading them into form controls, you could us global variables.
There won't be any limitations on the maximum number. You can read them
in with DLookUps, or recordsets or however.

Wayne

[jhob]

Thanks for the responses folks. I have now changed it so that DLookUp is used instead, makes a lot more sense. The form seems to load a little quicker too.

[Pat Hartman]

I don't like using domain functions due to the overhead associated with them unless there is no other way.

A possible alternate solution is to use a subform that is bound to the table that contains the reference fields.

Also, if you have so many fields that you are exceeding the 750 limit, your tables probably need normalizing.

[jhob]

Would using a recordset be more efficient that DLookUp? I have it all in a function so can easily switch over if one is more efficient than the other.

Pat - the tables are already normalised about as far as they can go, the application is just *very* data heavy, which in turn makes it quite a pain to work with as there is a lot of repetition and field copying involved. Sadly there's no way around it and that's what the client wants.

[Pat Hartman]

Someone posted here a while back substitute functions that replace DLookup() and the other domain functions. I don't have a link handy but you should be able to find them by searching.

If you can't use a join to obtain the lookup data then you have no alternative but to use a "heavier" method of retrieving the data.