Results 1 to 7 of 7
  1. #1
    savargo is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2015
    Posts
    2

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

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

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    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?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    savargo is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2015
    Posts
    2
    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...

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    TenOc is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Feb 2015
    Posts
    94
    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.

  6. #6
    darshit_goswami is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Posts
    29
    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

  7. #7
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    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

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 05-01-2014, 01:01 PM
  2. Replies: 0
    Last Post: 02-17-2013, 11:17 PM
  3. Command button open url
    By patrickmcdiver in forum Programming
    Replies: 2
    Last Post: 03-06-2012, 11:08 AM
  4. Access 2010 Forms Command button Question
    By RayMilhon in forum Forms
    Replies: 3
    Last Post: 09-12-2011, 03:38 PM
  5. Use a command button to open a form
    By johnpaul in forum Forms
    Replies: 24
    Last Post: 09-23-2010, 12:29 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums