Combo box limits?

[Guest]

Is there a limit to the number of combo boxes one can have on a form? I have
a form with 60. After clicking on several, I get message "Unable to open
anymore databases." The combo box list will be blank. I suspect that there
is a limit to how many combo boxes based on individual SQL statements can be
used, but can't find a definitive statement about this. The SQL statements
are apparently run and remain open while the form is open.

If I can't have 60, how can I work around this limitation? I suppose
breaking my form into several forms would work. Can I open and close each
combo box SQL statement as I access each box in succession. I did try to
make one query work for all with lists limited by which combo box had the
focus or was entered, but this did not seem to work. It appears that all
combo boxes have their list run upon opening the form. Also I could not
reference the name of the combo box in the query to populate it.

Any help?

 

[Allen Browne]

Yes, it's not too difficult to run into this issue if you have lots of
combos on a form. Each one opens its own RowSource, which uses database
connections for the source tables.

First option might be to normalize the database. If you have 60 fields that
repeat in your table, such as Location1, Location2, ..., Location60, with a
combo for each one, the solution will be to create a related table with many
*records* instead of this table with many fields. You can use a continuous
form (possibly a subform, with the other fields from this table in the main
form?), and the problem is solved because there is now only one combo per
row in the subform.

If that is not possible, but all the combos do have the same RowSource, it
is possible to write a callback function and assign it to the RowSourceType
of the combos. By loading an array in the initialization of the callback
function, they all have the same source data, but there is only one memory
load for all of them and so the problem is solved. These functions are
rather cryptic to write, but there's an example here:
DirListBox() function
at:
http://allenbrowne.com/func-02.html
For your case, you would OpenRecordset in the initialze case (case 0), and
loop through the records to load the array.

If the data cannot be normalized, and they each need different sources, the
only option left is to change the RowSourceType to Value List, and load each
one's RowSource with the strings you need for the fields.