RE: [MS_AccessPros] Re: subform design time issue

 

Liz

When I have a tab control with subforms I dynamically load the Source Object (subform) using the tab control's Change event. A Select Case is good for that.

Example:

Private Sub TabCtl225_Change()

    Dim strSQL As String

    If gbolErrorTrapOff = False Then On Error GoTo err_PROC

    'Save record before any child records are entered.

    If Me.Dirty Then Me.Dirty = False

    If Me.frmProjResources_sub.Form.Dirty Then Me.frmProjResources_sub.Form.Dirty = False

    Select Case Me.TabCtl225

        Case 1

            '            Me.frmTotalHrs_ProjAct_ByRole_sub.SourceObject = "frmTotalHrs_ProjAct_ByRole_sub"

            '            Me.frmTotalHrs_ProjAct_ByRole_sub.LinkChildFields = "ProjectNum"

            '            Me.frmTotalHrs_ProjAct_ByRole_sub.LinkMasterFields = "ProjectNum"

            '            Me.frmTotalHrs_ProjAct_ByRole_sub.Requery

        Case 2

            Me.frmProjectedHours_sub.Requery

        Case 4

            '            Me.frmRFS_DocStatus_sub.SourceObject = "frmRFS_DocStatus_sub"

            '            Me.frmRFS_DocStatus_sub.LinkChildFields = "ProjectNum"

            '            Me.frmRFS_DocStatus_sub.LinkMasterFields = "ProjectNum"

            '            Me.frmRFS_DocStatus_sub.Requery

        Case 5, 6

            strSQL = "SELECT qryEmpInfo.FullName " _

                     & "FROM tblResources INNER JOIN qryEmpInfo ON tblResources.EmpID = qryEmpInfo.EmpID " _

                     & "WHERE ProjectNum = " & Me.ProjectNum _

                     & " AND tblResources.ActiveInProj <> 0 " _

                     & "ORDER BY FullName"

            Me.frmActivities_sub.Controls("cboResourceID").RowSource = strSQL

            Me.frmActivities_sub.Controls("cboResourceID").Requery

            Me.frmCriticalIssues_sub.Controls("cboOwner").RowSource = strSQL

            Me.frmCriticalIssues_sub.Controls("cboOwner").Requery

    End Select

exit_PROC:

    On Error Resume Next

    Exit Sub

err_PROC:

    Dim strErrMsg As String

    Dim lngIconBtn As Long

    Select Case Err.Number

        Case 2046

            'Ignore "can't move to rec" error or "Save not available now".

        Case Else

            gProcName = "TabCtl225_Change"

            glngErrNum = Err.Number

            gstrErrDescr = Err.Description

            glngLineNum = Erl

            Call ErrorLog("Form_frmMasterView")

    End Select

    If strErrMsg <> "" Then MsgBox strErrMsg, lngIconBtn, _

       GetSummaryInfo("Title", False)

    Resume exit_PROC

End Sub

---In MS_Access_Professionals@yahoogroups.com, <liz_ravenwood@beaerospace.com> wrote :

Hello Bill, I decompile quite regularly, and it doesn't seem to make any difference.  The record sources can be simple tables too. 

Thanks though. 

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Thursday, June 23, 2016 8:41 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: subform design time issue

 

Liz

 

I've seen this slowdown when working with complex queries as RecordSources. Just to test, remove the RecordSources from the subs and move through the forms. If the slowness is gone then the problem lies in the fact that Access actually re-queries the subs with every focus change. Annoying but that's life.

 

One thing you might try is to decompile the database. Instructions here at FMS. Then compact. Then open your recordsource queries and make a little change. Save them. Undo your changes and save again. This forces Access to re-compile the queries.

 

I don't know if any of that will help you but I've used that technique in the past and it helped quite a bit.

 

Regards,

Bill Mosca, Founder - MS_Access_Professionals

http://www.thatlldoit.com

Microsoft Office Access MVP

https://mvp.microsoft.com/en-us/PublicProfile/35852?fullName=Bill%20%20Mosca

My nothing-to-do-with-Access blog

http://wrmosca.wordpress.com

 

---In MS_Access_Professionals@yahoogroups.com, <liz_ravenwood@beaerospace.com> wrote :

Does anyone including Microsoft understand how painstaking slow it is to move from subform to subform in design mode?  Is it just me?  Is it because I'm running it within Citrix?

I've worked these databases for 10 years and it was slow with 2002, slower with 2007, and even slower with 2010.  It takes sometimes as long as 5-10 minutes for the control to get the focus and display the properties with which to work.

 

Respectfully,

Liz Ravenwood

Programmer / Analyst

B/E Aerospace | Super First Class Environments

 

<clip>

__._,_.___

---

Posted by: wrmosca@comcast.net

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (10)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• New Members 2

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___