Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Montreal, Quebec, Canada
    Posts
    209
    Thanks
    12
    Thanked 1 Time in 1 Post

    Modifying Access 2007 Switchboard form

    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?
    David P - The Truth Is In Redmond

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,194
    Thanks
    201
    Thanked 784 Times in 718 Posts
    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.
    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
    Last edited by RetiredGeek; 2012-03-30 at 18:10. Reason: Correct Code
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  4. The Following User Says Thank You to RetiredGeek For This Useful Post:

    dpollock (2012-03-30)

  5. #3
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Montreal, Quebec, Canada
    Posts
    209
    Thanks
    12
    Thanked 1 Time in 1 Post
    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??
    David P - The Truth Is In Redmond

  6. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,194
    Thanks
    201
    Thanked 784 Times in 718 Posts
    David,

    You are correct. Make sure you create a new module to paste the code into.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  7. #5
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Montreal, Quebec, Canada
    Posts
    209
    Thanks
    12
    Thanked 1 Time in 1 Post
    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!
    David P - The Truth Is In Redmond

  8. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,194
    Thanks
    201
    Thanked 784 Times in 718 Posts
    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:
    AccessError.PNG
    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.

    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.
    Last edited by RetiredGeek; 2012-03-30 at 12:31.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  9. #7
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Montreal, Quebec, Canada
    Posts
    209
    Thanks
    12
    Thanked 1 Time in 1 Post
    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
    David P - The Truth Is In Redmond

  10. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,194
    Thanks
    201
    Thanked 784 Times in 718 Posts
    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.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  11. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,194
    Thanks
    201
    Thanked 784 Times in 718 Posts
    David,

    Just put Public in front of Sub, e.g. Public Sub routinename()
    FYI: I've adjusted the original code post to reflect this.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  12. #10
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Montreal, Quebec, Canada
    Posts
    209
    Thanks
    12
    Thanked 1 Time in 1 Post
    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
    David P - The Truth Is In Redmond

  13. #11
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by RetiredGeek View Post
    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?)

    autokeysmacro.gif
    Last edited by johnhutchison; 2012-03-30 at 15:21.
    Regards
    John



  14. #12
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,287
    Thanks
    130
    Thanked 1,154 Times in 1,063 Posts
    Sorry to intervene, but what's wrong with adding an event procedure to the On Click event of a button and calling the sub?

  15. #13
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,194
    Thanks
    201
    Thanked 784 Times in 718 Posts
    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.

    Switchboard Setup:
    SwitchboardSetup.PNG
    Switchboard View:
    SwitchboardView.PNG
    AutoKeys Setup:
    AutoKeys.PNG
    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.
    Last edited by RetiredGeek; 2012-03-30 at 18:28.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  16. #14
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Montreal, Quebec, Canada
    Posts
    209
    Thanks
    12
    Thanked 1 Time in 1 Post
    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
    David P - The Truth Is In Redmond

  17. #15
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,194
    Thanks
    201
    Thanked 784 Times in 718 Posts
    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
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •