Enable Page based on selection from combo box

[Nadine67]

Hello and thank you in advance for any attention this post may receive



Windows 8.1
MS Access 2013

I would like to enable 2 of 4 pages (tabs) based on a selection from a combo box which lists the names of the 4 tabs. Actually I would like to enable the fields on the tabs.

Is this possible to do on my form? If so I am not sure how to enable this.

Tab 1 - fields A, B C
Tab 2 - fields D, E F
Tab 3 - fields G H I
Tab 4 - fields J K L

Any help would be greatly appreciated!

Thanks Nadine

[Micron]

I can't decide if you've got a tab control with 4 tabs or 4 forms in tabbed view...

[Nadine67]

Woops sorry about that. I have one tab control with 4 tabs.

[Micron]

Set all tab pages to be enabled = False on form opening. Based on the combo selection (AfterUpdate event) enable the chosen tab. This will automatically enable the controls on that tab. The tricky part is, what to do when the selection is changed? Disable the enabled tab(s)? If so, your code should probably cycle through all tabs and disable, then enable the chosen one.

[Micron]

played around and came up with this:

Private Sub Form_Open(Cancel As Integer)

SetTabControl (Null)
End Sub

Private Sub Combo1_AfterUpdate()

SetTabControl (Me.Combo1)
End Sub

Function SetTabControl(pge As Variant)
Dim cntr As Integer

cntr = 0 'optional insurance
For cntr = 0 To Me.TabCtl3.Pages.Count - 1
Me.TabCtl3.Pages(cntr).Enabled = False
Next
If Not IsNull(pge) Then
    With Me.TabCtl3.Pages(pge)
        .Enabled = True
        .SetFocus
    End With
End If

End Function

[Nadine67]

Hi Micron

Thank you for your help.....and apologies for not responding sooner (I have been out of action for quite a while).

So I have set 'Enabled = No' on 3 of the 5 tab pages. I didn't set the other two because page1 contains the Combo Box from which I will make the selection which will drive the enabling of either pages 2,3,4, and Page5 needs to be enabled always.
I have added the Sub Form_Open to the Form and Combo1_AfterUpdate code to the Combo Box. However I am not sure where to add the SetTabControl Function.

Thank you Micron!

Nadine

[Nadine67]

Hi Micron

Actually more to the point....is the SetTabControl set against the individual tab pages or the TabControl?

[Nadine67]

Ok Micron, so I have set the function against the TabControl. Correct? When I debug I get "Compile Error: Invalid Use of Me keyword'. Do I need to 'set' Me?

[CODE][Function SetTabControl(pg As Variant)
Dim cntr As Integer
cntr = 0 'optional insurance
For cntr = 0 To Me.TabCtl3.Pages.Count - 1
Me.TabCtl3.Pages(cntr).Enabled = False
Next
If Not IsNull(pge) Then
With Me.TabCtl3.Pages(pge)
.Enabled = True
.SetFocus

End With
End If
End Function
/CODE]

[Micron]

Am away. Limited WiFi and on phone so short answer. Me can only be used to refer to object that allows such a reference. Need to use full reference when code is not on a form/report: Forms!formname.controlname. Perhaps others can chime in while I am gone.

[ssanfu]

Move (not copy) the "Function SetTabControl(pge As Variant)" code to the form module.
Your form module should look something like

Private Sub Form_Open(Cancel As Integer)
    SetTabControl (Null)
End Sub


Private Sub Combo1_AfterUpdate()
    SetTabControl (Me.Combo1)
End Sub


Function SetTabControl(pge As Variant)
    Dim cntr As Integer

    cntr = 0    'optional insurance
    For cntr = 0 To Me.TabCtl3.Pages.Count - 1
        Me.TabCtl3.Pages(cntr).Enabled = False
    Next
    If Not IsNull(pge) Then
        With Me.TabCtl3.Pages(pge)
            .Enabled = True
            .SetFocus
        End With
    End If

End Function

[Nadine67]

Thank you Steve

So I am not sure what you mean exactly by 'Move not copy'.

I renamed Combo1 to the correct field name which is is 'Type' (not sure if this is a forbidden word or not).

I debugged the first two codes and there was no issue. However the third piece of code, Function, returned a 'Compile Error: Invalid use of Me keyword'.

'TabCtrl3' - is this the tab control field name or is it code to indicate a tab control with 3 pages?

Cheers Steve

Nadine

[Micron]

He means don't have two versions of it. There are 3 basic types of code modules. Code behind a form or report is contained in a form or report module and can only be "seen" by the form or report it resides on; i.e. its scope is limited to the form or report. Code in a standard module can be utilized from anywhere in the database, provided the sub or function does not start with the Private declaration. If it does, its scope is limited to the module that it resides in. A Class module is a different animal again. Rather than me expound further, Google ms access modules for a better understanding. For your purposes, Me is a keyword that is used to shorten the reference to a form/report object and its scope is limited to the form or report (& maybe data pages, but I don't know). You cannot use the me keyword in code that resides in a standard module - it has no "Me". I think ssanfu (thanks for chiming in!!) assumes you placed the function in a standard module (I would assume the same) and intended to solve your Me error by moving the code to the form. Had you simply copied it and left the original in a standard module, you would have generated an ambiguous name error. If you did not put the code in a standard module, you may have introduced a problem by putting the combo on a page. The usual approach would be to have this on the form, not the tab control and that's how I did it.

Per your other comments/questions:
is the SetTabControl set against the individual tab pages or the TabControl? Don't know what you mean by 'set'. The function acts on the pages of the control. Set has a specific meaning in Access, which doesn't seem applicable here.
Yes, Type is a reserved word and I would not use it. You should NEVER have an issue with reserved words if you adopt a standard naming convention for every variable or name you create and keep a reference to reserved words handy. As an afterthought, I remembered one does not usually use such a convention for table field names.

[Nadine67]

Per your other comments/questions:
is the SetTabControl set against the individual tab pages or the TabControl? Don't know what you mean by 'set'. The function acts on the pages of the control. Set has a specific meaning in Access, which doesn't seem applicable here.
Yes, Type is a reserved word and I would not use it. You should NEVER have an issue with reserved words if you adopt a standard naming convention for every variable or name you create and keep a reference to reserved words handy. As an afterthought, I remembered one does not usually use such a convention for table field names.

Thank you Micron for taking the time to explain. I do appreciate it.

What I meant was is SetTabControl on the TabControl or TabPages, but you have answered my question by saying 'on the form'.

I get what you mean about naming convention - typically my combo boxes start with 'cbx' and this one should've been named cbxBenefitType, but in my laziness/ignorance I didn't do that.

I will let you know how I go once I put your advice into play.

Cheers!

[Nadine67]

Ok I started over again and here goes...


I have this code on the form 'On Open'

Private Sub Form_Open(Cancel As Integer)
    SetTabControl (Null)
    
End Sub

I have this code on the ComboBox 'After Update'

Private Sub Type_AfterUpdate()
    SetTabControl (Me.Type)
    
End Sub

And I have this code in a Module BUT how where do I put it on the form?

Function SetTabControl(pge As Variant)
Dim cntr As Integer
cntr = 0 'optional insurance
For cntr = 0 To Me.TabCtl3.Pages.Count - 1
Me.TabCtl3.Pages(cntr).Enabled = False
Next
If Not IsNull(pge) Then
    With Me.TabCtl3.Pages(pge)
        .Enabled = True
        .SetFocus
    End With
End If
End Function

[Nadine67]

Is it possible to do what I want with an if?

If ComboBox selection = "Cat" then enable page "Cat"
Else If ComboBox selection = "Dog" then enable page "Dog"
Else If ComboBox selection = "Bird" then enable page "Bird"

If so then I still am stumped as to how to write it.