ACCESS 2010: Feasibility of Using a Command Button to Open a Combo List of Subforms??

[savargo]

I have a switchboard that is running out of room. I've decided that it would be prudent if I could free up some space. Would I be able to create a Command Button on the switchboard that would open a Combo Box with a list of subforms (that are presently on the switchboard) that I could select to open and how could I do this??? If you think my strategy is off base, how would you approach the issue?? Please be aware that I am not very tech savvy. I inherited a database from a very disgruntled programmer who is not helpful at all. Thanks in advance for your input...

[June7]

What do you mean by 'switchboard'. Is this a form created by the old switchboard wizard and uses macros? That type of form object doesn't usually have subforms. So why are you running out of room? Why are subforms on 'switchboard'? Could the Tab control be useful to consolidate your form?

[savargo]

To try and answer your questions:

1). The "Switchboard" is a form that contains all the command buttons to open various tables, reports and forms in the database. I don't know whether it was created by the old switchboard wizard or was customized. However it was originally created with ACCESS 2000.

2). The "Switchboard" may not have subforms as I previously described. However it contains Command Buttons on the switchboard which open these forms.

What I am looking to do is to place one Command Button on the switchboard which would open to a list of forms. I could then select a form to open it up. Right now, these forms are individually opened by pressing its own individual command button on the switchboard. So I have a Command Button on the switchboard to open each form --- that is, for each form, I have one command button --- that is what is crowding my switchboard...

[June7]

Is there a SwitchboardItems table? The buttons use embedded macros? If so then this is the old Switchboard form object. Don't like and never used. I don't use macros, only VBA.

The button you want could probably be built but I find the Switchboard construct harder to understand and edit. Means adding another record in the table.

Still doesn't explain why Switchboard form would be 'running out of room'.

If you want to provide db for analysis, follow instructions at bottom of my post.

[TenOc]

The "old" switchboard can open up a sub-switchboard, but I forgot how. I have an old database 1997 (?) the the main switchboard has the options to "go to input forms" and " go to reports" each is a sub-switchboard.

[darshit_goswami]

But why do u want to stick to switchboard itself.?

Design a form with all required action buttons. And open it from switchboard . Well you can call it a sub switchboard if u want to. Disable record navigator and record selectors. And still not happy with design then make it as dialog or popup

[Missinglinq]

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

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:

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:

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:

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:

SELECT [Name] FROM MsysObjects
WHERE (([Type] = 5) AND ([Name] Not Like "~*") AND ([Name] Not Like "MSys*"))
ORDER BY [Name];

For listing Reports:

SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") And (MSysObjects.Type)=-32764 ORDER BY MSysObjects.Name;