Ribbon to Show open objects in Access 2007 (1 Viewer)

Insane_ai

Not Really an A.I.
Local time
Today, 11:25
Joined
Mar 20, 2009
Messages
264
I have been looking for a way to build a dynamic menu item on a ribbon that shows whatever happens to be open a the time to make it easier for the users to switch between open forms and reports. I found the following that gets the job done with a few additions / considerations:

The majority of the code attached is sourced from this blog entry.

http://blogs.office.com/b/microsoft...namicmenu-to-show-a-list-of-open-objects.aspx


I had a few problems with this. I expected an article like this to include everything needed to get the thing working but alas, it did not. I am posting my solutions in case someone else can find them useful.

1. It clearly states: "Remember that you'll need a reference to the Microsoft Office 12.0 Object Library to compile this code."

What it fails to mention is that this library is loaded by default but the code still will not compile until you add it again. (Offenders include: IRibbonControl, and any other reference to a Ribbon) Yes, this sounds stupid but the reference was checked in my application and the code would not compile until I added a second reference to it. (Tools | References in the VBA window)

2. The code refers to a function / subroutine that is not explained:
BuildAttribute("onAction", "OnOpenObject") & "/>"

Since I have never programmed a dynamic menu before today, I had no idea how to work with it and respond to the user's click. After all, what good is a list of open objects if the user can't do anything with it?

My solution is a bit long winded and includes Object types that probably will never be accessed directly by my users but to provide a complete reference, I dealt with all the objects I could define:


Public Sub OnOpenObject(Control As IRibbonControl)
Dim strType As String
Dim strName As String
strType = Right(Control.tag, Len(Control.tag) - (InStr(Control.tag, "|")))
strName = Left(Control.tag, (InStr(Control.tag, "|")) - 1)

Select Case strType
Case 0: 'Do nothing. The object is a table and should not be open to the user
Case 1: DoCmd.OpenQuery strName
Case 2: DoCmd.OpenForm strName
Case 3: DoCmd.OpenReport strName
Case 4: 'Do nothing. The object is a Macro and should not be open to the user
Case 5: 'Do nothing. The object is a Module and should not be open to the user
'There is no Type 6 to consider See http://msdn.microsoft.com/en-us/library/bb225926(v=office.12).aspx (this is my source for the list of object types)
Case 7: 'Do nothing. The object is a Server View and should not be open to the user
Case 8: DoCmd.OpenDiagram strName
Case 9: 'Do nothing. The object is a Stored Procedure and should not be open to the user
Case 10: 'Do nothing. The object is a Function and should not be open to the user
Case -1: 'Do nothing. The object is a Default Object.

End Select


End Sub


I did try to work through some setfocus commands with no success. If any of you can improve on this, please do.


I exported the module for this function as a text file and added the XML for the ribbon as reference. (RibbonFunctions.txt)
 

Attachments

  • RibbonFunctions.txt
    4.2 KB · Views: 233

Insane_ai

Not Really an A.I.
Local time
Today, 11:25
Joined
Mar 20, 2009
Messages
264
Improvement made to OnOpenObject. This new method allows you to open the object in its current state rather than opening it to a default state.


Code:
Public Sub OnOpenObject(Control As IRibbonControl)
Dim strType As String
Dim strName As String
    strType = Right(Control.tag, Len(Control.tag) - (InStr(Control.tag, "|")))
    strName = Left(Control.tag, (InStr(Control.tag, "|")) - 1)
    
    
    Select Case strType
    Case 0: DoCmd.SelectObject acTable, strName, False
    Case 1: DoCmd.SelectObject acQuery, strName, False
    Case 2: DoCmd.SelectObject acForm, strName, False
    Case 3: DoCmd.SelectObject acReport, strName, False
    Case 4: DoCmd.SelectObject acMacro, strName, False
    Case 5: DoCmd.SelectObject acModule, strName, False
    'There is no Type 6 to consider  See [URL]http://msdn.microsoft.com/en-us/library/bb225926(v=office.12).aspx[/URL]
    Case 7: DoCmd.SelectObject acServerView, strName, False
    Case 8: DoCmd.SelectObject acDiagram, strName, False
    Case 9: DoCmd.SelectObject acStoredProcedure, strName, False
    Case 10: DoCmd.SelectObject acFunction, strName, False
    Case -1: DoCmd.SelectObject acDefault, strName, False
    End Select
    
    
End Sub
 

Users who are viewing this thread

Top Bottom