Improve Hide/Unhide Toggle Code

I use this code on a command button to hide and unhide navigation & the ribbon.
It works fine about 95% of the time.
When it does fail, it just closes the form (with no error message).
I think It only fails when hiding (I can't pin down the circumstances).
It never fails twice in a row.

Can anyone improve on the code?

Private Sub CommandHIDE_Click()
    Static IsHidden As Boolean

    If IsHidden Then
        DoCmd.ShowToolbar "Ribbon", acToolbarYes
        DoCmd.SelectObject acTable, , True
        IsHidden = False
    Else
        DoCmd.ShowToolbar "Ribbon", acToolbarNo
        DoCmd.NavigateTo "acNavigationCategoryObjectType"
        DoCmd.RunCommand acCmdWindowHide
        IsHidden = True
    End If
End Sub

Open in new window

LVL 24
EirmanChief Operations ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I don't use NavigateTo, but the "ac" argument is a Constant, and shouldn't be enclosed in quotes:

DoCmd.NavigateTo acNavigationCategoryObjectType
1
Gemini GVBA  & Database ProgrammerCommented:
I make a button for each to Disable/Enable the ribbon:
 
Private Sub cmdDisable_Click()
    On Error Resume Next
    
    GlobalModule.Logging "Disable"
    
    Dim db As DAO.Database
    Dim PR As DAO.Property
    Set db = CurrentDb
    Set PR = db.CreateProperty("AllowBypassKey", dbBoolean, False)
    db.Properties.Append PR
    db.Properties("AllowBypassKey") = False
    Set db = Nothing
    DoCmd.ShowToolbar "Ribbon", acToolbarNo
    MsgBox "Bypass Key Disabled"
    
    

End Sub

Open in new window


Private Sub cmdEnable_Click()
    On Error Resume Next
    
    GlobalModule.Logging "Enable"
    Dim db As DAO.Database
    Dim PR As DAO.Property
    Set db = CurrentDb
    Set PR = db.CreateProperty("AllowBypassKey", dbBoolean, True)
    db.Properties.Append PR
    db.Properties("AllowBypassKey") = True
    Set db = Nothing
    DoCmd.ShowToolbar "Ribbon", acToolbarYes

    MsgBox "Bypass Key Enabled"

End Sub

Open in new window

0
Gemini GVBA  & Database ProgrammerCommented:
Remove the globalmodule.logging code in your instance.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

EirmanChief Operations ManagerAuthor Commented:
Remove the globalmodule.logging code in your instance.
Please evplain what you mean.
0
Gemini GVBA  & Database ProgrammerCommented:
I cut and pasted that from my code, i have a globalmodule i use to log when ever a uses presses the enable/disable button so delete those lines otherwise it will post an error that it can't find the module.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'm not sure how code to enable or disable the Shift Key Bypass feature in Access is relevant to this discussion, but perhaps I've missed something.

Eirman: Did you try my suggestion? Your code should work as-is to toggle the ribbon and such, but as noted you don't put constants in quotes.
0
EirmanChief Operations ManagerAuthor Commented:
Eirman: Did you try my suggestion?
Not yet Scott. - I try it tomorrow and probably close this by Saturday.

Thanks to all contributors
0
mbizupCommented:
Interesting question! Actually, it looks like the quotes around acNavigationCategoryObjectType are indeed necessary in this case.  I believe what is happening is that your static variable isHidden is getting lost, possibly due to unrelated runtime errors... or you can recreate your issue by switching to design view, and then back to form view.  

You can avoid this problem by using something that does not get lost in such cases. One option is using the Tempvars Collection, which persists as long as your database is open, regardless of errors, etc.

Or you can create a table, tblShowNavPane, with a yes/no field isHidden, and use that as the recordsource for your form.  Then modify your code to use the isHidden field instead of a static variable:

Private Sub CommandHIDE_Click()

    If Me.isHidden = True Then
        DoCmd.ShowToolbar "Ribbon", acToolbarYes
        DoCmd.SelectObject acTable, , True
        Me.isHidden = False
    Else
        DoCmd.ShowToolbar "Ribbon", acToolbarNo
        DoCmd.NavigateTo "acNavigationCategoryObjectType"
        DoCmd.RunCommand acCmdWindowHide
        Me.isHidden = True
    End If
    
End Sub

Open in new window


If your form is bound to another table or query, you can lookup the value of is Hidden through VBA (such as the DLookup function).

An advantage to storing isHidden in a table is that the visibility of the Nav Pane and Ribbon will be 'remembered' when you close/reopen your database.

(And as an aside, you can use the same block of code to toggle the caption of your button between 'Show' and 'Hide' if you want to.)
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EirmanChief Operations ManagerAuthor Commented:
I think you have it right mbizup > "static variable isHidden is getting lost"

I've put the TEMPVARS on my todo list and in the meantime,
I'll put up the extra 10 clicks a week.

Thanks to everyone (including Scott)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.