Re: error 3048 - Cannot open any more databases

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.

--
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: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.

.

Loading