Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2008
    Location
    All over, Now in Missouri
    Posts
    73

    Unanswered: Access 2007 Ribbon Control

    We upgraded to Access 2007, so now I am working out one issue at a time.

    - In 2007 the ribbon shows which could cause users to hit "VIEW" then they have ability to see the Design Mode.

    - Can I shut off the ribbon - users don't need it because I control all formatting/controls.

    Peter
    We tend to look at Linear paths which can lead us to a path of resistance!

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Sorry, but LOL. Microsoft really have made doing that way too hard.

    BTW, you should not have down.... erm... upgraded to 2007.

    It's far easier to just make an ACCDE file to protect your designs rather than hiding or customising the ribbon.

    Other than that, do a google search for "hiding the ribbon microsoft access 2007" and you should find some help.

    It involves:

    * Creating a table with a special name. USysRibbons I think it's called. You might like to add that term to your google search.
    * Adding specific fields. RibbonName and RibbonXML or something...
    * Adding some data to that table which contains XML code to define the ribbon.
    * Restarting Access.
    * Modifying Access properties (Access Options) to specify the RibbonName as the ribbon to show.

    Give it a shot and post back if you get stuck.

    Or just make an ACCDE
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    May 2005
    Posts
    1,191
    Here's step by step of what Star was talking about. A quick Google Search got me there in a couple of seconds.

    Just a note, I've ended up using MicheleGo's code (scroll down towards the bottom of the page, the second comment).
    Me.Geek = True

  4. #4
    Join Date
    Apr 2008
    Location
    All over, Now in Missouri
    Posts
    73
    Did not have any choice on the upgraded, they pushed it through during the night. Anyways, I will check out Google to overcome this new issue.

    They should have followed 2003 and gave you startup options on what you want to show (menus/tables etc..)!
    We tend to look at Linear paths which can lead us to a path of resistance!

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    They should have followed 2003 and gave you startup options on what you want to show (menus/tables etc..)!
    Agree with you there. I still don't like the 2007 interface and I've been using it for a while now.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    They should have followed 2003 and gave you startup options on what you want to show (menus/tables etc.)!
    I may be missing something here as I have only "played" with Office 2007 to see what I need to do to make my current user interface design(s) work and be maintained, but I think the startup options can be found by clicking the Office Button -> Access Options -> and selecting Current Database from the list ??

    Also, you can add command/toolbars and button programatically, they then appear in an 'Add In' ribbon (also in Excel). This is also true of point and click tool bars designed in A2k3 or earlier, but, as we know, you cannot maintain them by point and click in A2k7 (hurray for microsoft)!!

    Don't know if this helps.

    MTB

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Also, you can add command/toolbars and button programatically, they then appear in an 'Add In' ribbon
    But to place, build and manage the toolbars and custom menus, you have to use a version of Access prior to 2007 as 2007 just does not have it. It can see the custom menus and toolbars, but it has no abilities to manage them.

    Try using Access 2007 alone, create a new database and try to make a custom button, with a custom image on it, with your own words that calls up your own code. You will then agree that 2007 is lame.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi again
    Quote Originally Posted by StarTrekker
    But to place, build and manage the toolbars and custom menus, you have to use a version of Access prior to 2007 as 2007 just does not have it. It can see the custom menus and toolbars, but it has no abilities to manage them.

    Try using Access 2007 alone, create a new database and try to make a custom button, with a custom image on it, with your own words that calls up your own code. You will then agree that 2007 is lame.
    I must admit I had not created an .accdb file before so I didn't know if this would work in a native A2k7 file.

    So, first think this morning I jump on an Office 2007 PC and created a new .accdb file, pasted in the toolbar code in a module, set a reference to the Office library (forgot mention that before), ran if from an Startup form load event and, a hey presto!, the toolbars appeared in a the Addin Ribbon.

    This also creates shortcut/popup menus for use/attaching to textbox controls etc., including built in commands with standard images (I never used custom bit maps!)

    I'm not sure if that is what you mean or would want!?

    It would seem there are also one or two things that need to be changed in the 'Current Database' options to make startup form look like a 'moveable' (this apparently is a new property!?) form as in A2k3 !!


    MTB

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I missed the word "programatically". I meant try doing that without code.

    BTW, would you mind sharing that code?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  10. #10
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Quote Originally Posted by StarTrekker
    BTW, would you mind sharing that code?
    Hi StarTrekker

    I have posted my code for you (and any one else are interested) is a vey cut down examlpe of what I have come up witrh (actally I use sub procedured for each section but that does not illustate the required objects/properies too well!). My only reservation is will MicroSoft stop supporting CommandBars some time in the future!?
    Code:
    Option Compare Database
    Option Explicit
    
    Public Const cMenuBarName As String = "Information Menu"
        
    Sub AddMenus()
    On Error GoTo Bar_Exists
        Dim aMenuBar As CommandBar
        Dim Ctrl As CommandBarControl
        Dim SubCtrl As CommandBarControl
        Dim aBtn As CommandBarButton
        
        'ADD TOP MENU BAR
        Set aMenuBar = CommandBars.Add(Name:=cMenuBarName, Position:=msoBarTop, MenuBar:=False, Temporary:=True)
        With aMenuBar
            .Visible = True
            .Left = 0
            .Protection = msoBarNoChangeDock + msoBarNoMove + msoBarNoChangeVisible
            
            'ADD EMPLOYEE CONTROL
            Set Ctrl = aMenuBar.Controls.Add(Type:=msoControlPopup, Temporary:=True)
            With Ctrl
                .Caption = "&Employees"
                .Tag = "Employees"
                .BeginGroup = False
                .TooltipText = "Information by Employee"
            
                'ADD BUTTONS TO EMPLOYEE CONTROLS
                Set aBtn = .CommandBar.Controls.Add(Type:=msoControlButton, Temporary:=True)
                With aBtn
                    .Caption = "&Employee List"
                    .Tag = "EmpList"
                    .OnAction = "OpenForm"
                    .Style = msoButtonCaption
                    .Parameter = "frmEmployees"
                    .DescriptionText = CStr(acNormal)
                    .BeginGroup = False
                    .Enabled = True
                    .Visible = True
                End With
                
                Set aBtn = .CommandBar.Controls.Add(Type:=msoControlButton, Temporary:=True)
                With aBtn
                    .Caption = "Employee &Course List"
                    .Tag = "EmpCourseList"
                    .OnAction = "OpenForm"
                    .Style = msoButtonCaption
                    .Parameter = "frmEmployeeCourseList"
                    .DescriptionText = CStr(acNormal)
                    .BeginGroup = True
                    .Enabled = True
                    .Visible = True
                End With
                
                Set aBtn = .CommandBar.Controls.Add(Type:=msoControlButton, Temporary:=True)
                With aBtn
                    .Caption = "Employee Certi&fications"
                    .Tag = "EmpCertsList"
                    .OnAction = "OpenForm"
                    .Style = msoButtonCaption
                    .Parameter = "frmEmployeeCertifications"
                    .DescriptionText = CStr(acNormal)
                    .BeginGroup = False
                    .Enabled = True
                    .Visible = True
                End With
                
                Set aBtn = .CommandBar.Controls.Add(Type:=msoControlButton, Temporary:=True)
                With aBtn
                    .Caption = "Employee &Qualifications"
                    .Tag = "EmpCertsList"
                    .OnAction = "OpenForm"
                    .Style = msoButtonCaption
                    .Parameter = "frmEmployeeQualifications"
                    .DescriptionText = CStr(acNormal)
                    .BeginGroup = False
                    .Enabled = True
                    .Visible = True
                End With
                
            End With
            
            'ADD MISCELLANEOUS CONTROL
            Set Ctrl = aMenuBar.Controls.Add(Type:=msoControlPopup, Temporary:=True)
            With Ctrl
                .Caption = "&Miscellaneous Info"
                .Tag = "Miscell"
                .BeginGroup = True
                .TooltipText = "Office/Trade Info/Accidents etc."
                
                'ADD BUTTONS TO MISCELANEOUS CONTROLS
                Set aBtn = .CommandBar.Controls.Add(Type:=msoControlButton, Temporary:=True)
                With aBtn
                    .Caption = "CES &Miscellaneous Data"
                    .Tag = "CESData"
                    .OnAction = "OpenForm"
                    .Style = msoButtonCaption
                    .Parameter = "frmCESData"
                    .DescriptionText = CStr(acNormal)
                    .BeginGroup = False
                    .Enabled = True
                    .Visible = True
                End With
                
                Set aBtn = .CommandBar.Controls.Add(Type:=msoControlButton, Temporary:=True)
                With aBtn
                    .Caption = "&Offices Data"
                    .Tag = "Offices"
                    .OnAction = "OpenForm"
                    .Style = msoButtonCaption
                    .Parameter = "frmOffices"
                    .DescriptionText = CStr(acNormal)
                    .BeginGroup = False
                    .Enabled = True
                    .Visible = True
                End With
                
                'ADD RENEWABLE ITEMS SUB-CONTROL
                Set SubCtrl = .CommandBar.Controls.Add(Type:=msoControlPopup, Temporary:=True)
                With SubCtrl
                    .Caption = "&Renewable Items"
                    .Tag = "Renewal"
                    .BeginGroup = True
                    .Enabled = True
                                    
                    'ADD BUTTONS TO SUB-CONTROL
                    Set aBtn = .CommandBar.Controls.Add(Type:=msoControlButton, Temporary:=True)
                    With aBtn
                        .Caption = "&All Items"
                        .Tag = "RenewItems"
                        .OnAction = "OpenForm"
                        .Style = msoButtonCaption
                        .Parameter = "frmRenewableItems"
                        .DescriptionText = CStr(acFormDS)
                        .BeginGroup = False
                        .Enabled = True
                        .Visible = True
                    End With
                    
                    Set aBtn = .CommandBar.Controls.Add(Type:=msoControlButton, Temporary:=True)
                    With aBtn
                        .Caption = "&Due Items"
                        .Tag = "DueItems"
                        .OnAction = "OpenForm"
                        .Style = msoButtonCaption
                        .Parameter = "frmDueItems"
                        .DescriptionText = CStr(acFormDS)
                        .BeginGroup = False
                        .Enabled = True
                        .Visible = True
                    End With
                End With
                
                'CONTINUE ADDING BUTTON TO MAIN COTROL
                Set aBtn = .CommandBar.Controls.Add(Type:=msoControlButton, Temporary:=True)
                With aBtn
                    .Caption = "&SMS Manual Allocation"
                    .Tag = "SMSMan"
                    .OnAction = "OpenForm"
                    .Style = msoButtonCaption
                    .Parameter = "frmSMSManualAllocation"
                    .DescriptionText = CStr(acNormal)
                    .BeginGroup = True
                    .Enabled = True
                    .Visible = True
                End With
                
            End With
        End With
    
        
    Exit Sub
    
    Bar_Exists:
    
        If Err.Number = 5 Then
            'COMMAND BAR ALREADY EXISTS!
        Else
            MsgBox Err.Number & vbLf & Err.Description, vbCritical, "Creating Toolbars"
        End If
    End Sub
    I have used the Parameter and DescriptionText button propertied to pass the form name and view type parameter to the OnAction procedure using Application.CommandBars.ActionControl.Parameter etc.

    For the Short cut menus I use this
    Code:
    Sub CreateShortCutMenus()
        Dim aBtn As CommandBarButton
        Dim aCtrl As CommandBarControl
        Dim aCmdBar As CommandBar
        On Error Resume Next
    
        Set aCmdBar = CommandBars.Add(Position:=msoBarPopup, Temporary:=True, Name:="Sort Filter")
        
        With aCmdBar
    
            Set aBtn = .Controls.Add(Type:=msoControlButton, ID:=210, Temporary:=True)
            Set aBtn = .Controls.Add(Type:=msoControlButton, ID:=211, Temporary:=True)
            Set aBtn = .Controls.Add(Type:=msoControlButton, ID:=640, Temporary:=True)
            aBtn.BeginGroup = True
            Set aBtn = .Controls.Add(Type:=msoControlButton, ID:=3017, Temporary:=True)
            Set aBtn = .Controls.Add(Type:=msoControlEdit, ID:=2863, Temporary:=True)
            
            Set aBtn = .Controls.Add(Type:=msoControlButton, ID:=10077, Temporary:=True)
            Set aBtn = .Controls.Add(Type:=msoControlButton, ID:=10080, Temporary:=True)
            
            Set aBtn = .Controls.Add(Type:=msoControlButton, ID:=605, Temporary:=True)
        End With
        
            End With
            
        Exit Sub
    ErrorHandler:
        If Err.Number <> 5 Then
            MsgBox Err.Number & vbLf & _
                   Err.Description, vbCritical, "Loading Shortcut(s)"
        End If
    End Sub
    This uses built in buttons/control for which you need the ID. This can be obtained by running the enumeration code (I would post mine but I have reached the 10000 chatacture limit!!).

    Control button with the ID=2863 (Filter For: ) does not exist in A2k7 and is ignored without error. This is replaced by numerous search option to of which I have included (ID=1077 and 10080, which are ignored in A2k3 without error).

    I also believe you can assign user defined bitmaps images to a button with the Picture propery (according to the help file).

    Hope that helps anyone that's interested (if you haven't fallen asleep by now!).

    Just one question, does any one now how to get rid oft he 'Home' Ribbon ??


    MTB

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Awesome, thanks for sharing that

    You cannot get rid of the ribbon entirely. Nor the Office button. That's one of the weaknesses of 2007. If you prefer toolbars and menus, as almost everyone I have spoken to does, sorry, but too bad. You're stuck with the ribbon. The best you can do is customise it with XML code.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  12. #12
    Join Date
    Jan 2008
    Location
    Denver, CO
    Posts
    54

    Lightbulb getting rid of the home tab entirely

    I don't fancy myself much of an expert on Access and when it comes to custom ribbons I'm even more of a noob but in terms of hiding the home tab on the default ribbon, in the custom xml can't you use:

    Code:
    <tab idMso="TabHomeAccesstab" visible="false" />
    or to get rid of all of the default ribbons start the xml off after the "<customUI xmlns="http://schemas.microsoft.com /office/2006/01/customui"> ") with:

    Code:
    <ribbon startFromScratch="true">
    I think that gets rid of the home tab though this is aircode... haven't bothered testing it.

    Incidentally, you can download a list of office control ids from this link, and read more about costomizing the fluent interface here.

  13. #13
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yes, just as I said, customise it with XML.

    There's much that can be done, but no easy ways to find out how. Good luck if you want to add your own custom command buttons with your own custom images.

    Customising toolbars and menus in Access 2003 was soooo clean and easy by comparison.

    I find it truly amazing that everyone seems happy to pay Microsoft for an upgrade to 2007 when Access 2007 is a HUGE step backwards for the developer. Software is supposed to get better and easier over time, not harder and less flexible
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  14. #14
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Quote Originally Posted by StarTrekker
    I find it truly amazing that everyone seems happy to pay Microsoft for an upgrade to 2007 when Access 2007 is a HUGE step backwards for the developer. Software is supposed to get better and easier over time, not harder and less flexible
    I don't need to say anything else !!

    MTB

Posting Permissions

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