Access07 - TabControl OnChange set tab page subforms to ""

Posted on 2014-01-19
Last Modified: 2014-01-26
This is a follow up to a question that i had re: hitting "can not open any more databases" error once I split my db to Front/Back end.

Tab Control OnChange Prior Question

My prior question was answered partly by taking the multiple tab page subforms and making them unbound and with the OnChange event of the TabCtl using Cases to have the tabbed form's page Source Object update when I select that tab page.

My question is after entering the 1st complete record, which thus made me have to go to all the tabs, it saved the first record ok.

HOWEVER, when I went to enter a new record, I hit the "can not open any more databases" error again.....

I "THINK" it's because once I've opened a tab page and then gone to another tab page it is leaving the one I'm leaving "open".... thus all of them were considered "open" when I clicked my control button to create a new record. (not sure if "Open" is the right terminology)

My complete code for the tab Control OnChange event is:
Private Sub TabCtlRecords_Change()
    Static intPrevTab As Integer

    'This section would clear the subform controls SourceObject property
    'for those subforms you don't use as frequently.
    Select Case intPrevTab
        Case 0 ' Page 1
            ' Don't do anything to hide this tab page
        Case 1 ' Page 2
            Me.sfrmtbl_LandTracts.SourceObject = ""
        Case 2 ' Page 3
'        Case 3 ' Page 4
            Me.sfrmtbl_LandTractCalls.SourceObject = ""
'        Case 4 ' Page 5
            Me.sfrmtbl_LandTractLandmarksCallDescriptions.SourceObject = ""
            Me.sfrmtbl_LandTractLandmarksCall_AllInfo.SourceObject = ""
        Case 5 ' Page 6
            Me.sfrmtbl_Records_SelectPrior.SourceObject = ""
        Case 6 ' Page 7
            Me.sfrmTmptbl_Records_PriorRecSetup.SourceObject = ""
        Case 7 ' Page 8
            Me.sfrmtbl_LandTractPriorRecord.SourceObject = ""
    End Select
    Select Case TabCtlRecords.Value
        Case 0 ' Page 1
            ' Don't do anything to hide this tab page
        Case 1 ' Page 2
            If Me.sfrmtbl_LandTracts.SourceObject = "" Then
                Me.sfrmtbl_LandTracts.SourceObject = "sfrmtbl_LandTracts"
            End If
'        Case 2 ' Page 3

        Case 3 ' Page 4
            If Me.sfrmtbl_LandTractCalls.SourceObject = "" Then
                Me.sfrmtbl_LandTractCalls.SourceObject = "sfrmtbl_LandTractCalls"
            End If
        Case 4 ' Page 5
            If Me.sfrmtbl_LandTractLandmarksCallDescriptions.SourceObject = "" Then
                Me.sfrmtbl_LandTractLandmarksCallDescriptions.SourceObject = "sfrmtbl_LandTractLandmarksCallDescriptions"
            End If
            If Me.sfrmtbl_LandTractLandmarksCall_AllInfo.SourceObject = "" Then
                Me.sfrmtbl_LandTractLandmarksCall_AllInfo.SourceObject = "sfrmtbl_LandTractLandmarksCall_AllInfo"
            End If

        Case 5 ' Page 6
            If Me.sfrmtbl_Records_SelectPrior.SourceObject = "" Then
                Me.sfrmtbl_Records_SelectPrior.SourceObject = "sfrmtbl_Records_SelectPrior"
            End If

        Case 6 ' Page 7
            If Me.sfrmTmptbl_Records_PriorRecSetup.SourceObject = "" Then
                Me.sfrmTmptbl_Records_PriorRecSetup.SourceObject = "sfrmTmptbl_Records_PriorRecSetup"
            End If
        Case 7 ' Page 8
            If Me.sfrmtbl_LandTractPriorRecord.SourceObject = "" Then
                Me.sfrmtbl_LandTractPriorRecord.SourceObject = "sfrmtbl_LandTractPriorRecord"
            End If
    End Select

End Sub

Open in new window

I've "blown" this db up once because one of my Current.db "INSERT INTO...." I used the wrong field names and had to recreate a lot of it (not finished doing all that yet even)... And it was a great lesson in Backups.... hadn't done one for 2 wks when it blew up......


I'm thinking that when I go back to tab control page 0 that I have to close (or set to "") all those Source Objects....

Does that appear right???? I think I do that in the top half of the Onchange Select case but want to make sure........
Question by:wlwebb
  • 2
  • 2
LVL 74

Accepted Solution

Jeffrey Coachman earned 250 total points
ID: 39792732
Just a comment here.

Is this interface a deal breaker? (multiple subforms on tabs, ..etc)
I think some of the problem here is all the "SouceObject" calls.

Loading and clearing subform sources may take a lot of resources, ...thus contributing to the error.
If you research this error you will see that it has to do with the number of "Connections" you are making/using.

Doing this for a couple of subforms with simple sources is fine.
(I do it all the time)

But once you start getting half a dozen or so subforms, then you throw in things like:
Complex record sources, complex datatypes, calculated fields, recordsets, aggregates, graphic, VBA automation, timing issues linking with the Back end, multiple users, system restraints, ...etc.
...God only knows what might trigger the error...

If I need more than two subforms, I will typically just separate the other forms out by themselves.
I have never run into this error myself in over 10 years of doing this.
That is not to say that I consider myself a great designer, ...only that I try to keep my user interfaces "simple".
The fact that you are encountering this error twice now, ...seems to say that you are running up against some type of limit here...
...a limit that more "code tweaking" may not fix...

Perhaps there is a simple solution here, ...but there is a lot to be said for simplifying the design.

So to be fair, are you compacting the DB and compiling the code regularly?
Did you try creating a new db and importing the objects, ...what about a Decompile?


Author Comment

ID: 39792794
I have been compiling fairly often as well as closing the db with the "compact on close" selected.

I think through all of this I am re-thinking one of my tables....but haven't gone thru changing all of it yet......
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39792842
oK, ...just my thoughts...
LVL 34

Assisted Solution

PatHartman earned 250 total points
ID: 39792948
Even though it looks like controls sit on a particular tab, they actually all sit on the underlying form and the tabs are used to control visibility.  So, you can have a separate subform control for each tab page or you can have a single subform control that is placed on the main form so that it "bleeds" through onto every tab.  With option 1, you will have all the subforms active and open at the same time.  This can make the form sluggish.  With option 2, you can use the Change property of the tab control to change the Source object of the single subform control and I think that is what you are doing.  If that is the case, you don't need to do anything to get rid of the previous form.  As soon as you touch someplace outside of the subform, Access automatically saves the subform's current record so nothing bad would happen if you just substituted a new Source for the subform control.

If you really made the forms unbound, you are pretty much on your own.  Access won't do anything for you and you will have to somehow ensure that data gets saved appropriately.  In that case changing the SourceObject would loose the data in the form that was being replaced unless you had somehow forced it to be saved.

I don't use unbound forms.  Access is intended to work with bound objects and that is what sets it apart from all your other development environment options.  If you are not using bound forms, you are far better off to not use Access as your development platform.  You could still use Jet/ACE for your data but I wouldn't do that either.

Author Closing Comment

ID: 39809953
Thanks for the input... I have rethought some of my tables based on your all's input

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now