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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 39

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

650 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