Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 694
  • Last Modified:

Access07 - TabControl OnChange set tab page subforms to ""

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 = ""
        'sfrmtbl_LandTractPriorRecord
        
    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......


ANYWAY.....

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........
0
wlwebb
Asked:
wlwebb
  • 2
  • 2
2 Solutions
 
Jeffrey CoachmanCommented:
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?

JeffCoachman
0
 
wlwebbAuthor Commented:
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......
0
 
Jeffrey CoachmanCommented:
oK, ...just my thoughts...
0
 
PatHartmanCommented:
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.
0
 
wlwebbAuthor Commented:
Thanks for the input... I have rethought some of my tables based on your all's input
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now