Hide Ribbon problem with Tab Control

[LakotaMan]

Hi All,

I’m having the devil of a time with this one:
Am using ver. 2007 and have got the USysRibbons table and a public procedure to hide and show the ribbon at will. Most of the time this is working fine, except . . .

I have a form with a tab control on it that has 3 pages. The first page, has the main form controls on it, the other two tab pages have sub-forms on them. The form shows/hides tab pages depending on the value of the public variable strUserType.

When the form opens (with the first tab page active, and the other 2 hidden) the ShowHide ribbon code works fine and the ribbon is hidden, Depending on the value of strUserType, other tabs are shown and their sub-forms disabled and one is enabled. The pattern I have discerned here is that whenever the tab page with a sub-form is enabled the ribbon show up.

Not only can I not seem to fix it, I don’t understand why it’s happening.
Here’s my routine:

Public Sub ShowHideRibbon(strFormName)
    If strUserType = "Admin" Then
        If Left(strFormName, 3) = "frm" Then
            Forms(strFormName).RibbonName = "ShowTheRibbon"
        ElseIf Left(strFormName, 3) = "rpt" Then
            Reports(strFormName).RibbonName = "ShowTheRibbon"
        End If        
    Else
        If Left(strFormName, 3) = "frm" Then
            Forms(strFormName).RibbonName = "HideTheRibbon"
        ElseIf Left(strFormName, 3) = "rpt" Then
            Reports(strFormName).RibbonName = "HideTheRibbon"
        End If
    End If
End Sub

I call it in the Load even of the main form:

    ShowHideRibbon (Me.Name)

The value of strUserType is coming through correctly, I’ve checked the variable during run time, and it’s getting the proper value so that the Else condition executes.
I’ve tried re-calling the routine from the click event of the tab page (not the tab control) and also from the Change event of the tab control –though I don’t know why I’d need to call it again—neither trick worked.
I realize there’s plenty I don’t understand about tab controls as those and sub-forms give me most of my headaches in Access, and this is the place where I’ve learned the most. I would be grateful, as always for any insights.
LM

 

[lameid]

I've never used Access 2007 for any length of time, but it sounds like the ribbon status is re-evaluated or set to show every time a form becomes visible... You may have to run the code immediately after making the sub-form visible.

I assume that you are not running the code on the sub-form itself or that your name your sub forms so they being with frm.

I would step through the code that changes the status and figure out which line causes the status to appear. That should at least give you insight as to when.

This might fall under some things are easier to fix than they are to understand.

 

[lameid]

Oops I was not thinking about the fact you tried re-running the code. I do think that stepping through may give you some insight.

 

[LakotaMan]

lamid:

I think you are right about the naming problem. I name all my subforms with "sfrm", a fact that slipped my mind when I wrote the public procedure. But now I'm having syntax problems. Here's my amended procedure:

    If strUserType = "Admin" Then
        If Left(strFormName, 3) = "frm" Then
            Forms(strFormName).RibbonName = "ShowTheRibbon"
        ElseIf Left(strFormName, 3) = "rpt" Then
            Reports(strFormName).RibbonName = "ShowTheRibbon"
        End If
    Else
        If Left(strFormName, 3) = "frm" Then
            Forms(strFormName).RibbonName = "HideTheRibbon"
        [b][I]ElseIf Left(strFormName, 4) = "sfrm" Then
            Forms(strFormName).RibbonName = "HideTheRibbon"[/I][/b]
        ElseIf Left(strFormName, 3) = "rpt" Then
            Reports(strFormName).RibbonName = "ShowTheRibbon"
        End If
    End If

I get the usual Error #2450: "Microsoft Access can't find the form sfrmResponses referred to in a macro expression or Visual Basic code"
That is the name of the subform, but not it's full reference when it's placed on the main form.
I tried this:

        ElseIf Left(strFormName, 4) = "sfrm" Then
            strFormName = "Forms!frmComplaints." & strFormName
            Forms(strFormName).RibbonName = "HideTheRibbon"

but still get the same error message (except with the full reference name)
I am calling this from one of the subforms' load event.
Can you help me figure the syntax out?
LM

 

[lameid]

I get it now RibbonName property is like the Toolbar property.

I would think that you would simply have the desired results if you did nothing in the sub form's on load event.

If not I would use Select Case instead of nested IF's... just more readable.

    If strUserType = "Admin" Then
        Select Case Left(strFormName, 3) 
        Case "frm"
            Forms(strFormName).RibbonName = "ShowTheRibbon"
        Case "rpt"
            Reports(strFormName).RibbonName = "ShowTheRibbon"
        End Select 
    Else
        Select Case Left(strFormName, 3) 
        Case "frm", "sfr"
            Forms(strFormName).RibbonName = "HideTheRibbon"
        Case "rpt"
             Reports(strFormName).RibbonName = "ShowTheRibbon"
        End Select
    End If

That said, it doesn't solve the fact that the subform is not directly in the forms collection...

Public Sub ShowHideRibbon(ByRef frmOrRpt as Object)
    If strUserType = "Admin" Then
        frmOrRpt.RibbonName = "ShowTheRibbon"

    Else

        frmOrRpt.RibbonName = "HideTheRibbon"

     End If
End Sub

Of course in this case you have to pass the object instead of the name.