Steve,
Me too, but I wonder if speed sometimes trumps design efficiency.
I don't understand the on demand question, at least not in this context. Let me see if I can explain the main and subforms.
The main form represents a company/customer. A company may have subsidiaries, divisions, etc., that may also have sub companies. Subforms (actually 8):
- Parent. Shows the parent company of the current company, if there is one. Clicking goes to the page for that company.
- Children. Shows all direct children, if there are any. It also shows the sum of all revenue from each child, and any of that child's children, grandchildren. The children are ranked in descending order by revenue. clicking on a child goes the page for that company.
- SalesRep. Shows the Sales Rep and "Client Specialist" (handles issues with existing contracts) assigned to that company. I could roll this info into the underlying query for the main form.
- Totals by Product. Shows the total number contracts (financial services), grouped by product type, with the 12 mo revenue totals. This totals all directly to this company as well as all children, grandchildren, etc., under them.
- Company totals. This is the 1-line company totals. The reason why this is a separate form, rather than a calculated field in the footer of form#4, is that sometimes a contract# can be shared across multiple products. So, while we count each one separately under the prod type above, we only count the contract #s once for the total.
- Company alias table. List of other names the company can be known as.
- Product List. A list of all individual contracts and products for this company, including the contract#, name (ea one has to have a unique name in the system), 12mo revenue, product type, status, etc. Clicking opens up the detail for each contract.
- Subset Revenue. This totals revenue and volume by different groups of prod types. The form is hidden, but fields in this form are used to calculate a company score. I could move this to form #4 or 5, or use dlookups/dsums instead, but I don't think that would impact the speed.
I could use separate tabs, but that would require the user to take extra steps. All the info fits nicely on the main form, so I don't need that benefit from tabs. The only sub form they might have to scroll is #7.
What are your thoughts? Or, does this make it even more confusing?
---In MS_Access_Professionals@yahoogroups.com, <StephenMConklin@...> wrote :
Adam:
Where you said "create tables to store the sums." … I disagree with that, LOL - as a matter of conscience.
Can you load the sub forms with no data, and then only load their row sources on demand?
Steve
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Thursday, October 1, 2015 1:53 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Speeding up form with multiple subforms
Steve,
No tabs. They are all on the main form.
What do you mean by "don't store the calculations"?
Adam
---In MS_Access_Professionals@yahoogroups.com, <StephenMConklin@...> wrote :
The first thing to do is stop loading the source objects of the sub forms. Make their sources empty, and then only load them on demand, via the tab control's change event (I am assuming the tab control, and not just 7 sub forms directly on the main form.)
Something like:
Private Sub tabCtl_Change()
Select Case tabCtl.Value
Case 1
Me.objSubform.SourceObject = "sfrmCustomer"
Case 2
Me.objSubform.SourceObject = "sfrmInvoice"
etc ...
HTH
Steve Conklin
PS – don't store the calculations, no matter what.
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Thursday, October 1, 2015 1:31 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Speeding up form with multiple subforms
I have a form with 7 (count 'em) sub forms, that takes a long time to load, ~30 seconds. 3 of the sub forms are based on sum queries. What are some general suggestions for speeding it up?
What do you think of the idea of creating tables for some of all of the sum queries? I know this is not "efficient" database design. However, the underlying data is updated only monthly, and it will (I assume) make the form load quicker.
Adam
Posted by: runuphillracing@yahoo.com
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (6) |
Tidak ada komentar:
Posting Komentar