Re: error 3048 - Cannot open any more databases

Allen,

Thanks so much for a quick reply.

Anyways, I was actually able to figure out my questions on my own right
before seeing your response. However, performance on my form was
horrendously slow. but after reading your response, I followed your
recommendations and I declared my array as static and also loaded the array
and opened my recordset in the initialize case. This then improved
performance greatly. Not quite as good as before, but at least i do not get
the "cannot open any more databases" error. Yeah!

I greatly appreciate your help with this.

Thank You



"Allen Browne" wrote:

The multi-dimentional array needs to be declared as Static, so the values
are present without having to recreate it every time the callback function
is called to get a value.

You load the array in the acLBInitialize case.

You then assign the return value from the array in the acLBGetValue. Since
Access passes in Row and Col arguments to indicate the row and column it
wants. You use these values as the index to your 2-dimentional array.

Here's another example:
http://allenbrowne.com/ser-19.html
It's a single-element array again, but you just change the function return
value to include Col as well as Row.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Greg W" <GregW@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8603EDC8-8BA4-4179-B772-2128CB6206DC@xxxxxxxxxxxxxxxx
Allen,

Well I am here working with your suggestions tonight and have run into an
issue. I am having a hard time creating a multidimensional array. I am
also
not sure how to then send this multidimensional array to the combo box.

I have created a one column array and then sent this to a one column combo
box successfully.


Thanks for any input. It is greatly appreciated.



"Allen Browne" wrote:

Many years ago, I did a rostering app that had the problem you describe.
Using a callback function for the RowSourceType solved the problem.

The problem is not about whether the bound column is displayed or not:
It's
that fact that each combo needs its own RowSource, and you solve that by
using a single array instead of a separate connection for each one.

"Greg W" <GregW@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8B8261FD-D7C2-45A3-B536-B71673EECCF1@xxxxxxxxxxxxxxxx
Allen,

Yes the bound column of my combo boxes are not the display column.

This is good that the combo box will still be able to display the
desired
data. But do you think that if I were to go this route it will
definitely
decrease my open connections, since ultimately that is what I am after.

Also, would it just be easier for me to store, in my Type field, and
names
and not the numerical values. That way I can convert the combos to
text
boxes with a button beside each one that would open a seperate form
that
would display a list of values to select. Upon selection, the popup
would
close and data would then be entered into the type field? Wouldnt this
almost be simpler, and definitely decrease my open connections? Just a
thought.

Thanks,


"Allen Browne" wrote:

That's fine, Greg. I take it that the bound column is not the display
column. The combo that uses the callback function as its RowSourceType
will
still be able to display the desired data.

What happens is that you load the static array one (when the combo is
first
initialized), and then it calls the callback function whenever it
needs a
value. You better believe the documentation when it says Access will
call
the callback in any old order, but it doesn't matter because it is now
reading the values from the array.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Greg W" <GregW@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:32B07373-4E80-4294-9C74-64ED16019EEF@xxxxxxxxxxxxxxxx
Allen,

Thank you so much for the information.

I think I may understand what you are doing with your code.

I guess the biggest thing for me to understand, and please correct
me
if I
am wrong, is that I can populate a combo box with vba without using
the
row
source property buy creating a function and then typing that
function
name
into the Row Source Type Property thereby, I opening the
"connection"
to
the
underlying tables only when needed.

However, I am using these combo boxes not only as a way of selecting
a
grouping of information, but also to display the information in the
correct
way. That doesnt make sense when I read that, but here is what it
is
doing.
The "work schedule" table is storing numbers in a field titled
"Type"
for
the
type of work to be done that day. However, since I do not want the
user
to
see the number of the "Type" I have the combo box there to actually
display
the "type" field as a Name not a number. Is this bad practice. I
believe
this is actually causing some additional work load for access
because
each
scheduled item has this "Type" combo box. And since there are 48
subforms
capable of displaying 10 or so records, that could be, at a maximum,
480
combo boxes accessing the back end to "translate" the number to the
name
of
the type. This also is the same for several other combo boxes. (I
am
begining to see why I am having such a problem)

THanks again for the reply. I hope I havent lost you yet, i may
have
lost
myself.


"Allen Browne" wrote:

Greg, the problem probably does lie with the RowSource for the
combos.

If there are 48 combos that all have the same RowSource (such as
the
ones
where you select a staff member), you may be able to write a
callback
function to use as the RowSourceType.

Here's an example of such a function:
http://allenbrowne.com/func-02.html
That one uses a loop to read file names into a static array. You
could
do
a
similar thing, using OpenRecordset to read the employees into a
static
array, and then supply all the combos from the one array.

It's a bit arcane to wrap your head around if you've never seen
this
kind
of
callback before, but it's not difficult to implement.

"Greg W" <GregW@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F5513353-C981-4373-B1B9-E30D8443A0CE@xxxxxxxxxxxxxxxx
I am having an issue with a form that I am hoping someone can help
me
solve,
or at least point me in a direction so that I can arrive at a
solution
myself.

Well I guess to begin with I am running Access 2003 on Windows
XP.

My issue is, as stated in the subject line, that I am getting the
“Can
not
open any more databases” error after using a certain form I have
created
for
sometimes a very very short period of time. Sometimes I can only
click
three
combo boxes.

This form is an unbound form with 48 subforms. The 48 subforms
are
actually
the same subform, just inserted 48 times. I realize this sounds
reasonably
insane, but let me give you more info as to why this is the way
it
is.
It
is
a weekly work schedule for 8 employees over 6 days of the week.
I
have
linked each individual occurrence of this subform to two text
boxes
on
the
parent form, one for a date, one for a name. I have names
running
down
the
left hand side and dates running along the top, which are in the
parent
form.
Each subform has about 4 combo boxes plus a whole slew of other
controls,
well between 15 and 25 I would say. I also have a few other
things
going
on
in the parent form as well that references the child forms. I
hope
this
gives you something at least. I am happy to give more info on
this
if
need
be.

I understand that this error is caused by a limitation on the
amount
of
“connections” I can have. My ultimate question is how do I fix
it.
I
have
read that I can replace my Dcounts, and Dlookups with other
methods,
but I
did bit of this, but it did not seem to alleviate the issue much
at
all.
I
used recordcount instead of dcount, and closed the recordset
after
the
count.
Are there alternatives to combo boxes? Like programmatically
creating
one.
I notice that I can test the form by just going through and
clicking
the
drop
down arrow next to each combo box in each subform to replicate
this
error
message.(sometimes I get the error message after just clicking
one
combo
box
if I scroll parent form to view all 48 subfrms first.) My
assumption
is
that I am opening a “connection” accessing those tables over and
over
again,
without closing those connections with each combo box. So my
thoughts
were
that maybe I could create custom combo boxes so that I can open
the
connection only when needed and close the connections when not,
thereby,
decreasing the frequency of the error, or eliminating the error.

Thanks a bunch to all those who will give me a hand with this
one.


.