Modifying Access 2007 Switchboard form

Topic

New Reply

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?

Reply | Quote

Replies

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

Reply | Quote

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??

Reply | Quote

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

Reply | Quote

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!

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote