Avatar of developingprogrammer
developingprogrammer
 asked on

form with tab control takes a long time to open

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
Microsoft Access

Avatar of undefined
Last Comment
Dale Fye

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
mbizup

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
clarkscott

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
SOLUTION
Dale Fye

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
developingprogrammer

ASKER
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

Dale Fye

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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
developingprogrammer

ASKER
great thanks fyed, could you respond to this new question i created? thanks!

"optimising subforms in tabbed controls"
Dale Fye

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.
developingprogrammer

ASKER
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!!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Dale Fye

Always glad to help.