Is it possible to prevent MS Access from changing

Question

When a custom UI XML file is used to add several custom ribbon tabs in Access, the selected ribbon tab changes back to the first custom tab whenever a form is closed.

We load a custom ribbon programatically from VBA. I've create an accdb that reproduces the problem. The folder also includes an XML file that contains the ribbon definition. It must be in the same directory as the .accdb file.

The problem can easily be demonstrated:

1. open the database RibbonTest.accdb,
2. switch to Tab2 and open Form2 using the button on the ribbon and
3. close Form2.

Notice that Tab1 is now active.

Of course, in this small example db this problem seems very minor. However, we have a very large project with many custom tabs, each containing numerous groups and buttons. Our users are finding it very frustrating indeed that they keep losing their place on the ribbon every time they close a form.

We have investigated a workaround where we programatically store the selected tab and restore it when we think we need to. However, it is proving difficult to do this reliably. (There isn't an Office API for automating the ribbon like this, but this article helped.)

Has anyone else encountered this problem? Have you found a way to prevent the tab from changing automatically?

Edit: It seems that this problem was introduced with a fix implemented in Office 2010 SP1 . (Sorry, no link: don't think I can have more than two.) The problem is not present in the RTM version. The fix list for SP1 includes this: "Access does not activate or return the user to the correct Ribbon tab for a previously opened database object when the user returns to that object." It seems that they've tried to fix use of the Form.RibbonName property (which supports contextual ribbons), but have broken the default ribbon in the process.

Answer 1

Potential Workaround

Something I stumbled across that's been working for me is to hide the first tab in the XML using the visible tag. I haven't tested it much, but I have a copy of the standard Home tab that is hidden (no idea if it needs to be a populated tab or not). It appears to me that since Access can't actually activate the hidden tab when you close a form, it remains on the currently selected one.

I don't know if this was fixed in Access 2013 or not, but hopefully the info isn't too late to be of use to someone.

Answer 2

Actually one approach that works well is to reduce or eliminate the tabs in the forms you open. If you specify the ribbon (other tab in properties sheet), then when you switch between forms the ribbon displayed switches for you automatic and without code.

Now while this is not really a solution to your problem, the idea and concept here is that if you have to start writing a bunch of code to engage and switch to what tab then this really becomes difficult as you point out.

As noted for Access/office 2010 you can use code to set the active tab (this feature is not available in Access/office 2007).

So about the only suggest I have here is trying and limit most forms to one tab when possible. The other tip is that while in the menus might be grouped by "type of task" and thus you might have a menu that cascades down to a whole bunch of reports. Now with the ribbon when working on a invoice, then you have:

Create invoice
Balance invoice
Post invoice
Print invoice (a report).

So all of the above options are different things but are group in one ribbon to allow you to get one job done.

So the idea here is to group ribbon options not by type of option such as all reports, but group + create a ribbon based on doing ONE task that includes the options that the user requires for given task at the given time.

As noted, the above may not be a solution to your issue(s), but avoiding additional tabs often will solve a lot of these issues.

Answer 3

It's A Bug!

MS support has accepted a bug submission for this, and commented regarding Office 2010 SP1, "The change that was implemented allows us to track the active tab for each database object (forms, reports, etc) using the tab’s TCID so that as you move between objects the active tab is restored. However custom tabs all use the same TCID value, so with this change the active tab for custom tabs will always move to the first custom tab."

We hope that they will release a hotfix to resolve this in the future.

Workarounds

The following information has proved useful for us in creating a workaround.

1. See the answer from Johanness above regarding the IRibbonUI.ActivateTab method. This was introduced in Office 2010.
2. There is no Office API (AFAIK) for getting the currently selected tab. Therefore we use code from this article helpful. We
   • create an array when we generate our ribbon containing the id value of each ribbon tab,
   • handle Form_Deactivate and use it to start a timer in another hidden form and also store the index of the selected tab,
   • in the Timer_Tick handler in the hidden form we disable the timer and look up the id value of the tab whose index we stored in Form_Deactivate, and
   • activate the tab using IRibbonUI.ActivateTab.
3. This article shows an interesting use of IRibbonUI.Invalidate and the getVisible callback to select a particular tab.

Answer 4

Create an enumerator variable in a general code module corresponding to your ribbon tab numbers (ALT+Y)

' Used By Send Keys to Select Correct Ribbon Tab.
Enum eRibTabs
    DataEntry = 1
    Reporting = 2
    StockAndParts = 3
    AdminFinance = 4
    DataImport = 5
    OtherAdmin = 6
    Admin = 7
    LocalSystem = 8
End Enum

Create a form in ms Access called "zFrmRibbonSelect" and put an unbound text box called txtTabValue and then put the following code in your new form. (Suggest set form mode to hidden.

Private Sub Form_Current()
' Select Correct Tab Menu Item
Me.txtTabValue = Me.OpenArgs

End Sub

Private Sub Form_Close()
'Select Correct Tab Menu Item
Dim varTab As Variant

varTab = Me.txtTabValue

SendKeys "%Y" & varTab
SendKeys "{ESC}"
SendKeys "{ESC}"

End Sub

Private Sub Form_Timer()
DoCmd.Close acForm, Me.Name, acSaveNo
End Sub

Set form timer interval to 500 and check on event for timer shows in property box etc.

On your report put the following code: (use the enumerator value you want.)

Private Sub Report_Close()
'Select Correct Tab Menu Item
 DoCmd.OpenForm "zFrmRibbonSelect", , , , , acHidden, eRibTabs.StockAndParts
End Sub

For Forms that close use the following code int the form

Private Sub Form_Close()
'Select Correct Tab Menu Item
    SendKeys "%Y" & eRibTabs.StockAndParts
    SendKeys "{ESC}"
    SendKeys "{ESC}"
End Sub

Answer 5

This one line fixes the issue:

<tab id="tabBogus" label="Bogus" visible="false"></tab>

Just make it your first tab in <tabs>. Big thanks to Scott's Potential Workaround answer!! (Tried to Vote it up and/or comment, but just signed up so not enough reputation.) This saved hours (or days) of work versus the other complicated workaround! Thanks!

Answer 6

There seems to be a way to get the selected tab (as you mentioned and probably already have the code for, and as you can find here)

In the RibbonCode module: Save the ribbonObject to a module-variable: in the xml change the first line:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onload="OnRibbonLoad" >

and add this:

Private MyRibbon as IRibbonUI
Private ActiveRibbonTab as string

Sub OnRibbonLoad(ribbon As IRibbonUI)
  Set MyRibbon = ribbon
End Sub

Sub RememberRibbonTab
  ActiveRibbonTab=<Do the IAccessibleMagic here>   
End

Sub RecallActiveTab
  If ActiveRibbonTab<>"" then MyRibbon.ActivateTab(ActiveRibbonTab)
  ActiveRibbonTab=""
End

Now in every form add

Private Sub Form_Close()
  Remember_RibbonTab
End Sub

Private Sub Form_GotFocus()
  RecallActiveTab
End Sub