Access Tabs dynamically on Access Forms

"How can I accomplish to accelarate the performance" you will take a performance hit both ways.

If you load all your tabs with all your subforms then the hit will be when the form opens.
If you use a single subform control then the hit will be when the user moves from 1 tab to the next, or geos back to a tab
Finally you could have separate subform controls on each tab but not load them until the user selects that tab. This eliminates the need to load a tab you have already loaded.

Cheers, Andrew

Each tab is one subform that I used

fSubSNPDIRate
fSubDBMRAte
fSubSNRate
fSubSiteKeyPIDRate
fSubMasterRate
Each subform has a control source which is dependable from query.

How the code looks like?

Thanks

To get the maximum performance

The subform should exist but not be connected (Design and place the subform, create the parent child relationship then remove the source object from the properties page)
It should "Load" the subform when the tab is selected
it should not "reload" if you return to the tab after it has been loaded...  it should already be there with data waiting
(If you set the  .SourceObject a second time it will reload and requery...  adding additional overhead)


Then in the OnChange of the tab control you select case of the tabname

Replace <CtrlName> with your control name
   
  Select Case <CtrlName>.Pages.Item(<CtrlName>.Value).Name
        Case "pgClinicalStatus"
            '---------------------------
            'Clinical Status Review
            '---------------------------
            With Student_ClinicalStatusReview_sfrm
                If .SourceObject = "" Then
                    .SourceObject = "Student_ClinicalStatusReview_sfrm"
                End If
            End With
       
        Case "pgCoursesTaken"
            '---------------------------
            'Student Course Review
            '---------------------------
            With Student_Courses_sfrm
                If .SourceObject = "" Then
                    .SourceObject = "Student_Courses_sfrm"
                End If
            End With
           
Etc...

This is my design structure.

So, I am trying to follow your suggestion but i am not quite sure.

I have one subform on each tab control, on the first one I have two which it is another subform with chart view.
The tab control does not have on change event, so I am quite confuse,

thanks for any clarification.

The tabs do not have a change event but the tab control does.
Select the grey area to the right of the last tab and you have selected the whole tab control

Now...
For which ever tab has focus when you first open the form....  make no changes...  you want the data to show....  
After you have designed each of the other tabs and have placed and connected the subforms on each of the tabs...
Select the Subform control (not the form within the subform) and when you check its properties you delete the Source object value leaving the parent child information intact.

the ...  Select Case <CtrlName>.Pages.Item(<CtrlName>.Value).Name
 will return the name of the tab/page selected...  (naming convention is always important to clear code, do not use default names)

Let's say your tab control is called tabStudentInfo and each page is called pgCoursesTaken, pgClinicalStatus, pgPreviousEducation
when I click on the tab for the page CoursesTaken the select statement  tabStudentInfo.Pages.Item(tabStudentInfo.Value).Name will return "pgCoursesTaken"

the block of code from my prevous message will then reattach the subform if it is not already loaded

                If .SourceObject = "" Then
                    .SourceObject = "Student_Courses_sfrm"
                End If

this allows the main form to load quickly and display without loading a dozen subforms with their corresponding data.

The way I understand the process, subforms must load before the main form displays.  This makes heavy forms slow.
By loading the subforms on demand the main form can load and display without waiting for the additional data (that may or may not be needed) to load

Hi, I am quite confuse

What will be the process?
So far I did this
1. Created a main form, which is frmMain
2. I have several multiples forms fsubSNRate, FsubDBRate, etc...
3. So in the main form (frmMain) I have included tab control and tab control is referenced on each tab by one of this multiples subforms(forms)

And I  am quite confuse creating parent child ones and the code above, any other shot?
Thjanks

So far,
For test propose, I did this created an main form "main"
and on it I placed the tab form "TabCtl0"
And create 2 subforms
subDBRate
subSNRate

Option Compare Database
 
Private Sub TabCtl0_Change()
    Select Case TabCtl0_.Pages.Item(TabCtl0_.Value).Name
        Case "pgDBM"
            '---------------------------
            'Clinical Status Review
            '---------------------------
            With subDBMRate
                If .SourceObject = "" Then
                    .SourceObject = "subDBRate"
                End If
            End With
        
        Case "pgSNRate"
            '---------------------------
            'Student Course Review
            '---------------------------
            With subSNRAte
                If .SourceObject = "" Then
                    .SourceObject = "subSNRate"
                End If
            End With
    End Select
End Sub
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:

Looks OK to me, watch out for the .CourceObject it may be null rather than an empty string. Aslo in teh With for the subform you may need to state subformname.form
Cheers, Andrew

Andrew,

It is not working "Object Required" message appeared
I did the changes that you suggested

Option Compare Database
 
Private Sub TabCtl0_Change()
    Select Case TabCtl0_.Pages.Item(TabCtl0_.Value).Name
        Case "pgDBM"
            '---------------------------
            'DB Rate
            '---------------------------
            With subDBRate.Form
                If .SourceObject = Null Then
                    .SourceObject = "subDBRate"
                End If
            End With
        
        Case "pgSNRate"
            '---------------------------
            'SN Rate
            '---------------------------
            With subSNRate.Form
                If .SourceObject = Null Then
                    .SourceObject = "subSNRate"
                End If
            End With
    End Select
End Sub
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:

I normally reference it slightly differently, the select case refers to the TabControl,, The Case refers to the PageIndex property of the page. Also prefix it with Me so it is clear that it is a control rather than a variable.

Cheers, Andrew

    Select Case Me.TabCtl0
        Case Me.pgDBM.PageIndex
                                              

1:
2:

Both the index and name will work equally well...

However using the Name of the tab rather than it's index allows you to insert tabs or rearrange the order of the tabs without changing the code.

Select Case <CtrlName>.Pages.Item(<CtrlName>.Value).Name
        Case "pgClinicalStatus"

The name remains the same, regardless of the order of the tabs.

Hi,

On both cases I  have the same error message "method or data member not found"

I would appreciate your comments.

Thanks

Option Compare Database
 
Private Sub TabCtl0_Change()
    Select Case Me.TabCtl0_.Pages.Item(TabCtl0_.Value).Name
        'Case "pgDBM"
         Case Me.pgDBM.PageIndex
            '---------------------------
            'DB Rate
            '---------------------------
            With subDBRate.Form
                If .SourceObject = Null Then
                    .SourceObject = "subDBRate"
                End If
            End With
        
        'Case "pgSNRate"
         Case Me.pgSNRate.PageIndex
            '---------------------------
            'SN Rate
            '---------------------------
            With subSNRate.Form
                If .SourceObject = Null Then
                    .SourceObject = "subSNRate"
                End If
            End With
    End Select
End Sub

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:

OK Firstly please check the NAME property of the Tab Control, it probably doesnt have a _ at the end.
Secondly, you need to use either my option or RgGray3's option, you have mixed them.

In my option the Select Case statement is using the value of the tab control which is the currently selected tab and then in the Case it is checking the PageIndex property of the page controls.

Cheers, Andrew

    Select Case Me.TabCtl0
        Case Me.pgDBM.PageIndex

                                              

1:
2:

Why the underscores??  

Select Case Me.TabCtl0_.Pages.Item(TabCtl0_.Value).Name
                                   
                                     ^                                ^

Yes...  use either
 Select Case Me.TabCtl0_.Pages.Item(TabCtl0_.Value).Name
    with the ...    
    Case "pgDBM"
    Case "otherTabNames"

OR use the ...

Select Case Me.TabCtl0
with ...
        Case Me.pgDBM.PageIndex

But don't mix them

When you inspect the Tab control value  (Me.TabCtl0)  it will return the index value of the tab selected
 the index being the offset from the left...  (0 based... meaning the default tab is tab/page 0)   0,1,2,3,4,5,6,7...   how many tabs do you have...

When you use the .Name property of the selected tab....   you must compare it to the string value  NAME of the tab.

So if you have your tab pgDbm

you either will compare the value of the TabControl to the index value of the different tabs
or
compare the string name value of the selected tab

Andrew,

I fixed the mispelling. But still have a problem run-time error-424

Option Compare Database
 
Private Sub TabCtl0_Change()
    Select Case Me.TabCtl0
         Case Me.pgDBM.PageIndex
            '---------------------------
            'DB Rate
            '---------------------------
            With subDBRate
                If .SourceObject = Null Then
                    .SourceObject = "subDBRate"
                End If
            End With
        
        'Case "pgSNRate"
         Case Me.pgSNRate.PageIndex
            '---------------------------
            'SN Rate
            '---------------------------
            With subSNRate
                If .SourceObject = Null Then
                    .SourceObject = "subSNRate"
                End If
            End With
    End Select
End Sub
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:

RgGray3,

I got the same error "run-time error-424"

Thanks for any help

Private Sub TabCtl0_Change()
    'Select Case Me.TabCtl0
    Select Case Me.TabCtl0.Pages.Item(TabCtl0.Value).Name
 
         Case "pgDBM"
         'Case Me.pgDBM.PageIndex
            '---------------------------
            'DB Rate
            '---------------------------
            With subDBRate
                If .SourceObject = Null Then
                    .SourceObject = "subDBRate"
                End If
            End With
        
        'Case "pgSNRate"
         'Case Me.pgSNRate.PageIndex
            '---------------------------
            'SN Rate
            '---------------------------
            With subSNRate
                If .SourceObject = Null Then
                    .SourceObject = "subSNRate"
                End If
            End With
    End Select
End Sub

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:

Firstly qualify the subform control names using Me

            With Me.subSNRate

If the name doesn't appear when you press the fullstop after me then check that the Control Name is correct, it is the name of the control not the name of the subform, again these can sometimes be the same but it is the controlname you need here.

Cheers, Andrew

Make sure that you are using the SubForm Control Name and not the Subforms name when you are testing
(one reason I name the subform control with the Subforms name...  it works)

Make sure the subform control name is subDBRate  

I would strongly recommend you adopt a naming convention for all your objects such as tables, fields, forms and controls. The commonly used is the Leszynski/Reddick Guidelines for Microsoft Access and can be found http://www.databasedev.co.uk/naming_conv.html. You can also get mor details if your search the web.

Please note here that the recommendation is for the actual form to be prefixed fsub and the control to be prefixed sub. The reason for this is for easier debugging of code when you are not sure what you are trying to reference.

Cheers, Andrew

Well,

I understood, please check the subform propoerties for each example.
Thanks

Just quick question, I am still confuse.

I just made the changes suggested when I added a new subform object on each tab I was able to navigate well without retrieving the data.
Then, I just added record source to each subform and I got the data, but I think is not was i was looking for.

What will be your suggest?

You do not need to use a subform just to contain the subDBRate as you may as well add subDBRate directly to the Page and then set the SourceObject of thesubform control, I beleive you have an extra level of subform that you do not need.

Cheers, Andrew

I would appreciate if you should review my DB, thanks

I can't open the database, I suspect you have it as a 2003 DB and I currently only have 2002 on my PC. Sorry Andrew

Hi,

I converted to 97 version, so will be able to open.

Thanks

Hi after reviewing the database the code below works. Please note I have added the Option Explicit in the General Declarations section of the module. I would strongly recommend that this is in EVERY module as is eliminates logical errors caused by typos
Cheers, Andrew

Option Compare Database
Option Explicit
 
Private Sub TabCtl0_Change()
    Select Case Me.TabCtl0
    'Select Case Me.TabCtl0.Pages.Item(TabCtl0.Value).Name
 
         'Case "pgDBM"
         Case Me.pgDBM.PageIndex
            '---------------------------
            'DB Rate
            '---------------------------
            With Me.subDBRate
                If .SourceObject = "" Then
                   .SourceObject = "fsubDBRate"
                End If
            End With
        
        'Case "pgSNRate"
         Case Me.pgSNRate.PageIndex
            '---------------------------
            'SN Rate
            '---------------------------
            With Me.subSNRate
                If .SourceObject = "" Then
                   .SourceObject = "fsubSNRate"
                End If
            End With
    End Select
End Sub
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:

To be fair to RgGray3 the code below also works using RgGray3 method of identifying the current page. Cheers, Andrew

Option Compare Database
Option Explicit
 
Private Sub TabCtl0_Change()
    'Select Case Me.TabCtl0
    Select Case Me.TabCtl0.Pages.Item(TabCtl0.Value).Name
 
         Case "pgDBM"
         'Case Me.pgDBM.PageIndex
            '---------------------------
            'DB Rate
            '---------------------------
            With Me.subDBRate
                If .SourceObject = "" Then
                   .SourceObject = "fsubDBRate"
                End If
            End With
        
         Case "pgSNRate"
         'Case Me.pgSNRate.PageIndex
            '---------------------------
            'SN Rate
            '---------------------------
            With Me.subSNRate
                If .SourceObject = "" Then
                   .SourceObject = "fsubSNRate"
                End If
            End With
    End Select
End Sub
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:

Finally the code below assigns the query to the SubForm/SubReport on the main Form rather that assigning the SubForm that contains a SubForm that contains the query, hope that makes sense.

Cheers, Andrew

Option Compare Database
Option Explicit
 
Private Sub TabCtl0_Change()
    Select Case Me.TabCtl0
    'Select Case Me.TabCtl0.Pages.Item(TabCtl0.Value).Name
 
         'Case "pgDBM"
         Case Me.pgDBM.PageIndex
            '---------------------------
            'DB Rate
            '---------------------------
            With Me.subDBRate
                If .SourceObject = "" Then
                   '.SourceObject = "fsubDBRate"
                   .SourceObject = "Query.qryMetricsDB"
                End If
            End With
        
         'Case "pgSNRate"
         Case Me.pgSNRate.PageIndex
            '---------------------------
            'SN Rate
            '---------------------------
            With Me.subSNRate
                If .SourceObject = "" Then
                   '.SourceObject = "fsubSNRate"
                   .SourceObject = "Query.qryMetricsSNRate"
                End If
            End With
    End Select
End Sub
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:

Both cases, works fine. Thanks but just one thing. How can I left by default the first tab view when the form load?

I think we are good.

also, how can you display two forms on the same tab using the same logic above?