Too much memory

[Jeff]

I have a form that shows information in a calendar style format. The form
has 37 subform that query for each day. The problem is that this is very
slow, also it will not work over the network at all. I think the problem is
the fact that it requerys for every subform. Are there any alternate ways
to populate these subforms that are not so memory dependant.

I also have a query that returns every record for the month.
Can I filter in some way from the query?

 

[Allen Browne]

Hi Jeff

Had to do one of these years ago. 7 subforms wide (days of week) x 5 deep
(weeks of month).

The main form was unbound, and had 35 unbound text boxes, named txtDate00 to
txtDate34. Each one sat above a small subform control, named sf00 to sf34.
(They were actually 35 instances of the same subform.)

In the Load event of the main form, we ran some code to load the dates for
the month into the unbound text boxes. Used Weekday() to figure out where
the particular month started, and Mod 35 so that txtDate00 and txtDate01
could be reused if the month wrapped to a 6th row.

Once the dates were assigned to the text boxes, the subforms automatically
showed the right data, because their LinkMasterField was set to the relevant
text box.

The text boxes were formatted so the user could not get to them, and they
just showed the day of the month (although their value was a complete date):
Enabled No
Locked Yes
Format d

This loaded very fast, and was very easy to maintain (only 2 actual forms to
look after).

 

[Jeff Klein]

Allen, Thanks for the reply.
One of the reasons that I use subforms is so I can double click on any of
the records that appear and bring up the record form so I can change it.
Can I do this with your method?

Below is some of the code that I use when the form is loading. See how the
recordsource for each sunform is querying every time that it is looped. If
I had a way to run the query once during load and then the subforms fill
according to the querys recordset I think it might run faster. The query
looping makes it run slow.
I was doing a little experimenting and if I use a table in stead of a query
it runs alot faster.

Am I totally on the wrong track?


For cnt = FirstDay To ((DaysInMonth + FirstDay) - 1)
DayOfMonth = (cnt - FirstDay) + 1
Set frm = Forms!frm_calendar.Controls("SF" & CStr(cnt)).Form
frm.RecordSource = "SELECT
MeetingDate,meetingid,Clientid,EmployeeFullName,ClientFullName,StartTime,End
Time FROM qry_Union_Single_Weekly_Monthly WHERE meetingDate = #" &
CStr((DayOfMonth)) & "/" & Format(Me.txb_Month, "mmm/yyyy") & "#"
frm.lbl_Date.Caption = CStr(DayOfMonth)
frm.lbl_DateFull.Caption = SubFormDate
SubFormDate = SubFormDate + 1
Me.Controls("SF" & CStr(cnt)).Requery
Next

 

[Allen Browne]

Jeff, I think your 37 subforms all have the same RecordSource, apart from
the WHERE clause limiting it to one date? What I was suggesting was to use
the LinkMasterFields/LinkChildFields property of the subform controls
instead. No need for any code. Each subform just automatically shows the
correct date, once you put the right dates into the text boxes on the main
form.

 

[Jeff Klein]

OK...I am trying to understand the LinkMasterFields/LinkChildFields
I do have a label (lbl_DateFull) on the sunform that returns the actual date
of the box. Do I set the recordsource of the subform to my query that shows
the entire month? What do I set the recordsource to on the main form?
The subform has textboxes that show EmployeeNames, do I need to set the
control source of this textbox to my query? I believe I should set the
source object of the subform control to the main form, is this correct?
What do I set the link child and master fields on the subformcontrol? I am
trying to learn, I think this exactly what I need although I need educated.

 

[Allen Browne]

Hi Jeff

In a subform, Access shows only the records where the LinkMasterFields is
equal to the LinkChildFields. You can therefore show all the records for one
date in a subform, just by setting LinkMasterFields to a text box on the
main form that has a date, and setting the LinkChildFields to the name of
the date field used in the subform.

Leave the main form unbound. Use its Open or Load event to assign the dates
for the month to the 37 text boxes. (Post back if you need an example of how
to do this. It does involve a bit of code to get the right dates into the
right boxes.)

Set the RecordSource of your subform to: qry_Union_Single_Weekly_Monthly.

Set the LinkChildFields of each subform to: meetingDate

Set the LinkMasterFields of each subform to the name of the matching date
text box, e.g.: txtDate00.

 

[Jeff Klein]

Allen...thanks for the help
OK , I think I got how it works...I will need to use textboxes to place the
date numbers on the main form for each day and code them during form load to
reflect the datenumber....not a problem, but, Can I make use of the label in
the subform that already reflects the date?

 

[Allen Browne]

Guess you can use a label, but why? A text box in the subform would
automatically show the correct date (if there is an entry), whereas the
label would need its Caption programmed.

 

[Jeff Klein]

The code already exists that fills the caption. But I guess my question is
what to enter in the masterfield that references to the label on the
subform. If there is not a way I will add the textboxes on my main form
above each subform control. I am trying to avoid unnecessary rework on my
form. There is allot of code that handles the layout when resizing the
window. But "ya godda do what cha godda do!"

 

[Allen Browne]

No. You cannot use the name of a label in the LinkMasterFields.

 

[Jeff Klein]

Allen, Thanks for all the help. Your solution works great ...2am
here...going to bed.

 

[Jeff Klein]

Allen,
I have reworked my form and it still will not work over my network. My main
data tables are on the server. When running the forms on the server
everything works great...but on another machine using linked tables..I get
the error "Can not open any more databases" Looks like it processes about
half of the subforms. Maybe I need to add text boxes at each date location
instead of using subforms. I can populate the textboxes with the query data
but I will not have a separate record to click on to open the edit record
form. Any more suggestions?

 

[Allen Browne]

Okay: now we have a different problem: what is opening all these databases,
and not closing them?

Are you using domain aggregate functions such as DLookup() in the
RecordSource of the form? In the RowSource of the combos? These don't clean
up after themselves, and there is a replacement here that does clean up (and
runs faster):
http://members.iinet.net.au/~allenbrowne/ser-42.html

Do you have code that opens recordsets (or opens databases) and does not
Close them AND set them to nothing? You need to explicitly close everything
you open (but only what you open), and set all object variables to Nothing
(preferably in the error recovery section so they are dereferenced even
after an error).

 

[Jeff]

Allen,
I do not have any of the situations you asked for.

The recordsource of the form is set when it opens:
Form.RecordSource = qry_Union_Single_Weekly_Monthly

The rowsource of the combobox is:
SELECT DISTINCTROW Employees.EmployeeID, [EmployeeLastname] & ", " &
[EmployeeFirstName] AS EmployeeFullName FROM Employees ORDER BY
[EmployeeLastname] & ", " & [EmployeeFirstName];

 

[Allen Browne]

Could you remove the code that sets the RecordSouce, and just set it in the
property?

Anything in qry_Union_Single_Weekly_Monthly that uses domain aggregate
functions?

Hmm. Now you have a query as the rowsource for the combo, and the combo is
in the subform? If so, and there are 37 instances of this subform, then it
is loading 37 instances of the same query. That should work okay, but if you
were loading hundreds of instances of the same query, you would have to work
around that by using a callback function as the RowSourceType for the combo.
The callback function would load one array of the values when initialized,
and would respond to the calls from the different combo instances in the
different subform instances by just supplying the value out of the array.

Before proceeding down that path, just verify that this is the problem,
because callbacks are not the most obvious things if you have not worked
with them before. Remove the RowSource from the combo, and check that this
solves the problem.

If it does, this is the kind of thing you will need:

Function ListEmpl(fld As Control, id As Variant, _
row As Variant, col As Variant, code As Variant) As Variant
' Purpose: To return a list of Employees for a combo.
' Rationale: Workaround for "Cannot open more tables" error, when *many*
combos
' (perhaps 100) try to read directly from table/query.
' Arguments: As required to fill combo box.
' Usage: RowSourceType property of Combo.

Const MaxEntries = 256 'Max number of employees to cater for.
Dim rst As DAO.Recordset
Static iEntries As Integer ' Number of Employees found.
Static iInstances As Integer ' Number of currently opened
instances.
Static sEmpl(MaxEntries, 1) As String ' Array of EmplID and name.

Select Case code
Case acLBInitialize ' Initialize.
Set rst = CurrentDb().OpenRecordset("tblEmployee")
iEntries = 0
Do While iEntries < MaxEntries And Not rst.EOF
sEmpl(iEntries, 0) = rst![EmplID]
sEmpl(iEntries, 1) = rst![Surname] & ", " & rst![FirstName]
iEntries = iEntries + 1
rst.MoveNext
Loop
rst.Close
iInstances = iInstances + 1
ListEmpl = True
Case acLBOpen ' Open.
ListEmpl = Timer
Case acLBGetRowCount ' Get rows.
ListEmpl = iEntries
Case acLBGetColumnCount ' Get columns.
ListEmpl = 2
Case acLBGetColumnWidth ' Get column width.
If col = 0 Then ListEmpl = 0 Else ListEmpl = 1440
Case acLBGetValue ' Get the data.
ListEmpl = sEmpl(row, col)
Case acLBEnd ' End.
iInstances = iInstances - 1
If iInstances < 1 Then
Erase sEmpl
iEntries = 0
End If
End Select
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,
I do not have any of the situations you asked for.

The recordsource of the form is set when it opens:
Form.RecordSource = qry_Union_Single_Weekly_Monthly

The rowsource of the combobox is:
SELECT DISTINCTROW Employees.EmployeeID, [EmployeeLastname] & ", " &
[EmployeeFirstName] AS EmployeeFullName FROM Employees ORDER BY
[EmployeeLastname] & ", " & [EmployeeFirstName];

Okay: now we have a different problem: what is opening all these databases,
and not closing them?

Are you using domain aggregate functions such as DLookup() in the
RecordSource of the form? In the RowSource of the combos? These don't clean
up after themselves, and there is a replacement here that does clean up (and
runs faster):
http://members.iinet.net.au/~allenbrowne/ser-42.html

Do you have code that opens recordsets (or opens databases) and does not
Close them AND set them to nothing? You need to explicitly close everything
you open (but only what you open), and set all object variables to
Nothing
(preferably in the error recovery section so they are dereferenced even
after an error).

 

[Jeff]

My query prompts for cbo_EmployeeName and cbo_ClientName. So if I set the
RecordSource Property of the main form to the query it prompts me at the
form's open. OK..for experimental purposes I have eliminated the prompts
from the query and removed the code that sets it at forms open and again
tried to open the form....same perplexing result. ("Cant open any more
databases") The comboboxes I was talking about are not in the subform they
are on the main form.
Nothing with aggregate functions in any of the queries or forms or modules.

Could you remove the code that sets the RecordSouce, and just set it in the
property?

Anything in qry_Union_Single_Weekly_Monthly that uses domain aggregate
functions?

Hmm. Now you have a query as the rowsource for the combo, and the combo is
in the subform? If so, and there are 37 instances of this subform, then it
is loading 37 instances of the same query. That should work okay, but if you
were loading hundreds of instances of the same query, you would have to work
around that by using a callback function as the RowSourceType for the combo.
The callback function would load one array of the values when initialized,
and would respond to the calls from the different combo instances in the
different subform instances by just supplying the value out of the array.

Before proceeding down that path, just verify that this is the problem,
because callbacks are not the most obvious things if you have not worked
with them before. Remove the RowSource from the combo, and check that this
solves the problem.

If it does, this is the kind of thing you will need:

Function ListEmpl(fld As Control, id As Variant, _
row As Variant, col As Variant, code As Variant) As Variant
' Purpose: To return a list of Employees for a combo.
' Rationale: Workaround for "Cannot open more tables" error, when *many*
combos
' (perhaps 100) try to read directly from table/query.
' Arguments: As required to fill combo box.
' Usage: RowSourceType property of Combo.

Const MaxEntries = 256 'Max number of employees to cater for.
Dim rst As DAO.Recordset
Static iEntries As Integer ' Number of Employees found.
Static iInstances As Integer ' Number of currently opened
instances.
Static sEmpl(MaxEntries, 1) As String ' Array of EmplID and name.

Select Case code
Case acLBInitialize ' Initialize.
Set rst = CurrentDb().OpenRecordset("tblEmployee")
iEntries = 0
Do While iEntries < MaxEntries And Not rst.EOF
sEmpl(iEntries, 0) = rst![EmplID]
sEmpl(iEntries, 1) = rst![Surname] & ", " & rst![FirstName]
iEntries = iEntries + 1
rst.MoveNext
Loop
rst.Close
iInstances = iInstances + 1
ListEmpl = True
Case acLBOpen ' Open.
ListEmpl = Timer
Case acLBGetRowCount ' Get rows.
ListEmpl = iEntries
Case acLBGetColumnCount ' Get columns.
ListEmpl = 2
Case acLBGetColumnWidth ' Get column width.
If col = 0 Then ListEmpl = 0 Else ListEmpl = 1440
Case acLBGetValue ' Get the data.
ListEmpl = sEmpl(row, col)
Case acLBEnd ' End.
iInstances = iInstances - 1
If iInstances < 1 Then
Erase sEmpl
iEntries = 0
End If
End Select
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,
I do not have any of the situations you asked for.

The recordsource of the form is set when it opens:
Form.RecordSource = qry_Union_Single_Weekly_Monthly

The rowsource of the combobox is:
SELECT DISTINCTROW Employees.EmployeeID, [EmployeeLastname] & ", " &
[EmployeeFirstName] AS EmployeeFullName FROM Employees ORDER BY
[EmployeeLastname] & ", " & [EmployeeFirstName];

Okay: now we have a different problem: what is opening all these databases,
and not closing them?

Are you using domain aggregate functions such as DLookup() in the
RecordSource of the form? In the RowSource of the combos? These don't clean
up after themselves, and there is a replacement here that does clean up (and
runs faster):
http://members.iinet.net.au/~allenbrowne/ser-42.html

Do you have code that opens recordsets (or opens databases) and does not
Close them AND set them to nothing? You need to explicitly close everything
you open (but only what you open), and set all object variables to
Nothing
(preferably in the error recovery section so they are dereferenced even
after an error).


Allen,
I have reworked my form and it still will not work over my network. My
main
data tables are on the server. When running the forms on the server
everything works great...but on another machine using linked

tables..I
get

the error "Can not open any more databases" Looks like it processes about
half of the subforms. Maybe I need to add text boxes at each date
location
instead of using subforms. I can populate the textboxes with the query
data
but I will not have a separate record to click on to open the edit record
form. Any more suggestions?

 

[Allen Browne]

Not sure what else to suggest, Jeff.

If there are only a couple of combos on the main form, then messing with the
callback function will not be worthwhile. Perhaps there is something in your
code that is not closing what it opened, or calling things recursively, or
has a circular dependency.

It may be a process of elimination to trace down what it is, i.e. make a
copy of the database, and start removing things until you identify what is
causing the problem.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

My query prompts for cbo_EmployeeName and cbo_ClientName. So if I set the
RecordSource Property of the main form to the query it prompts me at the
form's open. OK..for experimental purposes I have eliminated the prompts
from the query and removed the code that sets it at forms open and again
tried to open the form....same perplexing result. ("Cant open any more
databases") The comboboxes I was talking about are not in the subform they
are on the main form.
Nothing with aggregate functions in any of the queries or forms or
modules.

Could you remove the code that sets the RecordSouce, and just set it in the
property?

Anything in qry_Union_Single_Weekly_Monthly that uses domain aggregate
functions?

Hmm. Now you have a query as the rowsource for the combo, and the combo
is
in the subform? If so, and there are 37 instances of this subform, then
it
is loading 37 instances of the same query. That should work okay, but if you
were loading hundreds of instances of the same query, you would have to work
around that by using a callback function as the RowSourceType for the combo.
The callback function would load one array of the values when
initialized,
and would respond to the calls from the different combo instances in the
different subform instances by just supplying the value out of the array.

Before proceeding down that path, just verify that this is the problem,
because callbacks are not the most obvious things if you have not worked
with them before. Remove the RowSource from the combo, and check that
this
solves the problem.

If it does, this is the kind of thing you will need:

Function ListEmpl(fld As Control, id As Variant, _
row As Variant, col As Variant, code As Variant) As Variant
' Purpose: To return a list of Employees for a combo.
' Rationale: Workaround for "Cannot open more tables" error, when *many*
combos
' (perhaps 100) try to read directly from table/query.
' Arguments: As required to fill combo box.
' Usage: RowSourceType property of Combo.

Const MaxEntries = 256 'Max number of employees to cater for.
Dim rst As DAO.Recordset
Static iEntries As Integer ' Number of Employees found.
Static iInstances As Integer ' Number of currently opened
instances.
Static sEmpl(MaxEntries, 1) As String ' Array of EmplID and name.

Select Case code
Case acLBInitialize ' Initialize.
Set rst = CurrentDb().OpenRecordset("tblEmployee")
iEntries = 0
Do While iEntries < MaxEntries And Not rst.EOF
sEmpl(iEntries, 0) = rst![EmplID]
sEmpl(iEntries, 1) = rst![Surname] & ", " & rst![FirstName]
iEntries = iEntries + 1
rst.MoveNext
Loop
rst.Close
iInstances = iInstances + 1
ListEmpl = True
Case acLBOpen ' Open.
ListEmpl = Timer
Case acLBGetRowCount ' Get rows.
ListEmpl = iEntries
Case acLBGetColumnCount ' Get columns.
ListEmpl = 2
Case acLBGetColumnWidth ' Get column width.
If col = 0 Then ListEmpl = 0 Else ListEmpl = 1440
Case acLBGetValue ' Get the data.
ListEmpl = sEmpl(row, col)
Case acLBEnd ' End.
iInstances = iInstances - 1
If iInstances < 1 Then
Erase sEmpl
iEntries = 0
End If
End Select
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,
I do not have any of the situations you asked for.

The recordsource of the form is set when it opens:
Form.RecordSource = qry_Union_Single_Weekly_Monthly

The rowsource of the combobox is:
SELECT DISTINCTROW Employees.EmployeeID, [EmployeeLastname] & ", " &
[EmployeeFirstName] AS EmployeeFullName FROM Employees ORDER BY
[EmployeeLastname] & ", " & [EmployeeFirstName];


Okay: now we have a different problem: what is opening all these
databases,
and not closing them?

Are you using domain aggregate functions such as DLookup() in the
RecordSource of the form? In the RowSource of the combos? These don't
clean
up after themselves, and there is a replacement here that does clean
up
(and
runs faster):
http://members.iinet.net.au/~allenbrowne/ser-42.html

Do you have code that opens recordsets (or opens databases) and does not
Close them AND set them to nothing? You need to explicitly close
everything
you open (but only what you open), and set all object variables to
Nothing
(preferably in the error recovery section so they are dereferenced
even
after an error).


Allen,
I have reworked my form and it still will not work over my network. My
main
data tables are on the server. When running the forms on the server
everything works great...but on another machine using linked tables..I
get
the error "Can not open any more databases" Looks like it processes
about
half of the subforms. Maybe I need to add text boxes at each date
location
instead of using subforms. I can populate the textboxes with the query
data
but I will not have a separate record to click on to open the edit
record
form. Any more suggestions?

 

[Jeff]

Allen,
I created a TestTable using records from the query and set the record source
od the subform to the TestTable. The TestTable is linked to the main file on
the server.This works great.....so the problem must be in using a query in
the subform. It must be rerunning the query for every subform. Is there a
way I can set up some kind of recordset? I was also wandering if there is a
way to make the query a snapshot...would this help? I set the subform to
snapshot, no change.

Not sure what else to suggest, Jeff.

If there are only a couple of combos on the main form, then messing with the
callback function will not be worthwhile. Perhaps there is something in your
code that is not closing what it opened, or calling things recursively, or
has a circular dependency.

It may be a process of elimination to trace down what it is, i.e. make a
copy of the database, and start removing things until you identify what is
causing the problem.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

My query prompts for cbo_EmployeeName and cbo_ClientName. So if I set the
RecordSource Property of the main form to the query it prompts me at the
form's open. OK..for experimental purposes I have eliminated the prompts
from the query and removed the code that sets it at forms open and again
tried to open the form....same perplexing result. ("Cant open any more
databases") The comboboxes I was talking about are not in the subform they
are on the main form.
Nothing with aggregate functions in any of the queries or forms or
modules.

Could you remove the code that sets the RecordSouce, and just set it in the
property?

Anything in qry_Union_Single_Weekly_Monthly that uses domain aggregate
functions?

Hmm. Now you have a query as the rowsource for the combo, and the combo
is
in the subform? If so, and there are 37 instances of this subform, then
it
is loading 37 instances of the same query. That should work okay, but

if
you

were loading hundreds of instances of the same query, you would have to work
around that by using a callback function as the RowSourceType for the combo.
The callback function would load one array of the values when
initialized,
and would respond to the calls from the different combo instances in the
different subform instances by just supplying the value out of the array.

Before proceeding down that path, just verify that this is the problem,
because callbacks are not the most obvious things if you have not worked
with them before. Remove the RowSource from the combo, and check that
this
solves the problem.

If it does, this is the kind of thing you will need:

Function ListEmpl(fld As Control, id As Variant, _
row As Variant, col As Variant, code As Variant) As Variant
' Purpose: To return a list of Employees for a combo.
' Rationale: Workaround for "Cannot open more tables" error, when *many*
combos
' (perhaps 100) try to read directly from table/query.
' Arguments: As required to fill combo box.
' Usage: RowSourceType property of Combo.

Const MaxEntries = 256 'Max number of employees to cater for.
Dim rst As DAO.Recordset
Static iEntries As Integer ' Number of Employees found.
Static iInstances As Integer ' Number of currently opened
instances.
Static sEmpl(MaxEntries, 1) As String ' Array of EmplID and name.

Select Case code
Case acLBInitialize ' Initialize.
Set rst = CurrentDb().OpenRecordset("tblEmployee")
iEntries = 0
Do While iEntries < MaxEntries And Not rst.EOF
sEmpl(iEntries, 0) = rst![EmplID]
sEmpl(iEntries, 1) = rst![Surname] & ", " & rst![FirstName]
iEntries = iEntries + 1
rst.MoveNext
Loop
rst.Close
iInstances = iInstances + 1
ListEmpl = True
Case acLBOpen ' Open.
ListEmpl = Timer
Case acLBGetRowCount ' Get rows.
ListEmpl = iEntries
Case acLBGetColumnCount ' Get columns.
ListEmpl = 2
Case acLBGetColumnWidth ' Get column width.
If col = 0 Then ListEmpl = 0 Else ListEmpl = 1440
Case acLBGetValue ' Get the data.
ListEmpl = sEmpl(row, col)
Case acLBEnd ' End.
iInstances = iInstances - 1
If iInstances < 1 Then
Erase sEmpl
iEntries = 0
End If
End Select
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
Allen,
I do not have any of the situations you asked for.

The recordsource of the form is set when it opens:
Form.RecordSource = qry_Union_Single_Weekly_Monthly

The rowsource of the combobox is:
SELECT DISTINCTROW Employees.EmployeeID, [EmployeeLastname] & ", " &
[EmployeeFirstName] AS EmployeeFullName FROM Employees ORDER BY
[EmployeeLastname] & ", " & [EmployeeFirstName];


Okay: now we have a different problem: what is opening all these
databases,
and not closing them?

Are you using domain aggregate functions such as DLookup() in the
RecordSource of the form? In the RowSource of the combos? These don't
clean
up after themselves, and there is a replacement here that does clean
up
(and
runs faster):
http://members.iinet.net.au/~allenbrowne/ser-42.html

Do you have code that opens recordsets (or opens databases) and does not
Close them AND set them to nothing? You need to explicitly close
everything
you open (but only what you open), and set all object variables to
Nothing
(preferably in the error recovery section so they are dereferenced
even
after an error).


Allen,
I have reworked my form and it still will not work over my

network.
My

main
data tables are on the server. When running the forms on the server
everything works great...but on another machine using linked tables..I
get
the error "Can not open any more databases" Looks like it processes
about
half of the subforms. Maybe I need to add text boxes at each date
location
instead of using subforms. I can populate the textboxes with the query
data
but I will not have a separate record to click on to open the edit
record
form. Any more suggestions?

 

[Allen Browne]

Yes, each subform needs to have its own independent recordset, since they
are differerent records for each date.

The fact that it worked directly from the TestTable does demonstrate that
the problem is with the query. If it is a query based on another query and
somewhere down the chain it is doing some aggregation, then yes, that's the
kind of thing that would be worth tracing further.

I doubt that a snapshot would be useful in a form. If you don't mind a
read-only result, you might be able to use a subquery (query statement
within the main query) instead of a stacked query (query that uses another
query as an input "table").

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,
I created a TestTable using records from the query and set the record
source
od the subform to the TestTable. The TestTable is linked to the main file
on
the server.This works great.....so the problem must be in using a query in
the subform. It must be rerunning the query for every subform. Is there
a
way I can set up some kind of recordset? I was also wandering if there is
a
way to make the query a snapshot...would this help? I set the subform to
snapshot, no change.

Not sure what else to suggest, Jeff.

If there are only a couple of combos on the main form, then messing with the
callback function will not be worthwhile. Perhaps there is something in your
code that is not closing what it opened, or calling things recursively,
or
has a circular dependency.

It may be a process of elimination to trace down what it is, i.e. make a
copy of the database, and start removing things until you identify what
is
causing the problem.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

My query prompts for cbo_EmployeeName and cbo_ClientName. So if I set the
RecordSource Property of the main form to the query it prompts me at
the
form's open. OK..for experimental purposes I have eliminated the prompts
from the query and removed the code that sets it at forms open and
again
tried to open the form....same perplexing result. ("Cant open any more
databases") The comboboxes I was talking about are not in the subform they
are on the main form.
Nothing with aggregate functions in any of the queries or forms or
modules.



Could you remove the code that sets the RecordSouce, and just set it
in
the
property?

Anything in qry_Union_Single_Weekly_Monthly that uses domain aggregate
functions?

Hmm. Now you have a query as the rowsource for the combo, and the
combo
is
in the subform? If so, and there are 37 instances of this subform,
then
it
is loading 37 instances of the same query. That should work okay, but if
you
were loading hundreds of instances of the same query, you would have
to
work
around that by using a callback function as the RowSourceType for the
combo.
The callback function would load one array of the values when
initialized,
and would respond to the calls from the different combo instances in the
different subform instances by just supplying the value out of the array.

Before proceeding down that path, just verify that this is the
problem,
because callbacks are not the most obvious things if you have not worked
with them before. Remove the RowSource from the combo, and check that
this
solves the problem.

If it does, this is the kind of thing you will need:

Function ListEmpl(fld As Control, id As Variant, _
row As Variant, col As Variant, code As Variant) As Variant
' Purpose: To return a list of Employees for a combo.
' Rationale: Workaround for "Cannot open more tables" error, when
*many*
combos
' (perhaps 100) try to read directly from table/query.
' Arguments: As required to fill combo box.
' Usage: RowSourceType property of Combo.

Const MaxEntries = 256 'Max number of employees to cater for.
Dim rst As DAO.Recordset
Static iEntries As Integer ' Number of Employees found.
Static iInstances As Integer ' Number of currently opened
instances.
Static sEmpl(MaxEntries, 1) As String ' Array of EmplID and name.

Select Case code
Case acLBInitialize ' Initialize.
Set rst = CurrentDb().OpenRecordset("tblEmployee")
iEntries = 0
Do While iEntries < MaxEntries And Not rst.EOF
sEmpl(iEntries, 0) = rst![EmplID]
sEmpl(iEntries, 1) = rst![Surname] & ", " &
rst![FirstName]
iEntries = iEntries + 1
rst.MoveNext
Loop
rst.Close
iInstances = iInstances + 1
ListEmpl = True
Case acLBOpen ' Open.
ListEmpl = Timer
Case acLBGetRowCount ' Get rows.
ListEmpl = iEntries
Case acLBGetColumnCount ' Get columns.
ListEmpl = 2
Case acLBGetColumnWidth ' Get column width.
If col = 0 Then ListEmpl = 0 Else ListEmpl = 1440
Case acLBGetValue ' Get the data.
ListEmpl = sEmpl(row, col)
Case acLBEnd ' End.
iInstances = iInstances - 1
If iInstances < 1 Then
Erase sEmpl
iEntries = 0
End If
End Select
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
Allen,
I do not have any of the situations you asked for.

The recordsource of the form is set when it opens:
Form.RecordSource = qry_Union_Single_Weekly_Monthly

The rowsource of the combobox is:
SELECT DISTINCTROW Employees.EmployeeID, [EmployeeLastname] & ", " &
[EmployeeFirstName] AS EmployeeFullName FROM Employees ORDER BY
[EmployeeLastname] & ", " & [EmployeeFirstName];


Okay: now we have a different problem: what is opening all these
databases,
and not closing them?

Are you using domain aggregate functions such as DLookup() in the
RecordSource of the form? In the RowSource of the combos? These don't
clean
up after themselves, and there is a replacement here that does
clean
up
(and
runs faster):
http://members.iinet.net.au/~allenbrowne/ser-42.html

Do you have code that opens recordsets (or opens databases) and
does
not
Close them AND set them to nothing? You need to explicitly close
everything
you open (but only what you open), and set all object variables to
Nothing
(preferably in the error recovery section so they are dereferenced
even
after an error).


Allen,
I have reworked my form and it still will not work over my network.
My
main
data tables are on the server. When running the forms on the server
everything works great...but on another machine using linked
tables..I
get
the error "Can not open any more databases" Looks like it processes
about
half of the subforms. Maybe I need to add text boxes at each
date
location
instead of using subforms. I can populate the textboxes with the
query
data
but I will not have a separate record to click on to open the
edit
record
form. Any more suggestions?