Subform Performance

[Larry Salvucci]

I have an unbound form that has 9 subforms on it. It loads VERY slow and I
was hoping there was a way to speed this up. All the subforms load from
queries but not all of them have data in them since they are all set up with
specific criteria's. Most of them are date driven so depending on the current
date will determine if there are records shown in them. Is there a way to not
load the subforms that don't have any records in them? My DB is still a work
in process so it's not split yet. But if the performance is this slow for
this form now I can only imagine how slow it will be once I split it and put
it on the server. Any ideas?

 

[Allen Browne]

First thing to do would be to make sure you have indexed your fields
correctly. For example, if you are filtering on a date/time field, make sure
that field is indexed in your table. (Lower pane, in table design.)

Next, make sure the data types are right. For exmaple, if you are using Text
fields to store the dates, ...

From there, it might be a matter of designing the criteria so they can use
the indexes.

(This all assumes a suitable relational structure.)

There are other workaround. For example, if you only need to see one subform
at a time, you may be able to use a single subform control and change its
SourceObject rather than load 9 subforms.

 

[Larry Salvucci]

Hello Allen,

There were some date/time fields that were not indexed. I corrected those
and tested it again. It took about 15 seconds to load the form. I'm using the
correct data types for my date fields.

What do you mean by designing the criteria so they use the indexes?

 

[Allen Browne]

Do the queries include any criteria? Some criteria can use indexes; others
can't e.g. text fields with leading wildcards can't; vba function calls
can't.

Also, what's in the LinkMasterFields/LinkChildFields of these subforms? Are
these expressions with defined data types, that can use the indexes?

 

[Larry Salvucci]

Here is an example of one of the queries I'm using. All 9 of them are
basically the same except that they pull from different tables, etc.

SELECT tblEmployees.ID, [LastName] & ", " & [FirstName] AS Name,
tblEmployees.NextReviewDate, tblDepartment.Department
FROM tblDepartment INNER JOIN tblEmployees ON tblDepartment.DeptID =
tblEmployees.Dept
WHERE (((tblEmployees.NextReviewDate)<=Now())) OR
(((tblEmployees.NextReviewDate)<=Now()));

I don't have any LinkMasterFields/LinkChildFields since the main form is
unbound. If it was bound would that make these load faster?

 

[Allen Browne]

Not sure why the criterion is repeated (with an OR.)
Presumably tblEmployees.NextReviewDate is indexed.
Date() might be more efficient than Now().
Presumably tblEmployees.Dept is the same data type, and you have a
relationship with Referential Integrity to tblDepartment.DeptID.
You are not doing any sorting in this query, so that's not slowing it down.
I would be inclined to change the alias to EmpName or FullName or something,
as Name is know to confuse Access. (In some contexts, it may think you mean
the name of the form/report rather than the contents of the field named
Name.)

There's nothing in that example that would cause it to run slowly.

Binding the main form won't make it work faster.

Using one subform and changing the SourceObject may still speed it up by an
order of magnitude if you have 10 subforms.

For other general suggestions, work through the items in Tony Toews' Access
Performance FAQ here:
http://www.granite.ab.ca/access/performancefaq.htm

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

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

Here is an example of one of the queries I'm using. All 9 of them are
basically the same except that they pull from different tables, etc.

SELECT tblEmployees.ID,
[LastName] & ", " & [FirstName] AS Name,
tblEmployees.NextReviewDate,
tblDepartment.Department
FROM tblDepartment INNER JOIN tblEmployees
ON tblDepartment.DeptID = tblEmployees.Dept
WHERE (((tblEmployees.NextReviewDate)<=Now()))
OR (((tblEmployees.NextReviewDate)<=Now()));

 

[Larry Salvucci]

I forgot to remove the OR criteria back when I modified this query.
Yes the NextReviewDate is indexed. I changed the Name to EmpName as you
mentioned. I also changed the Now() to Date().

It must be because I'm loading all 9 of them on one form then. Can you shed
some more light on your idea of changing the SourceObject and just using one
subform? That might be the best way to go at this point?

Not sure why the criterion is repeated (with an OR.)
Presumably tblEmployees.NextReviewDate is indexed.
Date() might be more efficient than Now().
Presumably tblEmployees.Dept is the same data type, and you have a
relationship with Referential Integrity to tblDepartment.DeptID.
You are not doing any sorting in this query, so that's not slowing it down.
I would be inclined to change the alias to EmpName or FullName or something,
as Name is know to confuse Access. (In some contexts, it may think you mean
the name of the form/report rather than the contents of the field named
Name.)

There's nothing in that example that would cause it to run slowly.

Binding the main form won't make it work faster.

Using one subform and changing the SourceObject may still speed it up by an
order of magnitude if you have 10 subforms.

For other general suggestions, work through the items in Tony Toews' Access
Performance FAQ here:
http://www.granite.ab.ca/access/performancefaq.htm

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

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

Here is an example of one of the queries I'm using. All 9 of them are
basically the same except that they pull from different tables, etc.

SELECT tblEmployees.ID,
[LastName] & ", " & [FirstName] AS Name,
tblEmployees.NextReviewDate,
tblDepartment.Department
FROM tblDepartment INNER JOIN tblEmployees
ON tblDepartment.DeptID = tblEmployees.Dept
WHERE (((tblEmployees.NextReviewDate)<=Now()))
OR (((tblEmployees.NextReviewDate)<=Now()));

I don't have any LinkMasterFields/LinkChildFields since the main form is
unbound. If it was bound would that make these load faster?

 

[Allen Browne]

Open your main form in design view.
Right-click the edge of the first subform, and choose Properties.
On the Other tab of the properties box, change the Name of the subform
control to something like fSubGeneric.

Now you can code:
Me.fSubGeneric.SourceObject = "Form2"
to load Form2 into the subform control.
At some other point:
Me.fSubGeneric.SourceObject = "Form3"

One way to do this is to use a tab control that's only about 1" high, above
the subform control. (The subform control sits directly on the form; it is
not on any of the pages of the tab control.) Now set the Tag property of
each of the pages on the tab control to the name of the form you want loaded
when that page is clicked. For example, if you want to load Form2 when Page2
is clicked, set the Tag property of Page2 to:
Form2

Name the tab control tabMain
Set its On Change property to [Event Procedure]
Click the Build button beside this.
Access opens the code window.
Put this code in:

Private Sub tabMain_Change()
On Error GoTo Err_tabMain_Change
'Purpose: Load the desired subform in to the generic subform control.
Dim bHide As Boolean

With Me.tabMain.Pages(Me.tabMain.Value)
If .Tag <> vbNullString Then
Me.fsubGeneric.SourceObject = .Tag
Else
Debug.Print Me.Name & " page " & .Name & " has no Tag."
End If
End With

Exit_tabMain_Change:
Exit Sub

Err_tabMain_Change:
If Err.Number = 2101 Then 'The setting you entered isn't valid for this
property.
MsgBox "Unable to display this tab.", vbInformation, "Sorry..."
Else
MsgBox "Error " & err.Number & ": " & Err.Description
End If
Resume Exit_tabMain_Change
End Sub

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

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

I forgot to remove the OR criteria back when I modified this query.
Yes the NextReviewDate is indexed. I changed the Name to EmpName as you
mentioned. I also changed the Now() to Date().

It must be because I'm loading all 9 of them on one form then. Can you
shed
some more light on your idea of changing the SourceObject and just using
one
subform? That might be the best way to go at this point?

Not sure why the criterion is repeated (with an OR.)
Presumably tblEmployees.NextReviewDate is indexed.
Date() might be more efficient than Now().
Presumably tblEmployees.Dept is the same data type, and you have a
relationship with Referential Integrity to tblDepartment.DeptID.
You are not doing any sorting in this query, so that's not slowing it
down.
I would be inclined to change the alias to EmpName or FullName or
something,
as Name is know to confuse Access. (In some contexts, it may think you
mean
the name of the form/report rather than the contents of the field named
Name.)

There's nothing in that example that would cause it to run slowly.

Binding the main form won't make it work faster.

Using one subform and changing the SourceObject may still speed it up by
an
order of magnitude if you have 10 subforms.

For other general suggestions, work through the items in Tony Toews'
Access
Performance FAQ here:
http://www.granite.ab.ca/access/performancefaq.htm

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

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

message

Here is an example of one of the queries I'm using. All 9 of them are
basically the same except that they pull from different tables, etc.

SELECT tblEmployees.ID,
[LastName] & ", " & [FirstName] AS Name,
tblEmployees.NextReviewDate,
tblDepartment.Department
FROM tblDepartment INNER JOIN tblEmployees
ON tblDepartment.DeptID = tblEmployees.Dept
WHERE (((tblEmployees.NextReviewDate)<=Now()))
OR (((tblEmployees.NextReviewDate)<=Now()));

I don't have any LinkMasterFields/LinkChildFields since the main form
is
unbound. If it was bound would that make these load faster?

:

Do the queries include any criteria? Some criteria can use indexes;
others
can't e.g. text fields with leading wildcards can't; vba function
calls
can't.

Also, what's in the LinkMasterFields/LinkChildFields of these
subforms?
Are
these expressions with defined data types, that can use the indexes?

message
Hello Allen,

There were some date/time fields that were not indexed. I corrected
those
and tested it again. It took about 15 seconds to load the form. I'm
using
the
correct data types for my date fields.

What do you mean by designing the criteria so they use the indexes?

:

First thing to do would be to make sure you have indexed your
fields
correctly. For example, if you are filtering on a date/time field,
make
sure
that field is indexed in your table. (Lower pane, in table design.)

Next, make sure the data types are right. For exmaple, if you are
using
Text
fields to store the dates, ...

From there, it might be a matter of designing the criteria so they
can
use
the indexes.

(This all assumes a suitable relational structure.)

There are other workaround. For example, if you only need to see
one
subform
at a time, you may be able to use a single subform control and
change
its
SourceObject rather than load 9 subforms.

message
I have an unbound form that has 9 subforms on it. It loads VERY
slow
and
I
was hoping there was a way to speed this up. All the subforms
load
from
queries but not all of them have data in them since they are all
set
up
with
specific criteria's. Most of them are date driven so depending on
the
current
date will determine if there are records shown in them. Is there
a
way
to
not
load the subforms that don't have any records in them? My DB is
still a
work
in process so it's not split yet. But if the performance is this
slow
for
this form now I can only imagine how slow it will be once I split
it
and
put
it on the server. Any ideas?

 

[Larry Salvucci]

I understand what you are saying. But a few questions.

1. Where exactly do I put this code:

Now you can code:
Me.fSubGeneric.SourceObject = "Form2"
to load Form2 into the subform control.
At some other point:
Me.fSubGeneric.SourceObject = "Form3"

2. Do all the controls on each of my 9 subforms have to be the same? For
example I have 6 subforms that have the same 3 controls on them, EmpName,
DateField, Department. The other 3 have EmpName but the other two are
different. Will this matter?

3. I assume I delete all the subforms except for one, correct? I'm only
going to use one subform to load whichever tab I click on, correct?

LS

Open your main form in design view.
Right-click the edge of the first subform, and choose Properties.
On the Other tab of the properties box, change the Name of the subform
control to something like fSubGeneric.

Now you can code:
Me.fSubGeneric.SourceObject = "Form2"
to load Form2 into the subform control.
At some other point:
Me.fSubGeneric.SourceObject = "Form3"

One way to do this is to use a tab control that's only about 1" high, above
the subform control. (The subform control sits directly on the form; it is
not on any of the pages of the tab control.) Now set the Tag property of
each of the pages on the tab control to the name of the form you want loaded
when that page is clicked. For example, if you want to load Form2 when Page2
is clicked, set the Tag property of Page2 to:
Form2

Name the tab control tabMain
Set its On Change property to [Event Procedure]
Click the Build button beside this.
Access opens the code window.
Put this code in:

Private Sub tabMain_Change()
On Error GoTo Err_tabMain_Change
'Purpose: Load the desired subform in to the generic subform control.
Dim bHide As Boolean

With Me.tabMain.Pages(Me.tabMain.Value)
If .Tag <> vbNullString Then
Me.fsubGeneric.SourceObject = .Tag
Else
Debug.Print Me.Name & " page " & .Name & " has no Tag."
End If
End With

Exit_tabMain_Change:
Exit Sub

Err_tabMain_Change:
If Err.Number = 2101 Then 'The setting you entered isn't valid for this
property.
MsgBox "Unable to display this tab.", vbInformation, "Sorry..."
Else
MsgBox "Error " & err.Number & ": " & Err.Description
End If
Resume Exit_tabMain_Change
End Sub

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

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

I forgot to remove the OR criteria back when I modified this query.
Yes the NextReviewDate is indexed. I changed the Name to EmpName as you
mentioned. I also changed the Now() to Date().

It must be because I'm loading all 9 of them on one form then. Can you
shed
some more light on your idea of changing the SourceObject and just using
one
subform? That might be the best way to go at this point?

Not sure why the criterion is repeated (with an OR.)
Presumably tblEmployees.NextReviewDate is indexed.
Date() might be more efficient than Now().
Presumably tblEmployees.Dept is the same data type, and you have a
relationship with Referential Integrity to tblDepartment.DeptID.
You are not doing any sorting in this query, so that's not slowing it
down.
I would be inclined to change the alias to EmpName or FullName or
something,
as Name is know to confuse Access. (In some contexts, it may think you
mean
the name of the form/report rather than the contents of the field named
Name.)

There's nothing in that example that would cause it to run slowly.

Binding the main form won't make it work faster.

Using one subform and changing the SourceObject may still speed it up by
an
order of magnitude if you have 10 subforms.

For other general suggestions, work through the items in Tony Toews'
Access
Performance FAQ here:
http://www.granite.ab.ca/access/performancefaq.htm

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

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

message
Here is an example of one of the queries I'm using. All 9 of them are
basically the same except that they pull from different tables, etc.

SELECT tblEmployees.ID,
[LastName] & ", " & [FirstName] AS Name,
tblEmployees.NextReviewDate,
tblDepartment.Department
FROM tblDepartment INNER JOIN tblEmployees
ON tblDepartment.DeptID = tblEmployees.Dept
WHERE (((tblEmployees.NextReviewDate)<=Now()))
OR (((tblEmployees.NextReviewDate)<=Now()));

I don't have any LinkMasterFields/LinkChildFields since the main form
is
unbound. If it was bound would that make these load faster?

:

Do the queries include any criteria? Some criteria can use indexes;
others
can't e.g. text fields with leading wildcards can't; vba function
calls
can't.

Also, what's in the LinkMasterFields/LinkChildFields of these
subforms?
Are
these expressions with defined data types, that can use the indexes?

message
Hello Allen,

There were some date/time fields that were not indexed. I corrected
those
and tested it again. It took about 15 seconds to load the form. I'm
using
the
correct data types for my date fields.

What do you mean by designing the criteria so they use the indexes?

:

First thing to do would be to make sure you have indexed your
fields
correctly. For example, if you are filtering on a date/time field,
make
sure
that field is indexed in your table. (Lower pane, in table design.)

Next, make sure the data types are right. For exmaple, if you are
using
Text
fields to store the dates, ...

From there, it might be a matter of designing the criteria so they
can
use
the indexes.

(This all assumes a suitable relational structure.)

There are other workaround. For example, if you only need to see
one
subform
at a time, you may be able to use a single subform control and
change
its
SourceObject rather than load 9 subforms.

message
I have an unbound form that has 9 subforms on it. It loads VERY
slow
and
I
was hoping there was a way to speed this up. All the subforms
load
from
queries but not all of them have data in them since they are all
set
up
with
specific criteria's. Most of them are date driven so depending on
the
current
date will determine if there are records shown in them. Is there
a
way
to
not
load the subforms that don't have any records in them? My DB is
still a
work
in process so it's not split yet. But if the performance is this
slow
for
this form now I can only imagine how slow it will be once I split
it
and
put
it on the server. Any ideas?

 

[Larry Salvucci]

Hi Allen,

After reading some more on Tony's website about how to improve performance
on forms I was able to cut the time down to 7 seconds. Not too bad! I ended
up using his RecordSource modification to put the recordsource in code rather
than on the form directly. I'd still like more info on your customization. I
still might want to use that instead. I did post another response prior to
this with some follow up questions regarding your code post.

LS

Open your main form in design view.
Right-click the edge of the first subform, and choose Properties.
On the Other tab of the properties box, change the Name of the subform
control to something like fSubGeneric.

Now you can code:
Me.fSubGeneric.SourceObject = "Form2"
to load Form2 into the subform control.
At some other point:
Me.fSubGeneric.SourceObject = "Form3"

One way to do this is to use a tab control that's only about 1" high, above
the subform control. (The subform control sits directly on the form; it is
not on any of the pages of the tab control.) Now set the Tag property of
each of the pages on the tab control to the name of the form you want loaded
when that page is clicked. For example, if you want to load Form2 when Page2
is clicked, set the Tag property of Page2 to:
Form2

Name the tab control tabMain
Set its On Change property to [Event Procedure]
Click the Build button beside this.
Access opens the code window.
Put this code in:

Private Sub tabMain_Change()
On Error GoTo Err_tabMain_Change
'Purpose: Load the desired subform in to the generic subform control.
Dim bHide As Boolean

With Me.tabMain.Pages(Me.tabMain.Value)
If .Tag <> vbNullString Then
Me.fsubGeneric.SourceObject = .Tag
Else
Debug.Print Me.Name & " page " & .Name & " has no Tag."
End If
End With

Exit_tabMain_Change:
Exit Sub

Err_tabMain_Change:
If Err.Number = 2101 Then 'The setting you entered isn't valid for this
property.
MsgBox "Unable to display this tab.", vbInformation, "Sorry..."
Else
MsgBox "Error " & err.Number & ": " & Err.Description
End If
Resume Exit_tabMain_Change
End Sub

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

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

I forgot to remove the OR criteria back when I modified this query.
Yes the NextReviewDate is indexed. I changed the Name to EmpName as you
mentioned. I also changed the Now() to Date().

It must be because I'm loading all 9 of them on one form then. Can you
shed
some more light on your idea of changing the SourceObject and just using
one
subform? That might be the best way to go at this point?

Not sure why the criterion is repeated (with an OR.)
Presumably tblEmployees.NextReviewDate is indexed.
Date() might be more efficient than Now().
Presumably tblEmployees.Dept is the same data type, and you have a
relationship with Referential Integrity to tblDepartment.DeptID.
You are not doing any sorting in this query, so that's not slowing it
down.
I would be inclined to change the alias to EmpName or FullName or
something,
as Name is know to confuse Access. (In some contexts, it may think you
mean
the name of the form/report rather than the contents of the field named
Name.)

There's nothing in that example that would cause it to run slowly.

Binding the main form won't make it work faster.

Using one subform and changing the SourceObject may still speed it up by
an
order of magnitude if you have 10 subforms.

For other general suggestions, work through the items in Tony Toews'
Access
Performance FAQ here:
http://www.granite.ab.ca/access/performancefaq.htm

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

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

message
Here is an example of one of the queries I'm using. All 9 of them are
basically the same except that they pull from different tables, etc.

SELECT tblEmployees.ID,
[LastName] & ", " & [FirstName] AS Name,
tblEmployees.NextReviewDate,
tblDepartment.Department
FROM tblDepartment INNER JOIN tblEmployees
ON tblDepartment.DeptID = tblEmployees.Dept
WHERE (((tblEmployees.NextReviewDate)<=Now()))
OR (((tblEmployees.NextReviewDate)<=Now()));

I don't have any LinkMasterFields/LinkChildFields since the main form
is
unbound. If it was bound would that make these load faster?

:

Do the queries include any criteria? Some criteria can use indexes;
others
can't e.g. text fields with leading wildcards can't; vba function
calls
can't.

Also, what's in the LinkMasterFields/LinkChildFields of these
subforms?
Are
these expressions with defined data types, that can use the indexes?

message
Hello Allen,

There were some date/time fields that were not indexed. I corrected
those
and tested it again. It took about 15 seconds to load the form. I'm
using
the
correct data types for my date fields.

What do you mean by designing the criteria so they use the indexes?

:

First thing to do would be to make sure you have indexed your
fields
correctly. For example, if you are filtering on a date/time field,
make
sure
that field is indexed in your table. (Lower pane, in table design.)

Next, make sure the data types are right. For exmaple, if you are
using
Text
fields to store the dates, ...

From there, it might be a matter of designing the criteria so they
can
use
the indexes.

(This all assumes a suitable relational structure.)

There are other workaround. For example, if you only need to see
one
subform
at a time, you may be able to use a single subform control and
change
its
SourceObject rather than load 9 subforms.

message
I have an unbound form that has 9 subforms on it. It loads VERY
slow
and
I
was hoping there was a way to speed this up. All the subforms
load
from
queries but not all of them have data in them since they are all
set
up
with
specific criteria's. Most of them are date driven so depending on
the
current
date will determine if there are records shown in them. Is there
a
way
to
not
load the subforms that don't have any records in them? My DB is
still a
work
in process so it's not split yet. But if the performance is this
slow
for
this form now I can only imagine how slow it will be once I split
it
and
put
it on the server. Any ideas?

 

[Allen Browne]

There is a difference between a subform control, and the form loaded into
the subform control.

If you look on the Forms tab of the database window (Or in the Navigation
Pane if you use A2007), you will see a form for each of your subforms. You
don't delete these.

If you look on your main form in design view, you will see 9 subform
controls. Delete 8 of them. Change the SourceObject of the remaining one,
and you will see a different form being loaded into it. The other form does
not need to have the same controls in it: it's a completely different saved
form that Access loads into the same subform control.

To answer your specific questions:
#1: Whatever event suits you.
I suggested the Change of a tab control.

#2 No.

#3 Yes. (Though the subform is not in the tab control: it sits directly on
the form.)

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

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

I understand what you are saying. But a few questions.

1. Where exactly do I put this code:

Now you can code:
Me.fSubGeneric.SourceObject = "Form2"
to load Form2 into the subform control.
At some other point:
Me.fSubGeneric.SourceObject = "Form3"

2. Do all the controls on each of my 9 subforms have to be the same? For
example I have 6 subforms that have the same 3 controls on them, EmpName,
DateField, Department. The other 3 have EmpName but the other two are
different. Will this matter?

3. I assume I delete all the subforms except for one, correct? I'm only
going to use one subform to load whichever tab I click on, correct?

LS

Open your main form in design view.
Right-click the edge of the first subform, and choose Properties.
On the Other tab of the properties box, change the Name of the subform
control to something like fSubGeneric.

Now you can code:
Me.fSubGeneric.SourceObject = "Form2"
to load Form2 into the subform control.
At some other point:
Me.fSubGeneric.SourceObject = "Form3"

One way to do this is to use a tab control that's only about 1" high,
above
the subform control. (The subform control sits directly on the form; it
is
not on any of the pages of the tab control.) Now set the Tag property of
each of the pages on the tab control to the name of the form you want
loaded
when that page is clicked. For example, if you want to load Form2 when
Page2
is clicked, set the Tag property of Page2 to:
Form2

Name the tab control tabMain
Set its On Change property to [Event Procedure]
Click the Build button beside this.
Access opens the code window.
Put this code in:

Private Sub tabMain_Change()
On Error GoTo Err_tabMain_Change
'Purpose: Load the desired subform in to the generic subform control.
Dim bHide As Boolean

With Me.tabMain.Pages(Me.tabMain.Value)
If .Tag <> vbNullString Then
Me.fsubGeneric.SourceObject = .Tag
Else
Debug.Print Me.Name & " page " & .Name & " has no Tag."
End If
End With

Exit_tabMain_Change:
Exit Sub

Err_tabMain_Change:
If Err.Number = 2101 Then 'The setting you entered isn't valid for
this
property.
MsgBox "Unable to display this tab.", vbInformation, "Sorry..."
Else
MsgBox "Error " & err.Number & ": " & Err.Description
End If
Resume Exit_tabMain_Change
End Sub

message

I forgot to remove the OR criteria back when I modified this query.
Yes the NextReviewDate is indexed. I changed the Name to EmpName as you
mentioned. I also changed the Now() to Date().

It must be because I'm loading all 9 of them on one form then. Can you
shed
some more light on your idea of changing the SourceObject and just
using
one
subform? That might be the best way to go at this point?

 

[Larry Salvucci]

I probably should have waited before I posted my last response. I found out
why it's taking my form so long to load. There is 1 subform that is pulling
it's data from a union query. When I removed that subform the form with the 8
subforms loaded in 2 seconds. Obviously that's where the problem lies. It's
the union query that is slowing things down. Is there another way to speed
these types up? Here is what I'm using for a union query:

SELECT qrySummaryAttendance.EmpName, qrySummaryAttendance.CDate,
qrySummaryAttendance.Type, qrySummaryAttendance.Department
FROM qrySummaryAttendance
UNION SELECT ALL
qrySummaryPersonalTime.EmpName, qrySummaryPersonalTime.CDate,
qrySummaryPersonalTime.Type, qrySummaryPersonalTime.Department
FROM qrySummaryPersonalTime
UNION SELECT ALL
qrySummaryVacationTimeA.EmpName, qrySummaryVacationTimeA.CDate,
qrySummaryVacationTimeA.Type, qrySummaryVacationTimeA.Department
FROM qrySummaryVacationTimeA
UNION SELECT ALL
qrySummaryVacationTimeN.EmpName, qrySummaryVacationTimeN.CDate,
qrySummaryVacationTimeN.Type, qrySummaryVacationTimeN.Department
FROM qrySummaryVacationTimeN
UNION SELECT ALL
qrySummaryVacationTimeR.EmpName, qrySummaryVacationTimeR.CDate,
qrySummaryVacationTimeR.Type, qrySummaryVacationTimeR.Department
FROM qrySummaryVacationTimeR
UNION SELECT ALL
qrySummaryVacationTimeU.EmpName, qrySummaryVacationTimeU.CDate,
qrySummaryVacationTimeU.Type, qrySummaryVacationTimeU.Department
FROM qrySummaryVacationTimeU
ORDER BY EmpName;

Open your main form in design view.
Right-click the edge of the first subform, and choose Properties.
On the Other tab of the properties box, change the Name of the subform
control to something like fSubGeneric.

Now you can code:
Me.fSubGeneric.SourceObject = "Form2"
to load Form2 into the subform control.
At some other point:
Me.fSubGeneric.SourceObject = "Form3"

One way to do this is to use a tab control that's only about 1" high, above
the subform control. (The subform control sits directly on the form; it is
not on any of the pages of the tab control.) Now set the Tag property of
each of the pages on the tab control to the name of the form you want loaded
when that page is clicked. For example, if you want to load Form2 when Page2
is clicked, set the Tag property of Page2 to:
Form2

Name the tab control tabMain
Set its On Change property to [Event Procedure]
Click the Build button beside this.
Access opens the code window.
Put this code in:

Private Sub tabMain_Change()
On Error GoTo Err_tabMain_Change
'Purpose: Load the desired subform in to the generic subform control.
Dim bHide As Boolean

With Me.tabMain.Pages(Me.tabMain.Value)
If .Tag <> vbNullString Then
Me.fsubGeneric.SourceObject = .Tag
Else
Debug.Print Me.Name & " page " & .Name & " has no Tag."
End If
End With

Exit_tabMain_Change:
Exit Sub

Err_tabMain_Change:
If Err.Number = 2101 Then 'The setting you entered isn't valid for this
property.
MsgBox "Unable to display this tab.", vbInformation, "Sorry..."
Else
MsgBox "Error " & err.Number & ": " & Err.Description
End If
Resume Exit_tabMain_Change
End Sub

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

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

I forgot to remove the OR criteria back when I modified this query.
Yes the NextReviewDate is indexed. I changed the Name to EmpName as you
mentioned. I also changed the Now() to Date().

It must be because I'm loading all 9 of them on one form then. Can you
shed
some more light on your idea of changing the SourceObject and just using
one
subform? That might be the best way to go at this point?

Not sure why the criterion is repeated (with an OR.)
Presumably tblEmployees.NextReviewDate is indexed.
Date() might be more efficient than Now().
Presumably tblEmployees.Dept is the same data type, and you have a
relationship with Referential Integrity to tblDepartment.DeptID.
You are not doing any sorting in this query, so that's not slowing it
down.
I would be inclined to change the alias to EmpName or FullName or
something,
as Name is know to confuse Access. (In some contexts, it may think you
mean
the name of the form/report rather than the contents of the field named
Name.)

There's nothing in that example that would cause it to run slowly.

Binding the main form won't make it work faster.

Using one subform and changing the SourceObject may still speed it up by
an
order of magnitude if you have 10 subforms.

For other general suggestions, work through the items in Tony Toews'
Access
Performance FAQ here:
http://www.granite.ab.ca/access/performancefaq.htm

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

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

message
Here is an example of one of the queries I'm using. All 9 of them are
basically the same except that they pull from different tables, etc.

SELECT tblEmployees.ID,
[LastName] & ", " & [FirstName] AS Name,
tblEmployees.NextReviewDate,
tblDepartment.Department
FROM tblDepartment INNER JOIN tblEmployees
ON tblDepartment.DeptID = tblEmployees.Dept
WHERE (((tblEmployees.NextReviewDate)<=Now()))
OR (((tblEmployees.NextReviewDate)<=Now()));

I don't have any LinkMasterFields/LinkChildFields since the main form
is
unbound. If it was bound would that make these load faster?

:

Do the queries include any criteria? Some criteria can use indexes;
others
can't e.g. text fields with leading wildcards can't; vba function
calls
can't.

Also, what's in the LinkMasterFields/LinkChildFields of these
subforms?
Are
these expressions with defined data types, that can use the indexes?

message
Hello Allen,

There were some date/time fields that were not indexed. I corrected
those
and tested it again. It took about 15 seconds to load the form. I'm
using
the
correct data types for my date fields.

What do you mean by designing the criteria so they use the indexes?

:

First thing to do would be to make sure you have indexed your
fields
correctly. For example, if you are filtering on a date/time field,
make
sure
that field is indexed in your table. (Lower pane, in table design.)

Next, make sure the data types are right. For exmaple, if you are
using
Text
fields to store the dates, ...

From there, it might be a matter of designing the criteria so they
can
use
the indexes.

(This all assumes a suitable relational structure.)

There are other workaround. For example, if you only need to see
one
subform
at a time, you may be able to use a single subform control and
change
its
SourceObject rather than load 9 subforms.

message
I have an unbound form that has 9 subforms on it. It loads VERY
slow
and
I
was hoping there was a way to speed this up. All the subforms
load
from
queries but not all of them have data in them since they are all
set
up
with
specific criteria's. Most of them are date driven so depending on
the
current
date will determine if there are records shown in them. Is there
a
way
to
not
load the subforms that don't have any records in them? My DB is
still a
work
in process so it's not split yet. But if the performance is this
slow
for
this form now I can only imagine how slow it will be once I split
it
and
put
it on the server. Any ideas?

 

[Allen Browne]

Good: you've pinned the issue down, and that does make sense.

The solution will be to create a normalized design, where you don't have
multiple different tables all with similar structure. Whenever you see
tables that have very similar fields, it's worth asking why the data is not
in one table. That would avoid the need for the UNION query, make the result
updatable (if you need that), and let JET use the indexes (e.g. if you
filter the results.)

You may need an additional field to distinguish whatever is the reason for
the different tables.

If they are import data, perhaps you could use an Append query to write them
all to one table, which can then be your source.

Not sure what data you are modelling, so this may not be useful but it is an
example of keeping entities of a similar type in one table and relating them
to each other:
People in households and companies - modelling human relationships
at:
http://allenbrowne.com/AppHuman.html

 

[Larry Salvucci]

Hi Allen,

The reason for the multiple tables is because they are different types of
absences. Some are vacation days, some personal days, and some just
attendance entries. They all have different fields within their respective
tables. So I decided to create a query for each and then union them together
using the same syntax. I couldn't think of an easier way to have them all
into one table but a light bulb just appeared above my head with an idea that
just might work. It's a pretty bright light bulb too!

Just for clarification purposes if the union query was only pulling from 3
queries instead of 6 would that speed it up or do all union queries
regardless of the amount of unions take the same time to load?

I appreciate all your help in solving my problem. Working through it like
this definitely gives me the perspective from looking from the outside in.

 

[Allen Browne]

In general, a SELECT that has UNIONs will be quicker than one that UNIONs 6.
But it really depends on what filtering/sorting is being done after the
UNION.

FWIW, using UNION ALL is faster than UNION, because it doesn't do the
de-duplication.

Placing all the attendance entries in one table does sound like the better
idea, even if you have some other fields that are rarely populated. If there
are too many fields like that, it may be necessary to sub-class them.

 

[Tony Toews [MVP]]

I probably should have waited before I posted my last response. I found out
why it's taking my form so long to load. There is 1 subform that is pulling
it's data from a union query. When I removed that subform the form with the 8
subforms loaded in 2 seconds. Obviously that's where the problem lies.

If that subform is on it's own tab then only populate the record
source of the subform when the user clicks on the tab. Or make it a
separate called form.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/