Re: error 3048 - Cannot open any more databases

Tech-Archive recommends: Fix windows errors by optimizing your registry

A simple function to populate a Combo/ListBox

Private Function cbo_Populate(Fld As Access.Control, id As Long, row As
Long, col As Long, Code As Integer)
Dim thQ As String
Dim Rs As DAO.Recordset
Dim F As DAO.Field
Static QDef As DAO.QueryDef
Dim Db As DAO.Database
Dim Parm As DAO.Parameter
Static Entries As Variant
Static Cnt As Long
Dim ReturnVal As Variant

On Local Error Resume Next

ReturnVal = Null
Select Case Code
Case Access.acLBInitialize 'Initialize.
Cnt = &HFFFF& 'Max Rows Allowed
Entries = Empty
thQ = "SELECT T.TypeID, T.Type FROM Type T" & VBA.vbCrlf & _
"Order By 2"
Set Db = Access.CurrentDb
Set QDef = Db.CreateQueryDef(VBA.vbNullString, thQ)
For Each Parm In QDef.Parameters
With Parm
.Value = Access.Eval(.Name)
End With
Next
Set Rs = QDef.OpenRecordset(DAO.dbOpenSnapshot)
Entries = Rs.GetRows(Cnt)
Cnt = Rs.RecordCount
Rs.Close: Set Rs = Nothing
Set Db = Nothing
ReturnVal = Cnt
Case Access.acLBOpen 'Open.
ReturnVal = Timer 'Unique ID number for control.
Case Access.acLBGetRowCount 'Number of rows.
ReturnVal = Cnt
Case Access.acLBGetColumnCount 'Number of columns.
ReturnVal = Fld.ColumnCount
Case Access.acLBGetColumnWidth 'Column width.
ReturnVal = -1 'Use the default width.
Case Access.acLBGetValue 'Get the data.
If Fld.ColumnHeads And row = 0 Then
ReturnVal = VBA.StrConv(QDef.Fields(col).Name, VBA.vbProperCase)
Else
ReturnVal = Entries(col, row + Fld.ColumnHeads)
End If
Case Access.acLBEnd 'End
Cnt = 0
Entries = Empty
ReturnVal = Nothing
QDef.Close : Set QDef = Nothing
End Select
cbo_Populate = ReturnVal
End Function


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

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





.

Relevant Pages

  • Re: error 3048 - Cannot open any more databases
    ... into the Row Source Type Property thereby, I opening the "connection" to the ... I am using these combo boxes not only as a way of selecting a ... the same subform, just inserted 48 times. ... in the parent form as well that references the child forms. ...
    (microsoft.public.access.formscoding)
  • Trouble resetting subform after update
    ... following is some code that is called after 3 combo boxes are checked to see ... The subform is unbound with several check boxes and labels in a ... Dim rst As Recordset ... Dim varX As Integer ...
    (microsoft.public.access.formscoding)
  • RE: Trouble getting correct info on Subform
    ... the code to update the fields on the subform. ... show in order which correspond to the combo boxes on the main form. ... Dim rst As Recordset ... I tried using a query and as my recordset using this ...
    (microsoft.public.access.formscoding)
  • RE: Trouble resetting subform after update
    ... else on the subform after checking the box. ... > following is some code that is called after 3 combo boxes are checked to see ... > Dim dbs As Database ... > Dim rst As Recordset ...
    (microsoft.public.access.formscoding)
  • PLEASE HELP - COMBO BOX PROBLEM
    ... I've typed the following for on a subform using an unbound Combox behind ... Dim rs As Recordset ... MsgBox "No Entry Found.", vbInformation ... boxes should be populated with the information found. ...
    (microsoft.public.access.formscoding)