unload subforms within a tab of a mainform (1 Viewer)

shatteras

Registered User.
Local time
Today, 09:43
Joined
Apr 11, 2016
Messages
25
I am using a code I found on the net to log use of forms. (who opens it and when), the problem I am facing is, the code works if I put it in the "onload" event of a form. BUT, the forms are subforms within different tabs of the main form, so when I load the main form, the subforms in the tabs are also loaded technically. I am beginning to confuse myself here LOL!
I need help..
I need to log who's opening the tabs/subforms within the tabs. to make it shorter, I want to have a log who is looking at that particular tabs/subform.

thank you very much in advance.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:43
Joined
May 7, 2009
Messages
19,169
then put also your code the the subforms' load event.
 

shatteras

Registered User.
Local time
Today, 09:43
Joined
Apr 11, 2016
Messages
25
thank you for your response sir. but unfortunately, as I have mentioned earlier, when I put it "onload" event of the form (which is a subform within a tab control embedded to a main form), it will log the opening of the main form even if I am not selecting that particular tab. I am currently trying to unload the subforms within that tabs so when I click on that tab, only then will it fire an on load event. then unloads when I click another. I am trying many things already... whew...
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:43
Joined
May 7, 2009
Messages
19,169
ok, then, make the sourceobject of the subform to nothing first.
on the click of the tab, reinstate its sourceobject.
when one click another tab, again set the sourceobject to nothing, etc....
 

shatteras

Registered User.
Local time
Today, 09:43
Joined
Apr 11, 2016
Messages
25
thank you,. I will try and be right back... I hope this works...
 

shatteras

Registered User.
Local time
Today, 09:43
Joined
Apr 11, 2016
Messages
25
the "unloading" plan didn't work :( could you please help me in how exactly should I remove the source object while it is not yet clicked or the tab is not active? thanks a bunch

thank you too uncle gizmo, I'm also reading that thread.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:43
Joined
May 7, 2009
Messages
19,169
here take a look at this sample. observe the Load event of the main form. check also the click event of the tab control.
 

Attachments

  • tabs.accdb
    476 KB · Views: 632

shatteras

Registered User.
Local time
Today, 09:43
Joined
Apr 11, 2016
Messages
25
can you please take a look at this code.... :( the first tab, is working. it won't load unless I click on it. but the rest remains blank even if I click on it angrily :(
also because of this, the log for users who is clicking on the first tab is also working. now all I have to do is make the other tabs work like the first tab. THANK YOU.


Private Sub tab_ops_Change()
If Me.tab_ops.Value = 0 Then
If Me!OPERATIONSops.SourceObject = "" Then
Me!OPERATIONSops.SourceObject = "OPERATIONSops"
If Me.tab_ops.Value = 1 Then
If Me!OPERATIONSgascom.SourceObject = "" Then
Me!OPERATIONSgascom.SourceObject = "OPERATIONSgascom"
If Me.tab_ops.Value = 2 Then
If Me!OPERATIONSdiesel.SourceObject = "" Then
Me!OPERATIONSdiesel.SourceObject = "OPERATIONSdiesel"
If Me.tab_ops.Value = 3 Then
If Me!OPERATIONScrude.SourceObject = "" Then
Me!OPERATIONScrude.SourceObject = "OPERATIONSutilities"
If Me.tab_ops.Value = 3 Then
If Me!OPERATIONSutilities.SourceObject = "" Then
Me!OPERATIONSutilities.SourceObject = "OPERATIONSutilities"
If Me.tab_ops.Value = 4 Then
If Me!OPERATIONSoil.SourceObject = "" Then
Me!OPERATIONSoil.SourceObject = "OPERATIONSoil"

End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If

End Sub
 

shatteras

Registered User.
Local time
Today, 09:43
Joined
Apr 11, 2016
Messages
25
SIR THANK YOU FOR THE REFERENCE!!!! it's now working like a charm, plus my code for log can be triggered with the on load of the forms. thank you again for the help sir!!!! here's how I wrote the code based on your sample

Private Sub tab_ops_Click()
If Me.tab_ops = 0 Then 'first tab clicked
Me.OPERATIONSops.SourceObject = "OPERATIONSops"
Me.OPERATIONSgascom.SourceObject = ""
Me.OPERATIONSdiesel.SourceObject = ""
Me.OPERATIONScrude.SourceObject = ""
Me.OPERATIONSutilities.SourceObject = ""
Me.OPERATIONSoil.SourceObject = ""
Else
If Me.tab_ops = 1 Then 'second tab clicked
Me.OPERATIONSops.SourceObject = ""
Me.OPERATIONSgascom.SourceObject = "OPERATIONSgascom"
Me.OPERATIONSdiesel.SourceObject = ""
Me.OPERATIONScrude.SourceObject = ""
Me.OPERATIONSutilities.SourceObject = ""
Me.OPERATIONSoil.SourceObject = ""
Else
If Me.tab_ops = 2 Then 'third tab clicked
Me.OPERATIONSops.SourceObject = ""
Me.OPERATIONSgascom.SourceObject = ""
Me.OPERATIONSdiesel.SourceObject = "OPERATIONSdiesel"
Me.OPERATIONScrude.SourceObject = ""
Me.OPERATIONSutilities.SourceObject = ""
Me.OPERATIONSoil.SourceObject = ""
Else
If Me.tab_ops = 3 Then 'fourth tab clicked
Me.OPERATIONSops.SourceObject = ""
Me.OPERATIONSgascom.SourceObject = ""
Me.OPERATIONSdiesel.SourceObject = ""
Me.OPERATIONScrude.SourceObject = "OPERATIONScrude"
Me.OPERATIONSutilities.SourceObject = ""
Me.OPERATIONSoil.SourceObject = ""
Else
If Me.tab_ops = 4 Then 'fifth tab clicked
Me.OPERATIONSops.SourceObject = ""
Me.OPERATIONSgascom.SourceObject = ""
Me.OPERATIONSdiesel.SourceObject = ""
Me.OPERATIONScrude.SourceObject = ""
Me.OPERATIONSutilities.SourceObject = "OPERATIONSutilities"
Me.OPERATIONSoil.SourceObject = ""
Else
If Me.tab_ops = 5 Then 'fifth tab clicked
Me.OPERATIONSops.SourceObject = ""
Me.OPERATIONSgascom.SourceObject = ""
Me.OPERATIONSdiesel.SourceObject = ""
Me.OPERATIONScrude.SourceObject = ""
Me.OPERATIONSutilities.SourceObject = ""
Me.OPERATIONSoil.SourceObject = "OPERATIONSoil"
End If
End If
End If
End If
End If
End If
End Sub
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:43
Joined
May 7, 2009
Messages
19,169
to reduced the IFs and End IFs, use SELECT CASE.
 

shatteras

Registered User.
Local time
Today, 09:43
Joined
Apr 11, 2016
Messages
25
i'll do that on the next half of my db. this one is only for one department, the other department have more than 5 divisions so the select case will be very helpful. thank you for the help and tip. :)
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 16:43
Joined
Jul 9, 2003
Messages
16,244
I would suggest a possible modification; add this function:-

Code:
Private Function fSetTabCtrlSubFrmSourceToEmptyStr(strTabCtrlName As String) As String
Dim Ctrl As Control

    For Each Ctrl In Me.Controls        'All of the controls in the form
        Select Case Ctrl.ControlType    'What type of control is it?
            Case acSubform              'Is the control a sub-form window?
            
            'Check to see if we are within the correct Tab control
            '(the sub-form window is on a page, the page is on the Tab control. That's why you have to have parent twice)
            If Ctrl.Parent.Parent.Name = strTabCtrlName Then Ctrl.SourceObject = ""
            
        End Select
    Next Ctrl
End Function      'fSetTabCtrlSubFrmSourceToEmptyStr


and then call it like this:-

Code:
Private Sub tab_ops_Click()

Call fSetTabCtrlSubFrmSourceToEmptyStr("tab_ops")

If Me.tab_ops = 0 Then 'first tab clicked
    Me.OPERATIONSops.SourceObject = "OPERATIONSops"
Else
If Me.tab_ops = 1 Then 'second tab clicked
    Me.OPERATIONSgascom.SourceObject = "OPERATIONSgascom"
Else
If Me.tab_ops = 2 Then 'third tab clicked
    Me.OPERATIONSdiesel.SourceObject = "OPERATIONSdiesel"
Else
If Me.tab_ops = 3 Then 'fourth tab clicked
    Me.OPERATIONScrude.SourceObject = "OPERATIONScrude"
Else
If Me.tab_ops = 4 Then 'fifth tab clicked
    Me.OPERATIONSutilities.SourceObject = "OPERATIONSutilities"
Else
If Me.tab_ops = 5 Then 'fifth tab clicked
    Me.OPERATIONSoil.SourceObject = "OPERATIONSoil"
End If
End If
End If
End If
End If
End If
End Sub
 

shatteras

Registered User.
Local time
Today, 09:43
Joined
Apr 11, 2016
Messages
25
for the 2nd department, I used your suggested code uncle gizmo, thank you very much. thank you for your help guys. I'm learning a lot :)
 

Ummyeah

New member
Local time
Today, 10:43
Joined
Jun 16, 2022
Messages
15
I realize the last time a comment was made on this was 2016, but here goes. So I used a version of the code supplied by Gizmo and it worked great. The issue I have with it is that I have a very complex database utilizing multiple subforms on each tab and multiple forms that I would like to utilize this technique with. Yes I should upgrade to SQL Server, but I work for the government and we are trying to get it but it's like a whole thing so MEANWHILE we have Access.

I decided to write my own code that does the same thing, but does not require explicitly identifying the sub forms and the source object names, accommodates an undefined number of sub forms, and skips exceptions if needed.

Edit; I originally made this post to ask for help with this code, but then like 20 minutes later got it to work on my own so just decided to share it.

Code:
Option Compare Database



Public Sub DynamicLoad(tabCtrl As TabControl, ParamArray sfrmExceptions())

On Error GoTo DynamicLoad_Err

    Dim pg As page
    Dim ctrl As Control
    Dim pgActive As Integer
    Dim i As Integer
    Dim keep As Boolean

    pgActive = tabCtrl.Value

    For Each pg In tabCtrl.Pages
        If pg.pageIndex <> pgActive Then
            For Each ctrl In pg.Controls
                Select Case ctrl.ControlType

                Case acSubform
                    keep = False
                   
                    For i = LBound(sfrmExceptions) To UBound(sfrmExceptions)
                        If sfrmExceptions(i) = ctrl.Name Then
                            keep = True
                            Exit For
                        End If
                    Next
                   
                    If Not keep Then
                        ctrl.SourceObject = ""
                    End If
                End Select
               
            Next ctrl
           
        Else:
            For Each ctrl In pg.Controls
                Select Case ctrl.ControlType
                Case acSubform
                    ctrl.SourceObject = ctrl.Name
                End Select
            Next ctrl
        End If
    Next pg

DynamicLoad_Exit:
    Exit Sub

DynamicLoad_Err:
    MsgBox Error$
    Resume DynamicLoad_Exit
 
 
End Sub

call in your form like this:

Code:
Private Sub tabControlName_Change()

On Error GoTo tabControlName_Change_Err

    DynamicLoad Me.tabControlName
   
tabControlName_Change_Exit:
    Exit Sub

tabControlName_Change_Err:
    MsgBox Error$
    Resume tabControlName_Change_Exit
       
End Sub
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:43
Joined
Feb 19, 2002
Messages
42,971
Just FYI, Switching to SQL Server would have no effect on the interface unless the interface is complex because your database is not properly normalized. But, you could fix that with Access without converting the BE.
 

Ummyeah

New member
Local time
Today, 10:43
Joined
Jun 16, 2022
Messages
15
Just FYI, Switching to SQL Server would have no effect on the interface unless the interface is complex because your database is not properly normalized. But, you could fix that with Access without converting the BE.
Actually it would BECAUSE my database is properly normalized, but that is beyond the scope of this topic. I really dislike that people always assume that when a person has a database issue, they assume the person knows nothing about normalization. There are many other factors that can diminish performance, especially with a large, complex database that goes beyond what Access was designed for.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:43
Joined
Feb 19, 2002
Messages
42,971
My point was that upgrading to SQL Server would NOT change the complexity of your interface and I'm not sure why you think it would, especially since you're an expert. It wouldn't even necessarily give you a performance improvement unless you were approaching the max database size or concurrent user count of ACE OR your schema was poor and you would fix it as part of the move. AND if your Access application had not been designed to take advantage of an ODBC RDBMS from the beginning then, in all likely hood, the straight conversion would result in slower performance not faster. That one blows everyone's mind. Jet/ACE are limited in their usefulness but what they do, they do exceptionally well.

I suggest that you read my post again. I did not assume that the database was improperly designed. Your post made the typical non-expert assumption that Access = Jet/ACE therefore converting "Access" to SQL Server solves all problems. FYI, Access is a RAD (Rapid Application Development) tool. It is NOT a database engine. Access uses Jet/ACE to store its own objects so the two are totally linked. But Access as an FE can work with any ODBC compliant RDBMS and at that point is freed from all the constraints of Jet and ACE. The max number of concurrent users becomes the number of seat licenses your RDBMS supports. The max database size becomes the max your RDBMS supports. You still have the awkwardness of having to distribute the Access Runtime if users don't have MS Access installed and the impossibility of concurrent multi-user development though so Access is still not a general purpose language.
 

Ummyeah

New member
Local time
Today, 10:43
Joined
Jun 16, 2022
Messages
15
My point was that upgrading to SQL Server would NOT change the complexity of your interface and I'm not sure why you think it would, especially since you're an expert. It wouldn't even necessarily give you a performance improvement unless you were approaching the max database size or concurrent user count of ACE OR your schema was poor and you would fix it as part of the move. AND if your Access application had not been designed to take advantage of an ODBC RDBMS from the beginning then, in all likely hood, the straight conversion would result in slower performance not faster. That one blows everyone's mind. Jet/ACE are limited in their usefulness but what they do, they do exceptionally well.

I suggest that you read my post again. I did not assume that the database was improperly designed. Your post made the typical non-expert assumption that Access = Jet/ACE therefore converting "Access" to SQL Server solves all problems. FYI, Access is a RAD (Rapid Application Development) tool. It is NOT a database engine. Access uses Jet/ACE to store its own objects so the two are totally linked. But Access as an FE can work with any ODBC compliant RDBMS and at that point is freed from all the constraints of Jet and ACE. The max number of concurrent users becomes the number of seat licenses your RDBMS supports. The max database size becomes the max your RDBMS supports. You still have the awkwardness of having to distribute the Access Runtime if users don't have MS Access installed and the impossibility of concurrent multi-user development though so Access is still not a general purpose language.
I wouldn't call myself "an expert" but I do understand normalization. The issues I have, which would be alleviated by switching to SQL server, are things like table index limits, which at this time require me to horizontally split a table into multiple tables, which then require more subforms on the front end to keep the data together, which results in more objects counting toward the max object limit in Access and slower form load times. Additionally the infrastructure the database would be running on would change to one with less traffic if we switched. Yeah this is not really about SQL vs Access, but it is an important aspect for our topology. Database size is also a factor, the data set itself is very close to the maximum that Access can handle in addition to the complexity of the data. So yes I am aware that with smaller databases Access tends to run a bit faster, but this database has outgrown the capabilities of Access and could be fully optimized by upgrading to SQL Server.

It is funny most of the time when I see a post about Access on other platforms, StackOverflow to be specific, the first response is "upgrade to SQL" regardless of the issue which is basically the DBA version of "git gud". The comment about switching to SQL was to hopefully avoid that discussion which, alas, has failed. Other issues that would lead to higher performance would be that it would be on a dedicated server that only 10 people at most have access to rather than a share drive that is utilized by idk how many people. That would be a use boost in just network traffic that has absolutely nothing to do with SQL vs Access performance necessarily but would make a functional difference for us. I don't believe that SQL is a magical fix for any database issue, but at the same time there are the right tools for the right job.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:43
Joined
Feb 19, 2002
Messages
42,971
The people who tell you that SQL Server is the ONLY solution don't have a clue what they are talking about. Yes, Jet and ACE have limits and many applications eventually reach them. Technically 255 concurrent users but practically speaking, the limit is closer to 50 - 100 as long as the LAN is stable and you use optimistic locking. The database size is limited to 2Gig. You could get around that by having multiple BE's BUT to do that you have to give up RI because RI cannot be enforced except within a single physical BE. But most of the problems are caused by poor development technique. I develop all my applications with the idea that at some point they might outgrow ACE. As a result, when that time comes and it did recently for one of my apps, I can perform the conversion in a couple of hours at best or a whole day at worst depending on how many objects need to be tested. How is that possible? For starters, all the DAO is written using the arguments SQL Server needs so I don't have to modify any code. Plus the forms are built as described below.

I have a number of Access applications that work flawlessly and quickly with millions of records. Jet/ACE are extremely well optimized. The limit is 2G, not the number of rows. All the forms are bound to queries with selection criteria to minimize the number of rows that ever need to be returned. NO form is ever bound to a naked table or query with local filtering.

Jet/ACE are limited to 32 indexes per table. I've been working with Access since the early 90's and I don't think I've ever come even close to that except for a couple of Insurance applications. Insurance applications are the worst because they always have a gazillion "codes" that require lookups and therefore relationships.

I think if you have to split a table horizontally, you almost certainly have one or more repeating groups and those need to be turned into separate tables.

Sounds like you have too many indexes. That bloats the database and slows down processing due to having to manage the index updates. Keep in mind that Access automatically builds a hidden index for each FK so you don't need to or you'll end up with two indexes for the same field. If you never removed the defaults, Access automatically builds indexes for every field whose name ends in ID, CD and a couple of other strings. Clear that field and define your own indexes.
 

Users who are viewing this thread

Top Bottom