• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 35
  • Last Modified:

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

0
Eirman
Asked:
Eirman
  • 3
  • 3
  • 2
  • +1
2 Solutions
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now