Solved

form with tab control takes a long time to open

Posted on 2012-12-27
9
399 Views
Last Modified: 2012-12-27
hey guys i've got a form with a tab control containing a few subforms. it takes very long to open because i think it's opening all the queries the first time it's open. i've attached the database here (though it's missing some of the queries), how do i ensure that Access doesn't make the user wait whilst they open forms that the user doesn't see because it's not in the visible tab right now?

i've already got code to sense for the active tab and refresh just that subform but upon opening the form for the first time, it seems that it's mandatory to open all the queries once - i couldn't intercept the events - or rather don't know how to ha.

thanks guys!!!
Query.mdb
0
Comment
Question by:developingprogrammer
9 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 450 total points
ID: 38723271
This might save time as the form loads... you can load the individual subforms dynamically on an as-needed basis.

Try this:

-  Set the Source Object property of the subform controls to blank/null
- Place code in the click event of your tabcontrol to load the subforms when the associated tabs are clicked:
Select Case Me!TabCtl28.Pages(Me!TabCtl28.Value).Name
    
    
    Case "MTK Summary"
        Me.subfrmMTKSummaryDatasheet.SourceObject = "subfrmMTKSummaryDatasheet"
     ' etc....

Open in new window


Note  -- this will only work for subforms that are not referred to in code on other tabs (because the subform is only present after the code runs)...
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 38723445
You could keep the subforms as 'regular forms' and add buttons to open the forms as needed.  (Why keep subforms on a 'parent' form if you aren't going to load them?)

Scott C
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 50 total points
ID: 38723589
No points please.

I do it the way mbizup describes above, although I generally use the Change event of the Tab control rather than the Click event
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:developingprogrammer
ID: 38723696
whao mbizup you're a genius!! didn't think of that haha! (psst i didn't even know there was such a thing as source object!! haha, man i really need to up my skills!!)

thanks for your post scott, it's more for a UI thing that the user sees it as a tab rather than individual forms - so more of a UI design choice = )

fyed, yup yup, i had my event in the existing database as a on tab change event = ) thanks for your advice!

mbizup i got inspiration from your decoupling of the objects to also decouple the recordsource setting the subform's recordsource to nothing by default. thanks!! = ))

    Case "MTK Summary"
        Forms!frmMTKManager!subfrmMTKSummaryDatasheet.SourceObject = "subfrmMTKSummaryDatasheet"
        If Me.txtManagersTeamCode = 2 Then
            Forms!frmMTKManager!subfrmMTKSummaryDatasheet.Form.RecordSource = "qryMTKSummary"
        ElseIf Me.txtManagersTeamCode = 3 Then
            Forms!frmMTKManager!subfrmMTKSummaryDatasheet.Form.RecordSource = "qryMTKSummaryServicing"
        End If

Open in new window

0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38723748
You don't need to decouple the Recordsource from the subform.

Assuming that your subforms are supposed to be linked to your main form, you may also have to set the values of the LinkMaster and LinkChild properties of the subform control.  I ususally use something like:
    Select Case Me.tab_ControlName.Pages(Me.tab_ControlName).Caption

        Case "Details"

            if me.sub_Details.SourceObject = "" then
                me.sub_Details.SourceObject = "frm_Details_Subform"
                me.sub_Details.LinkMasterFields = "[TransID]"
                me.sub_Details.LinkChildFields = "[TransID]"
            end if

Open in new window

The advantage of this method is that it will only reset the SourceObject if it has not already been established, and it ensures that the main form and subform are linked properly.
0
 

Author Comment

by:developingprogrammer
ID: 38724319
great thanks fyed, could you respond to this new question i created? thanks!

"optimising subforms in tabbed controls"
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38724432
You are correct that if your subform is not actually linked, then you can leave out the portion where you set the LinkMasterField and LinkChildField properties.  That just ensures that if they are supposed to be linked, they link properly.

The thing that takes the time when loading multiple subforms is running the query to populate your subform.  If the subform is based on a simple table, the subform will populate almost instantaneously.  But if you have a query, then it takes a bit longer.  If you have multiple subforms, on multiple tabs, each one has to process (sequentially).  That is why I generally leave the SourceObject blank until the tab that it is on is first accessed.

Sometimes (if there are a lot of subforms or some of them take a while to populate) I'll even set the SourceObject of some of the subforms back to "" as I navigate between records on the main form.  Just depends on how many and how complex the queries are.
0
 

Author Comment

by:developingprogrammer
ID: 38724662
whao cool! ok great, learnt something new here = ) i didn't think of setting the subforms' sourceobject back to "" to save the querying time.

thanks fyed!!
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38724693
Always glad to help.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question