unload subforms within a tab of a mainform (2 Viewers)

[Ummyeah]

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.

You comment about having too many indexes means I need more tables is just wrong. I know all of these things and you people just literally don't believe that someone could need more indexes than Access has available and that is ridiculous. This is why I don't try to ask for help or help others on forums like this. You do realize that more tables means more joins which means more foreign keys which means more indexes right? Which is why I have so many. A properly normalized database by definition has more indexes. The database is for devices that we certify and they each have many, many attributes and measurements and many of them have repeating options so it gets it's own table and some only apply to about 1% of the devices and some share attributes with other types of devices so those tables get more indexes. We track about 30 options for each device, not to mention, the approximately 30 measurements that are taken that are unique numbers, also tracking the status of the certification effort, the points of contact and their organization and their function, which devices are built into other devices, we have parent devices as well so a certification can piggyback onto another certification which means self joins. NONE of which I should have to justify to you people in a COMPLETELY unrelated thread. Not one of you has actually commented on my code, which is much better than your EXPERT advice btw, but have latched onto that one offhand comment about upgrading to SQL so that you can show off all your knowledge about how great Access is despite the fact that has nothing to do with this topic. go high five each other about how awesome you are at making tiny databases why don't you.

 

[Ummyeah]

You comment about having too many indexes means I need more tables is just wrong. I know all of these things and you people just literally don't believe that someone could need more indexes than Access has available and that is ridiculous. This is why I don't try to ask for help or help others on forums like this. You do realize that more tables means more joins which means more foreign keys which means more indexes right? Which is why I have so many. A properly normalized database by definition has more indexes. The database is for devices that we certify and they each have many, many attributes and measurements and many of them have repeating options so it gets it's own table and some only apply to about 1% of the devices and some share attributes with other types of devices so those tables get more indexes. We track about 30 options for each device, not to mention, the approximately 30 measurements that are taken that are unique numbers, also tracking the status of the certification effort, the points of contact and their organization and their function, which devices are built into other devices, we have parent devices as well so a certification can piggyback onto another certification which means self joins. NONE of which I should have to justify to you people in a COMPLETELY unrelated thread. Not one of you has actually commented on my code, which is much better than your EXPERT advice btw, but have latched onto that one offhand comment about upgrading to SQL so that you can show off all your knowledge about how great Access is despite the fact that has nothing to do with this topic. go high five each other about how awesome you are at making tiny databases why don't you.

And another thing! Don't come back here like, why are you being so mean? I was just trying to help! No you weren't. You were trying to be right and superior. Next time try something like

hey that code is a huge improvement on that previous suggestion. Thanks so much for contributing to the community. I disagree that you need to upgrade to SQL. If you have questions or want to discuss it, check out this forum <link here> or ask me some questions and I will be happy to help.

 

[Pat Hartman]

I'll make a note of your opinions.

 

[Ummyeah]

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.

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:

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

So I am having a problem with this that came up later because my database is still in design mode and I am making frequent changes. Now I can easily comment this code out and wait until I put out the operational executable only copy. However, it slows down switching from one form to another without it and both options cause pain for me when I am doing maintenance or making changes and I would rather ensure that it stops happening and to prevent it happening to some future person that has to take care of this database when I get a better job. (someday..)

The problem is when I save the form when the first tab is unloaded, it does not reload the first page when I open the form. So I end up with unloaded subforms which will only load if I switch to another tab and back which is not ideal obviously. I wrote some code that was very similar to what I originally wrote and called it DynamicOpen that is supposed to, on form open, check the first tab for unloaded subforms and load them. However I keep getting the error "Object Variable with block variable not set", but I don't understand what variable I didn't set. Any thoughts on how to improve this function? The other option is to use the exception in the original code and always have these loaded, but I don't like that either.

Public Sub DynamicOpen(tabCtrl As TabControl)

On Error GoTo DynamicOpen_Err

    Dim pg As page
    Dim ctrl As Control


    pg = tabCtrl.Pages(0)

    For Each ctrl In pg
    
        Select Case ctrl.ControlType

        Case acSubform

            If ctrl.SourceObject = "" Then
                ctrl.SourceObject = ctrl.Name
            End If

        End Select

    Next ctrl

DynamicOpen_Exit:
    Exit Sub

DynamicOpen_Err:
    MsgBox Error$
    Resume DynamicOpen_Exit

End Sub

called thusly,

Private Sub Form_Open(Cancel As Integer)

On Error GoTo Form_Open_Err

    DynamicOpen Me.tabSoftwareVersion

Form_Open_Exit:
    Exit Sub

Form_Open_Err:
    MsgBox Error$
    Resume Form_Open_Exit

End Sub

 

[Pat Hartman]

I'm moving this thread. It is buried in a tutorials forum and this is certainly not a tutorial

In the future, please always start a NEW thread for a new question. No one is going to bother to read a very long thread to see if someone has already offered their suggestion. Now this thread is up to three questions.

Good luck in your new location.