Access07 - TabControl OnChange set tab page subforms to ""

Posted on 2014-01-19
Medium Priority
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 1000 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 41

Assisted Solution

PatHartman earned 1000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
The best software application must always have an error handling tool
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

589 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