slow due to multiple subforms

  • Thread starter Thread starter jenniferspnc
  • Start date Start date
J

jenniferspnc

I've done lots of reading and made changes (on the ones I understood) but my
form takes over a minute to open. I split the database, turned off track
name autocorrect info, and changed subdatasheet to [none].

I am confused on what to do now. I have a form with 10 tabs and 11
subforms. I read this following statement, but it didn't make sense to me,
as I'm not sure how to accomplish.

"a better solution, faster because of less overhead when loading, would be
to have *one* subform, set *under* the tab control so that it "shows through"
on each tab page. then change the subform's SourceObject property to the
appropriate table as you move from one tab to another. same effect from the
user's point of view, but will load faster than multiple subforms"



And I read the below but I'm completely confused on how to fill in.
http://www.granite.ab.ca/access/performanceforms.htm:
Delete the SQL from the RecordSource and RowSource properties of the form,
subforms, comboboxes and listboxes. Now in the Form_Load event load the
appropriate SQL as follows ...

Private Sub Form_Load()
Me.RecordSource = "qryLargeTable"
Me.txtSomeField.RowSource = _
"SELECT SomeField " & _
"FROM qryLargeTable " & _
"GROUP BY SomeField " & _
"ORDER BY SomeField;"
End Sub

The only part I get is that I open each subform and I'm supposed to remove
the "record source" and put code into the "Load Event" correct?

For example, my subform is based off the table "services". I have the
following fields in this subform: partner_id, yes, no, limited. Can someone
provide guidance so I can apply it to the rest of my forms/subforms?

Please let me know if additional detail is needed.

Thanks in advance!
 
I don't know your situation, but I would save each of the record sources of
your many subforms as stored queries, and leave the recordsource blank (with
the exception of the first tab). Then I would use the tabctl change event
to load each recordsource as the tab is selected. In this example, I have
stored the recordsource for tab1 (tabs are zero-based) as "qryInfotest":

Private Sub TabCtl0_Change()

Select Case TabCtl0.Value
Case 0
Case 1
Me!subfrmDetails.Form.RecordSource = "qryInfoTest"
Case 2
your next subform...
Case 3
etc......
End Select

End Sub

When the form is first loaded, it should open pretty quickly, as it has no
recordsources for your 11 subforms. Then as a tab is selected, the
recordsource for that tab only is loaded.

The example from the website is also a viable alternative. Mine is simple,
but each time the tab is selected, it goes thru the same routine, which
seems a bit redundant...

Damon
 
My apologies...I just saw your response and had posted a similar question
(which is your answer) this morning. So this appears to be what will solve
my problem.

Question: The line below. What do I input into the line below? I keep
getting errors so I'm not sure if it's the page name, subform name, form
name, or what.

Me!subfrmDetails.Form.RecordSource

Everything else is easy enough to understand.

Thanks again for your help.

Damon Heron said:
I don't know your situation, but I would save each of the record sources of
your many subforms as stored queries, and leave the recordsource blank (with
the exception of the first tab). Then I would use the tabctl change event
to load each recordsource as the tab is selected. In this example, I have
stored the recordsource for tab1 (tabs are zero-based) as "qryInfotest":

Private Sub TabCtl0_Change()

Select Case TabCtl0.Value
Case 0
Case 1
Me!subfrmDetails.Form.RecordSource = "qryInfoTest"
Case 2
your next subform...
Case 3
etc......
End Select

End Sub

When the form is first loaded, it should open pretty quickly, as it has no
recordsources for your 11 subforms. Then as a tab is selected, the
recordsource for that tab only is loaded.

The example from the website is also a viable alternative. Mine is simple,
but each time the tab is selected, it goes thru the same routine, which
seems a bit redundant...

Damon

jenniferspnc said:
I've done lots of reading and made changes (on the ones I understood) but
my
form takes over a minute to open. I split the database, turned off track
name autocorrect info, and changed subdatasheet to [none].

I am confused on what to do now. I have a form with 10 tabs and 11
subforms. I read this following statement, but it didn't make sense to
me,
as I'm not sure how to accomplish.

"a better solution, faster because of less overhead when loading, would be
to have *one* subform, set *under* the tab control so that it "shows
through"
on each tab page. then change the subform's SourceObject property to the
appropriate table as you move from one tab to another. same effect from
the
user's point of view, but will load faster than multiple subforms"



And I read the below but I'm completely confused on how to fill in.
http://www.granite.ab.ca/access/performanceforms.htm:
Delete the SQL from the RecordSource and RowSource properties of the form,
subforms, comboboxes and listboxes. Now in the Form_Load event load the
appropriate SQL as follows ...

Private Sub Form_Load()
Me.RecordSource = "qryLargeTable"
Me.txtSomeField.RowSource = _
"SELECT SomeField " & _
"FROM qryLargeTable " & _
"GROUP BY SomeField " & _
"ORDER BY SomeField;"
End Sub

The only part I get is that I open each subform and I'm supposed to remove
the "record source" and put code into the "Load Event" correct?

For example, my subform is based off the table "services". I have the
following fields in this subform: partner_id, yes, no, limited. Can
someone
provide guidance so I can apply it to the rest of my forms/subforms?

Please let me know if additional detail is needed.

Thanks in advance!
Click to expand...
Click to expand...
 
Its probably the subform name - substitute your subform name for
"subfrmDetails".
Make sure it is the subform name that is on the main form, sometimes Access
names the subforms different from what you would expect.

Damon


jenniferspnc said:
My apologies...I just saw your response and had posted a similar question
(which is your answer) this morning. So this appears to be what will
solve
my problem.

Question: The line below. What do I input into the line below? I keep
getting errors so I'm not sure if it's the page name, subform name, form
name, or what.

Me!subfrmDetails.Form.RecordSource

Everything else is easy enough to understand.

Thanks again for your help.

Damon Heron said:
I don't know your situation, but I would save each of the record sources
of
your many subforms as stored queries, and leave the recordsource blank
(with
the exception of the first tab). Then I would use the tabctl change
event
to load each recordsource as the tab is selected. In this example, I
have
stored the recordsource for tab1 (tabs are zero-based) as "qryInfotest":

Private Sub TabCtl0_Change()

Select Case TabCtl0.Value
Case 0
Case 1
Me!subfrmDetails.Form.RecordSource = "qryInfoTest"
Case 2
your next subform...
Case 3
etc......
End Select

End Sub

When the form is first loaded, it should open pretty quickly, as it has
no
recordsources for your 11 subforms. Then as a tab is selected, the
recordsource for that tab only is loaded.

The example from the website is also a viable alternative. Mine is
simple,
but each time the tab is selected, it goes thru the same routine, which
seems a bit redundant...

Damon

jenniferspnc said:
I've done lots of reading and made changes (on the ones I understood)
but
my
form takes over a minute to open. I split the database, turned off
track
name autocorrect info, and changed subdatasheet to [none].

I am confused on what to do now. I have a form with 10 tabs and 11
subforms. I read this following statement, but it didn't make sense to
me,
as I'm not sure how to accomplish.

"a better solution, faster because of less overhead when loading, would
be
to have *one* subform, set *under* the tab control so that it "shows
through"
on each tab page. then change the subform's SourceObject property to
the
appropriate table as you move from one tab to another. same effect from
the
user's point of view, but will load faster than multiple subforms"



And I read the below but I'm completely confused on how to fill in.
http://www.granite.ab.ca/access/performanceforms.htm:
Delete the SQL from the RecordSource and RowSource properties of the
form,
subforms, comboboxes and listboxes. Now in the Form_Load event load the
appropriate SQL as follows ...

Private Sub Form_Load()
Me.RecordSource = "qryLargeTable"
Me.txtSomeField.RowSource = _
"SELECT SomeField " & _
"FROM qryLargeTable " & _
"GROUP BY SomeField " & _
"ORDER BY SomeField;"
End Sub

The only part I get is that I open each subform and I'm supposed to
remove
the "record source" and put code into the "Load Event" correct?

For example, my subform is based off the table "services". I have the
following fields in this subform: partner_id, yes, no, limited. Can
someone
provide guidance so I can apply it to the rest of my forms/subforms?

Please let me know if additional detail is needed.

Thanks in advance!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top