Solved

Access07 - TabControl OnChange set tab page subforms to ""

Posted on 2014-01-19
5
659 Views
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 = ""
        '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
Comment
Question by:wlwebb
  • 2
  • 2
5 Comments
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 250 total points
Comment Utility
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
 

Author Comment

by:wlwebb
Comment Utility
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
oK, ...just my thoughts...
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
Comment Utility
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
 

Author Closing Comment

by:wlwebb
Comment Utility
Thanks for the input... I have rethought some of my tables based on your all's input
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

771 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

10 Experts available now in Live!

Get 1:1 Help Now