Speed up Form with 4 Subforms

[wrldruler]

Hello,

I've got a time card database. Our office uses 4 types of time
(Project, Admin, etc). I want to have a form that shows 4 sections --
one section for each type of time.

I have successfully built the query that contains summary data for all
time. No problem there.

Currently, I have 4 sub-forms on the main form -- one subform for each
type. On Form_Load, I bind the 4 subforms to the one query, with the
"Forecast_Work_Type_Code" field deciding which data goes in which
subform:

Me!subfrm_Forecast_Summary_User_NonCSD.Form.RecordSource =
"SELECT qry_Forecast_Summary_Indiv_f_Calc_All.* FROM
qry_Forecast_Summary_Indiv_f_Calc_All WHERE
(((qry_Forecast_Summary_Indiv_f_Calc_All.Forecast_Work_Type_Code)=1));"

Me!subfrm_Forecast_Summary_User_NonCSD.Form.RecordSource =
"SELECT qry_Forecast_Summary_Indiv_f_Calc_All.* FROM
qry_Forecast_Summary_Indiv_f_Calc_All WHERE
(((qry_Forecast_Summary_Indiv_f_Calc_All.Forecast_Work_Type_Code)=2));"

Me!subfrm_Forecast_Summary_User_RTE.Form.RecordSource =
"SELECT qry_Forecast_Summary_Indiv_f_Calc_All.* FROM
qry_Forecast_Summary_Indiv_f_Calc_All WHERE
(((qry_Forecast_Summary_Indiv_f_Calc_All.Forecast_Work_Type_Code)=3));"

Me!subfrm_Forecast_Summary_User_Admin.Form.RecordSource =
"SELECT qry_Forecast_Summary_Indiv_f_Calc_All.* FROM
qry_Forecast_Summary_Indiv_f_Calc_All WHERE
(((qry_Forecast_Summary_Indiv_f_Calc_All.Forecast_Work_Type_Code)=4));"

So 4 identical subforms, pointing to the same query, with just
Forecast_Work_Type_Code being different.

Here is my problem. The query takes 5 seconds to load, which is OK
with me. But every time I bind the query to the subform it takes 5
seconds per subform. 5 seconds x 4 subforms = 20 seconds to load.

Can you recommend a faster solution that will load the query one time
and then I can filter by Forecast_Work_Type_Code to produce my 4
different sections?

Thanks,

Chris

 

[Jeff Boyce]

"Types of time" implies categorization. If all the records are in the same
table (as your SQL statements seem to suggest), have you looked at using a
Totals query, grouping by that categorization?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[wrldruler]

"Types of time" implies categorization. If all the records are in the same
table (as your SQL statements seem to suggest), have you looked at using a
Totals query, grouping by that categorization?

I think I already have a totals query built. It's already grouped. I
know I could easily build a static report that does what I want, but
we all know that Forms don't have the same Grouping abilities.

++Here is what my query results looks like:
Admin Time | Task abc | x hours |
Admin Time | Task xyc | x hours |
Project Time | Project 123 | x hours |
Project Time | Project 456 | x hours |

++Here is what I want the form to look like:

**Admin Time
Task abc x hours
Task xyz x hours

**Project Time
Project 123 x hours
Project 456 x hours

I made each section be a sub-form and then changed the Recordsource
using the SQL statements. It's just slow to bind.

I am thinking about experimenting with creating 4 Listboxes, instead
of 4 sub-forms. Curious if the SQL binds faster.

Also thinking about dumping the Query to a temp table, and then
binding the sub-forms or Listboxes to the Temp table. If the query
doesn't have to run 4 times, then it may be faster.

Thanks

 

[Jeff Boyce]

Sounds like you're comfortable with experiementing ... go for it! I don't
know if you'll find anyone here who can authoritatively state "X is faster
than Y" for what YOU are doing, unless they also qualify it with "but it
depends..." <g>

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP