As has been suggested, I would replace the switchboard form with a simple custom form. You can then use Comboboxes to access the various Forms, Reports, Queries. Here's a short tutorial that may give you some guidance; I wrote this a long time ago, but I think it's all still pertinent:
Place a Combobox on your Form
When the Wizard pops up, hit Cancel
Go to the Combobox Property Sheet and make sure under Row Source Type it says Table/Query
Then, for Forms, in the Row Source property paste this in
Code:
SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND
(MSysObjects.Type)= 32768 ORDER BY MSysObjects.Name;
Now, in the Combobox’s AfterUpdate event, you can do one of two things, depending on your particular situation.
If none of your forms need to be opened in Datasheet View, you can simply use:
Code:
Private Sub FormOpenCombo_AfterUpdate()
stDocName = Me.FormOpenCombo
DoCmd.OpenForm stDocName
End Sub
If all of your Forms need to opened in Datasheet View, you can use:
Code:
Private Sub FormOpenCombo_AfterUpdate()
stDocName = Me.FormOpenCombo
DoCmd.OpenForm stDocName, acFormDS
End Sub
If you have a mixed bag, some forms in Single View or Continuous View and some in Datasheet View, it becomes a little more complicated. The line
DoCmd.OpenForm FormName
will open a form defined as Single View or Continuous View in the defined mode, but will open a Form defined as Datasheet View as a Single View Form.
If you want a Form called from another form to open in Datasheet View, you have to explicitly call it as a Datasheet Form. The command must read
DoCmd.OpenForm FormName, acFormDS
So if you need to display all types, Datasheet and non-Datasheet forms, and you want this to be flexible, i.e. you don’t want to have to go back in and modify your code each time you add a new form, you’ll have to have two buttons to open the Forms, depending on how you want them displayed. You’ll still set your Combobox up the same way, but you won’t have code in the FormOpenCombo_AfterUpdate() event. Instead, you’ll have two buttons, with code like this:
Code:
Private Sub OpenFormInDatasheetView_Click()
If Not IsNull(Me.FormOpenCombo) Then
stDocName = Me.FormOpenCombo
DoCmd.OpenForm stDocName, acFormDS
Else
MsgBox "You Must Select a Form To Open"
End If
End Sub
Private Sub OpenFormNonDatasheetView_Click()
If Not IsNull(Me.FormOpenCombo) Then
stDocName = Me.FormOpenCombo
DoCmd.OpenForm stDocName
Else
MsgBox "You Must Select a Form To Open"
End If
End Sub
Now the two buttons will determine how the forms are opened. This gives you the added advantage of being able to open any form for a quick peek in Datasheet View.
For listing Queries:
Code:
SELECT [Name] FROM MsysObjects
WHERE (([Type] = 5) AND ([Name] Not Like "~*") AND ([Name] Not Like "MSys*"))
ORDER BY [Name];
For listing Reports:
Code:
SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") And (MSysObjects.Type)=-32764 ORDER BY MSysObjects.Name;
The problem with making anything foolproof...is that fools are so darn ingenious!
All posts/responses based on Access 2003/2007