CASE statement to hide/unhide tabs (on page control) based on cascading combos

[skydivetom]

Experts:

I have a form with a page control. Upon opening the form, only the "Main" page/tab is shown. Subsequent tabs/pages ("Military", "Civilian", and "Contractor") are set to invisible.

Now, the main tab includes various fields including two cascading combo boxes. Depending on the value selection of the two cascading combo values, I want to unhide/hide the other pages in order to get to other fields depending (not all fields in my table apply to all three categories military, civilian, contractor).

At this time, I need some help with preferably creating a CASE statement (or nested IF statement).

For Combo #1, user can selected from the following values:
- "---" 'cboService.ItemData(0)
- "Navy" 'cboService.ItemData(1)


- "Civilian" 'cboService.ItemData(2)
- "Air Force" 'cboService.ItemData(3)
- "Army" 'cboService.ItemData(4)
- "Coast Guard" 'cboService.ItemData(5)
- "Marines" 'cboService.ItemData(6)

For combo #2, it's a cascading value based on the service:
- If cboService.ItemData was either 1, 3:6 then 2nd combo will show
-- "Officer" Me.cboType.ItemData(0)
-- "Enlisted" Me.cboType.ItemData(1)

However, if cboService.ItemData was "2" then 2nd combo will show
-- "Civilian" Me.cboType.ItemData(2)
-- Contractor" Me.cboType.ItemData(3)

Let's recap:
- Any combination of military personnel regardless of "service" and "type" should unhide the MILITARY tab on my page control.
- Alternatively, if user selected "Civilian" on first combo and then "Civilian" again on second combo, I want my CIVILIAN tab to become visible.
- And finally, if user selected "Civilian" on first combo and then "Contractor" on second combo, I want my CONTRACTOR tab to become visible.


So, I envision my CASE statement to be something like this:

Select Case PagesUnhide
   Case Me.cboService.ItemData(0)
     'Do nothing (that's the default "---")
      
   Case Me.cboService.ItemData(2) AND Me.cboType.ItemData(2)            'This is civilian | civilian        
      CivlianPage = Visible
  
   Case Me.cboService.ItemData(2) AND Me.cboType.ItemData(3)            'This is civilian | contractor       
      ContractorPage = Visible
  
   Case Else                                                                   'This is any combination of military services and officer or enlisted for "type" 
      MilitaryPage = Visible
      
End Select

Could anybody please assist me with creating the required CASE statement that would hide/unhide the 3 tabs on my page control?

Thanks,
EEH

[accesstos]

Hi EEH!

Maybe the code below is that you are looking for:

Option Compare Database
Option Explicit

'This is the form's code module.

Private Sub ShowHideTypePages()
    With Me.TabCtrl
        .Pages("pgCivilian").Visible = (Nz(Me.cboType, "") = "Civilian")
        .Pages("pgContractor").Visible = (Nz(Me.cboType, "") = "Contractor")
        .Pages("pgMilitary").Visible = ((Nz(Me.cboService, "") <> "Civilian") And (Nz(Me.cboService, "---") <> "---"))
    End With
End Sub

Private Sub cboService_AfterUpdate()
    '[...]
    With Me.cboType
        .Value = Null
        .Requery
    End With
    cboType_AfterUpdate
End Sub

Private Sub cboType_AfterUpdate()
    ShowHideTypePages
End Sub

Good luck with your project!
John

[skydivetom]

John - thank you... I placed your proposed code into the form.

When I click/select combo "Service" and select any of the military services, the tab "Military" pops up. Great!

However, ....
-... when switching let's say from "Navy" to "Civilian", the military tab disappears (Great!!) but neither "Civilian" nor the "Contractor" pages show up AFTER I also make a selection for "Type".
-Likewise, when I open the form and create a new record and then immediately select Service = "Civilian" AND Type "Civilian" or "Contractor, nothing happens either.

In summary, I need to ensure the following:
- Whether a return to an existing record or create a new record, any change in service and type must result in pages becoming visible/invisible.

What am I missing that prevents the Civilian or Contractor tab to *not* become visible? If this because to the secondary selection in cboType?

[accesstos]

EEH, you are welcome!

At first, replace the

 (Nz(Me.cboService, "") <> "---")

with the

(Nz(Me.cboService, "---") <> "---")

What am I missing that prevents the Civilian or Contractor tab to *not* become visible?

Put a breakpoint on the line "End With" of the sub ShowHideTypePages to see what values returns the comboboxes at run time.
If the cboType is a multi-column combobox, point to the column with the text "Civilian / Contractor" via the Column property of cboType as shown below:

Nz(Me.cboType.Column(1), "") = "Civilian"

Can you post the code, even of cboService_AfterUpdate(), as your tryed it in your db?

[skydivetom]

John:

I appreciate the continued assistance... I truly appreciate it!!

As suggested, I put a breakpoint on the line "End With"... every time I execute it the
- **.Pages("Civilian").Visible** AND **.Pages("Contractor").Visible** return a false when hovering over ".Pages".
- However, .Pages("Military") always return as true

Attached is an extract from my db with the sample table, lookup table for service and types as well as the form where VBA requires modification to hide/unhide the tabs. Table only contains a single dummy record. Feel free to add/change staff member's contact info for testing purposes.

Upon opening the form, the cascading combo boxes "Service" and "Type" are below the staff member's name. If you change any of Michael's Smith service/type values, you notice that only "Military" gets activated.

Thank you for any additional help!

EEH

[accesstos]

Sorry EEH. Unable to open your file in my system (Unrecognized Database Format).
Check my simple example in the attachment, and as I sayed, ensure that the code refers to the proper column of cboType.

P.S.:
In break mode, hover on Me.cboType to see its value, or, insert a

Debug.Print "Type: " & me.cboType

somewhere in the code and look at the Immediate window (Ctrl+G) after every update of comboboxes.

And, don't worry, we are too close to the solution.

[skydivetom]

John:

Your sample db works fine; however, it won't work in my actual database. Here's why, there's actual a third level (i.e., Rank/Title) which values is driven by the 2nd combo "Type". So, if officer is selected then I only get officer ranks (which also vary based on service).

I did not include the 3rd level cascading as the rank title (in my view) has nothing to do whether someone is military or civilian. That said, however, when plugging in your code, it's now overwriting the 3rd level lookup information. Nothing seems to be easy.

Below is the full VBA code before integrating your latest solution.

Since you uploaded your db in .zip formatting, are you saying you can't unzip a document? Things would be much clearer if you were to view my sample database. If you can't unzip, is there another method I could provide you the sample db? "A picture is worth a thousand words.

Thank you,
EEH

Private Sub cboService_Change()

    'Refreshes and clears combo boxes
    Me.cboType.Requery
    Me.cboType = Me.cboType.ItemData(0)
    Me.cboType = Null

    Me.cboRankTitle.Requery
    Me.cboRankTitle = Me.cboRankTitle.ItemData(0)
    Me.cboRankTitle = Null
    
End Sub

Private Sub cboService_AfterUpdate()

    'Calls function cboType in order to hide/unhide pages
    With Me.cboType
        .Value = Null
        .Requery
    End With
    cboType_AfterUpdate
    
End Sub

Private Sub cboType_Change()

    'Refreshes and clears combo boxes
    Me.cboRankTitle.Requery
    Me.cboRankTitle = Me.cboRankTitle.ItemData(0)
    Me.cboRankTitle = Null
       
End Sub

Private Sub cboType_AfterUpdate()

    'Calls the function ShowHideTypePages
    ShowHideTypePages
    
End Sub

Private Sub ShowHideTypePages()
    
    'Page control's tabs (Military | Civilian | Contractor) are hidden/unhidden depending on value selection of combo boxes "Service" AND "Type" based on the following logic:
    '- Any service value (Navy, Air Force, Army, Coast Guard, or Marines) unhides the "Military tab (**regardless** of type officer/enlisted).
    '- Alternatively, if service equals "Civilian" AND type equals "Civilian" OR "Contractor" either page "Civilian" or "Contractor" is brought up, respectively.
    
    With Me.tabContacts
    
        .Pages("Civilian").Visible = (Nz(Me.cboType, "") = "Civilian")
        .Pages("Contractor").Visible = (Nz(Me.cboType, "") = "Contractor")
        .Pages("Military").Visible = ((Nz(Me.cboService, "") <> "Civilian") And (Nz(Me.cboService, "---") <> "---"))
                
    End With
    
End Sub

Private Sub cboRankTitle_Change()

    'Refreshes combo box
    Me.cboGoToContact.Requery
    
End Sub

[accesstos]

No, I can unzip the .zip file but can not open the .accdb file(!). I get the message "Unrecognized Database Format". I assume that is a version incompatibility.

Anyway, (because I have to go for now) keep in mind that the logic of the code is
PageOfCivilian.visible = (cboType = "Civilian").
If this condition is true, then the page is visible.

Regards,
John

[skydivetom]

John:

Not sure where to go from here... keep in mind that cboType will list either "Civilian" and "Contractor".

Values in combo Service: Any of the 5 military services, Civilian
Value in combo Type: For any of the 5 military services it will bring up "Officer" or "Enlisted"; if civilian was selected in service, type will then bring up "Civilian" or "Contractor"

Reattached the database in MDB format. I'd welcome if you have another look at my specific construct when you have a chance.

Thank you in advance.
EEH

[skydivetom]

Last follow-up... I wish I would have gotten your solution to work. Unfortunately, I didn't... so I changed the whole concept. Not very elegant but at least it works.

Certainly would welcome if you can have another look at the posted db. If it works in that version, I'll update my code. In the meanwhile, I have to go with "ugly code" below:

Private Sub cboService_AfterUpdate()

    tabContacts.Pages(1).Visible = False
    tabContacts.Pages(2).Visible = False
    tabContacts.Pages(3).Visible = False
        
End Sub



Private Sub cboType_AfterUpdate()

    'Page control's tabs (Military | Civilian | Contractor) are hidden/unhidden depending on value selection of combo boxes "Service" AND "Type" based on the following logic:
    '- Any service value (Navy, Air Force, Army, Coast Guard, or Marines) unhides the "Military tab (**regardless** of type officer/enlisted).
    '- Alternatively, if service equals "Civilian" AND type equals "Civilian" OR "Contractor" either page "Civilian" or "Contractor" is brought up, respectively.
    If ((Nz(Me.cboType, "") = "Officer") Or Nz(Me.cboType, "") = "Enlisted") Then
        tabContacts.Pages(1).Visible = True
        tabContacts.Pages(2).Visible = False
        tabContacts.Pages(3).Visible = False
        
    ElseIf (Nz(Me.cboType, "") = "Civilian") Then
        tabContacts.Pages(1).Visible = False
        tabContacts.Pages(2).Visible = True
        tabContacts.Pages(3).Visible = False
        
    ElseIf (Nz(Me.cboType, "") = "Contractor") Then
        tabContacts.Pages(1).Visible = False
        tabContacts.Pages(2).Visible = False
        tabContacts.Pages(3).Visible = True
    
    Else
        tabContacts.Pages(1).Visible = False
        tabContacts.Pages(2).Visible = False
        tabContacts.Pages(3).Visible = False
    
    End If
    
End Sub

[June7]

Call ShowHideTypePages in Load event.

Set [Event Procedure] in cboType AfterUpdate property.

Code works.

[skydivetom]

June 7 -- thank you for chiming in... code works great now. Thank you, accesstos, for providing a very smooth solution. June7... thanks for helping out (again).

Cheers,
EEH

[accesstos]

EEH, unfortunately I am still can't open your files, not even in mdb format. I think that is an "Access 64bit to Access 32bit" problem.
But, I am glad that your issue is solved.
I am glad to help!

Cheers,
John

[skydivetom]

Thank you, John... excellent & elegant solution.

[June7]

I had no problem opening the mdb.