• Modifying Access 2007 Switchboard form

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » Modifying Access 2007 Switchboard form

    Author
    Topic
    #482443

    I have a Switchboard form which runs on startup and have chosen to ‘Hide Navigation Pane fo’r the current database. I would like a ‘for advanced users’ button on the Switchboard form to close the form, leaving the database open, and re-display the navigation Pane?

    Viewing 8 reply threads
    Author
    Replies
    • #1326838

      David,

      Here’s some code that will get the trick done.
      I’d recommend you assign a Shortcut Key to the HideNavPaneShowSwitchboard() routine if not both although you could modify the switchboard code to run the other one. :cheers:

      Code:
      Public Sub CloseSwitchBoardShowNavPane()
      
         DoCmd.SelectObject acTable, , True
         Forms![switchboard].Visible = False
      
      End Sub
      
      Public Function HideNavPaneShowSwitchboard()
      
         Forms![switchboard].Visible = True
         DoCmd.NavigateTo "acNavigationCategoryObjectType"
         DoCmd.RunCommand acCmdWindowHide
      
      End Function
      

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

      • #1326912

        Thanks for that. It is a long time since I looked at VBA – I assume I just create this macro in the VBA editor, name it and link it to a button in the Switchboard menu??

    • #1326915

      David,

      You are correct. Make sure you create a new module to paste the code into. :cheers:

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

    • #1326930

      Well, I’m getting into more problems than I thought. Firstly, a question on your sub “CloseSwitchBoardShowNavPane(). The second line is easy to understand, it hides the Switchboard, but does the first line show the Nav Pane?
      Next Question is that having created a new module with the first sub in it, I cannot figure out how to attach it to a button in aSwitchboard Manager page. With other macros I have created, and which are listed in the Nav Pane, i.e. to run a Query, I have used the Switchboard Manager “Run Macro” option. This sub does not appear in the drop-down list of macros. I have tried in the Access “Create Macro” screen to attach this sub using “Run Macro”, “Run Code”, and “Run Command”, but cannot seem to find a way to get at this sub. Assistance for the mentally lacking greatly appreciated!

    • #1326943

      David,

      Sorry I didn’t test this further.
      First, the code runs correctly if executed from the VBE.
      Second, you do have to declare the Subs as Public Sub ….. to call them from a Switchboard.

      If attached to the Switchboard via the RunCode option I get the error:
      30490-AccessError
      I even get this error on a simple procedure to display a message!
      I know I have done this successfully in Access 2003 but can’t lay my hands on the code right now. I’ll get back when I find out what is going on. Perhaps while I’m looking someone else will offer some advice. :cheers:

      Update: Ok I got it sorted.
      1. Make sure DO NOT include the () after the function/sub name in the Switchboard function name box!
      2. You’ll have to assign a shortcut key to the function to reshow the Switchboard and Hide the NavPane. :cheers:

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

      • #1326946

        How do I edit your sub to make it public – not sure of the exact format of the statements. Here is what I have in VB
        Option Compare Database
        Sub CloseSwitchBoardShowNavPane()
        DoCmd.SelectObject acTable, , True
        Forms![switchboard].Visible = False
        End Sub

        • #1326950

          David,

          Just put Public in front of Sub, e.g. Public Sub routinename() :cheers:
          FYI: I’ve adjusted the original code post to reflect this.

          May the Forces of good computing be with you!

          RG

          PowerShell & VBA Rule!
          Computer Specs

          • #1326960

            Well I tried everything I can think of, but I still can’t attach this sub to an Access macro. Doing something wrong but I don’t know what

    • #1326949

      Hey All,

      I’ve been googling and reading and I still can’t figure out how to simply assign a keyboard shortcut to run a VBA routine in Access 2010. This was a piece of cake in 2003 with AutoKeys macro. HELP! A tutorial w/pictures would be useful as all the things I’ve found the text steps don’t follow through. :cheers:

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

      • #1326964

        Hey All,

        I’ve been googling and reading and I still can’t figure out how to simply assign a keyboard shortcut to run a VBA routine in Access 2010. This was a piece of cake in 2003 with AutoKeys macro.

        I am not aware of anything changing with this from 2003. I still just use an AutoKeys macro like I used to. (But I never use accdb format. Does that affect things?)

        30491-autokeysmacro

    • #1326967

      Sorry to intervene, but what’s wrong with adding an event procedure to the On Click event of a button and calling the sub?

    • #1326974

      John,

      Thanks I didn’t know about the SubMacro thingy but your picture made it clear.
      I had to make one other change and that was to make the HideNavPaneShowSwitchboard routine a Function as the Macro didn’t like it being a Sub {FYI: I modified the original code post to reflect this}.

      Public Function HideNavPaneShowSwitchboard()

      Now all works as I think the OP wanted it to. :cheers:

      Switchboard Setup:
      30492-SwitchboardSetup
      Switchboard View:
      30493-SwitchboardView
      AutoKeys Setup:
      30494-AutoKeys
      Also note that unlike Switchboard the Macro setup requires the () after the Function name!

      Two additional things you might want to consider:

      1. The 1st time the Switchboard is displayed the Navigation Pane is still visible, so you probably need to code it’s invocation to hide the Nav Pane.

      2. If F12 is pressed before the Switchboard has been displayed it will error out. What is need is a check to see if the Switchboard is loaded before trying to make it visible and if not loaded then load it.

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

      • #1326975

        I did a bit of Google and also found that changing to a function did the trick. I did not need to use Autokeys. Thanks for all your efforts

    • #1326976

      David,

      Glad you got it sorted. I got curious so I worked some more and here’s a version of the second routine that checks to see if the Switchboard is loaded and loads it if not. So pressing the hot key {F12 is my case} works every time.

      Code:
      'Code to hide the navigation pane and  display the switchboard in Access 2007-2010
      Public Function HideNavPaneShowSwitchboard()
      
         If CurrentProject.AllForms("Switchboard").IsLoaded Then
           Forms![switchboard].Visible = True
         Else
           DoCmd.OpenForm "Switchboard", acNormal
         End If
         DoCmd.NavigateTo "acNavigationCategoryObjectType"
         DoCmd.RunCommand acCmdWindowHide
      
      End Function
      

      :cheers:

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

      • #1326979

        I have tested your new macro – it is close. My Access normaly opens with Switchboard showing and Nav Pane hidden (I have hidden it in Current database options), so what I am looking for is:

        1 Close Switchboard
        2 Display Nav Pane

        Your new macro closes the Nav Pane and leaves the Switchboard visible according to my tests.

    • #1327015

      David,

      The First of the two macros: CloseSwitchBoardShowNavPane() does this if you attach it to a Switchboard item using the RunCode option in the Switchboard Manager. Isn’t this what you wanted or do you also want to run this one from a key combination? :cheers:

      Update:

      Ok I’ve explored some more and I’ve come up with this:

      Code:
      'Code to Toggle Navigation Pane and  Switchboard in Access 2007-2010
      'e.g. Switchboard on Navigation Pane off & visa versa.
      
      Public Function HideShowNavPaneSwitchboard()
      
         If CurrentProject.AllForms("Switchboard").IsLoaded Then
           If Forms![switchboard].Visible Then
             DoCmd.SelectObject acTable, , True
             Forms![switchboard].Visible = False
           Else
            DoCmd.NavigateTo "acNavigationCategoryObjectType"
            DoCmd.RunCommand acCmdWindowHide
            Forms![switchboard].Visible = True
           End If
         Else   'Not Loaded
           DoCmd.SelectObject acTable, , True
           DoCmd.OpenForm "Switchboard", acNormal
           DoCmd.NavigateTo "acNavigationCategoryObjectType"
           DoCmd.RunCommand acCmdWindowHide
        End If
      
      End Function
      

      Attach the above code to the AutoKey of your choice and it handles all situations except the closing of the Switchboard manually using the X (close box). To handle that go into the code of the Switchboard in the VBE and modify the Form_Unload event as follows:

      Code:
      Private Sub Form_Unload(Cancel As Integer)
         DoCmd.SelectObject acTable, , True
      End Sub
      

      You should now have all the bases covered. I hope 😆 :cheers:

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

      • #1327032

        Great job! I now have all your macros working just fine. Thanks for you help

    Viewing 8 reply threads
    Reply To: Modifying Access 2007 Switchboard form

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: