Slow Loading Tab Control (1 Viewer)

psyc0tic1

Access Moron
Local time
Today, 02:49
Joined
Jul 10, 2017
Messages
360
Through the great help by many here the re-write of my database is complete and fully functional.

However I really need to re-address the seriously slow loading of my main form (frm_home) after changing to tab control from navigation control.

This was dealt with briefly in another thread but that thread covered several different subjects and got quite long. I ultimately decided on the solution by pbaldy in this post https://www.access-programmers.co.uk/forums/showpost.php?p=1590961&postcount=108

but the main form takes sometimes more than 60 seconds to load.

Database starts with the login form (frm_loginform) and logging in opens the main form (frm_home).

This is the code for frm_home that deals with the forms on the tabs loading:
Code:
Option Compare Database
Option Explicit

Public Sub Form_Load()

    Dim strMsg As String
    Const TabName = "TabCtl87"
    Dim pg As Access.Page
    Dim tb As Access.TabControl
    Set tb = Me.Controls(TabName)
    
Property Get CurrentPage() As Access.Page
    With Me.TabCtl87
        Set CurrentPage = .Pages(.Value)
    End With
End Property

Private Sub TabCtl87_Change()

    Select Case Me.CurrentPage.Name
        Case "NewRecordInputForm"
            Me.Patrick_Input_Form.SourceObject = "frm_engineerinput"
        Case "VisInputForm"
            Me.Visual_Inspection_Input_Form.SourceObject = "frm_visualinspectioninput"
        Case "LabInputForm"
            Me.Lab_Test_Input_Form.SourceObject = "frm_labtestinput"
        Case "NewPartForm"
            Me.New_Part_Input_Form.SourceObject = "frm_newpartinput"
        Case "NewUserForm"
            Me.New_User_Input_Form.SourceObject = "frm_newuserinput"
        Case "UserProfileForm"
            Me.userprofile.SourceObject = "frm_userprofile"
        Case "ReportCenterForm"
            Me.newreportcenter.SourceObject = "frm_newreportcenter"
        
    End Select

End Sub

I understand this should work but I was wondering if there was another reason.

Two of my forms (frm_labtestinput and frm_visualinspectioninput) have a combobox on them that checks the main table (tbl_auditdata) for the value in the field called "Status".

Let's just deal with one of the forms (frm_visualinspectioninput).

The record source for the form is this query (qry_visinspectinputform)
Code:
SELECT tbl_auditdata.Status, tbl_auditdata.AuditID, tbl_auditdata.Facility, tbl_auditdata.PONumber, tbl_auditdata.PartNumber, tbl_auditdata.TotalReceived, tbl_auditdata.VisInspectDate, tbl_auditdata.PartProdDate, tbl_auditdata.QCLine, tbl_auditdata.BlackGreenDot, tbl_auditdata.TotalVisInspected, tbl_auditdata.TotalVisBad, tbl_auditdata.TotalVisGood, tbl_auditdata.VisDefectFound, tbl_auditdata.VisAtt, tbl_auditdata.VisualInspectorUserID, tbl_auditdata.LabInspectorUserID, tbl_auditdata.VisUpdate, tbl_auditdata.RecDate, tbl_auditdata.MfgPONum
FROM tbl_auditdata
WHERE (((tbl_auditdata.Status)="Waiting on Visual Inspection"));

The query for the combobox is
Code:
SELECT tbl_auditdata.AuditID, tbl_auditdata.PONumber, tbl_auditdata.Status, [tbl_parts].[PartNumber] & " - " & [tbl_parts].[PartDesc] AS Expr1
FROM tbl_auditdata INNER JOIN tbl_parts ON tbl_auditdata.PartNumber = tbl_parts.ID
WHERE (((tbl_auditdata.Status)="Waiting on Visual Inspection"))
ORDER BY tbl_auditdata.PONumber;
All looking for the status of "Waiting on Visual Inspection".

in the code for the form (frm_visualinspectioninput) are these blocks of code:
Code:
Private Sub cboGoToRecord_AfterUpdate()
     On Error Resume Next
     Dim rst As Object
     Set rst = Me.RecordsetClone
     rst.FindFirst "AuditID = " & Me.cboGoToRecord.Value
     Me.Bookmark = rst.Bookmark
End Sub

Private Sub Form_Current()
    Me.cboGoToRecord.Value = Me.AuditID.Value
End Sub

All of this populates the combobox and some of the form fields when the form loads with the first record specified by the query and changes as you change selections in the combobox.

Granted there are two forms that use the same method all be it looking at two different statuses... but just trying to deal with one form and any solution can be ported over to the other one.

Could all of this be contributing tot he slow loading of the main form? Would it help things to have the combobox be blank in the beginning and no forms fields auto filled until a choice was made from the combobox?
 

Minty

AWF VIP
Local time
Today, 07:49
Joined
Jul 26, 2013
Messages
10,355
It's difficult to tell form the code you have posted, but I'm assuming that all those sub form containers are empty on initial form load ?

In which case the combo boxes you mention shouldn't be there to be loaded?
 

psyc0tic1

Access Moron
Local time
Today, 02:49
Joined
Jul 10, 2017
Messages
360
It's difficult to tell form the code you have posted, but I'm assuming that all those sub form containers are empty on initial form load ?

Correct. Any Ideas why it loads so slow then? What more info would be needed to make an educated guess?
 

JHB

Have been here a while
Local time
Today, 08:49
Joined
Jun 17, 2012
Messages
7,732
How fast does the main form load if you not load anything into the tap control?
You can test it by renaming the form's load event.
Code:
Public Sub Form_Load[B][COLOR=Red]1[/COLOR][/B]()
Are you sure that all sub form containers are empty, did you actually open the main form in design view and look for it, newly?
 

Minty

AWF VIP
Local time
Today, 07:49
Joined
Jul 26, 2013
Messages
10,355
What's the rowsource for the main form?
 

psyc0tic1

Access Moron
Local time
Today, 02:49
Joined
Jul 10, 2017
Messages
360
I'm assuming that all those sub form containers are empty on initial form load ?

So it turns out that not all containers were empty. The reason why...

Due to the code I posted to delay loading the forms and the not posted code that was determining what user groups saw what tabs... I had a couple of forms in their containers. The reason why...

The code to delay loading the forms until you clicked on a tab was leaving all pages blank even the one that had focus on load... so I had the first couple of forms in their containers so the users would see a page when the main form loaded.

Since yesterday I changed all that to have only one small form in it's container and have that one be the focused form when the main form loads for everyone.

This didn't seem to speed anything up at all so I started looking into the two forms that had the comboboxes I mentioned in post #1 and found fields in the queries that didn't need to be there and worked on removing unnecessary fields from the forms too. Strangely this didn't seem to speed up anything either.

Next I did a modification where I created a small table with a couple of random fields and a couple of bogus records and a small form related to it that opens hidden through an AutoExec to create a persistent back end connection. This seemed to speed things up a bit but not a dramatic change.

While creating the new table in the back end I noticed I was having a speed issue even working with the back end and came to the uneducated conclusion that this was all network speed related since the back end is in a directory on a server many states away from me and most of the users except for the users in that facility where the server resides. This coupled with the fact that there were several users updating records during the time I was trying to figure this out and one user that never closed their front end and went home.

This morning the database opens very quickly as there is nobody in there but me right now and our network is not being used by too many at the moment (it is early hardly anybody at work).

I am going to continue to monitor this today to see if I am correct. I am hoping we were just having a bad network day.
 

Minty

AWF VIP
Local time
Today, 07:49
Joined
Jul 26, 2013
Messages
10,355
If this is being used over a WAN then you will have speed issues, unless you have a ninja fast, super stable connectivity backbone, Access / SQL doesn't play very well over a WAN full stop.

Have a search on here for a few posts by jleach and his links to tips on how to improve WAN based Access performance. You can with careful design make things better.
 

psyc0tic1

Access Moron
Local time
Today, 02:49
Joined
Jul 10, 2017
Messages
360
I do however revert back to my original question due to noticing that right now the main form loads fast but the forms on the tabs that have the comboboxes are slow to load once the tab is clicked.

Is there some way that those queries are causing the slow loading with the inner joins and whatnot?
 

psyc0tic1

Access Moron
Local time
Today, 02:49
Joined
Jul 10, 2017
Messages
360
If this is being used over a WAN then you will have speed issues, unless you have a ninja fast, super stable connectivity backbone, Access / SQL doesn't play very well over a WAN full stop.

Have a search on here for a few posts by jleach and his links to tips on how to improve WAN based Access performance. You can with careful design make things better.

I tried searching for posts by him regarding WAN but no matches.
 

Users who are viewing this thread

Top Bottom