Prev: Conditional Formatting for Image box on Continuous Forms - Again
Next: Access 2007 Runtime Problems
From: Guy on 23 May 2007 01:39
Hi All,

I am upgrading a large .ADP application from Office 2003 to Office 2007 and
would like some feedback on the best way to manage Ribbons in Access.

I am in the process of converting all the old custom menubar/toolbar
functionality into equivalent/similar/improved functionality using the Ribbon

Currently I have over 20 Ribbons (and growing) all use the "Start from
Scratch=True" attribute. Obviously I would prefer to utilise less Ribbons,
but by the same token the prospect of dynamically applying lots of
customisation to fewer Ribbons when each form or report is opened appears
equally onerous. Needless to say I am still learning about how to best manage
the Ribbon and hence the reason for this posting

The biggest problems I currently have are simple issues such as
enabling/disabling controls on the Ribbon dynamically according to actions a
user might take on any given form.

To force a ribbon control refresh requires you to "Invalidate" that control
on the ribbon, however, this requires a reference to the ribbon and I have
over 20 of them.

I have tried storing the references in a collection using the Ribbon
"OnLoad" callback:

Public mcolRibbon As New Collection

Public Function OnLoad(ByVal Ribbon As IRibbonUI)
mcolRibbon.Add Ribbon, Screen.ActiveForm.RibbonName
End Function

However when I try to retrieve the reference perhaps in the Form_Open event

Dim objRibbon as IRibbonUI
Set objRibbon = GetRibbon(me.RibbonName)

Public Function GetRibbon(strRibbonName as String) As IRibbonUI
GetRibbon = mcolRibbon.Item(strRibbonName)
End Function

or invalidate the control from the from by calling:

Public Sub gsubInvalidateControl(strRibbonName As String, strControl As
String)
mcolRibbon.Item(strRibbonName).InvalidateControl (strControl)
End Sub

it fails.

There appear to be two issues:

1) If the Ribbon has not yet been loaded the open Form action causes the
Ribbon to Load, however, the Form_Open event occurs before the Ribbon_OnLoad
event meaning that a Null Reference is returned.

2) I don't use custom collections very often and I may have the syntax
incorrect, but I am also experiencing problems accessing the Ribbon object
from within the collection.

Can anyone assist in either resolving the issues I am having or suggest a
better approach.

Your Assistance Appreciated
Guy
From: Albert D. Kallal on 23 May 2007 09:33
Ah...

I like you idea of building a collection.

I really feel bad that I don't have more time to write up a nice article.

However, a few things:

The active screen is not set until AFTER both on-open , and then on-load ARE
COMPLETE.
(I know this, because I have for years use the on-open, or even the on-load
event of the form
to pick up the PREVIOUS form by going:

set frmPrevous = screen.ActiveForm.

So, that puts a bit of damper on your approach to pick up the active
screen...(you can't do that from on-load, or on-open events of a form...you
get the previous screen!!!

and, normally, a *great* trick is to place the public function in a form, as
menu items will call that first before the same function name in a public
module (but, again..the form is not yet consider loaded. -- DO NOTE that
once the ribbon is loaded, then routines declared public in your form that
HAS THE FOCUS will be called. This allows some really nice custom code for
each form, but the SAME ribbon to be used.

Anyway, you can't use screen.active.

2nd thing:

> mcolRibbon.Item(strRibbonName).InvalidateControl (strControl)

You do realize that invalidate method only causes the xml mark-up text to be
re-executed for that control. So, for setting the controls visible property,
you actually have to maintain that value in memory (in the old menu bars,
the menu control it self held the state of the control. This is NOT the case
with a ribbon. (it possible you realize this...as you not posted any xml
ribbon code)..

So, what this means is that you can hard code the mark-up, but that is of no
use. The solution is thus to use a function, such as:

<button id="button1" label="Buttion1"
getVisible="MyVisible"
onAction="=MyTest1()"/>

While we at this, it important to note that the mark-up text IS CASE
sensitive (I read 20+ articles about the ribbon, and NOT ONE has mentioned
this -- I just trying to save you some lessons that I learned!!!.

Note that we have a function called MyVisble. In theory, you need a
variable declared for EACH control that you plan to have visible, or not
visible.

The ONLY difference between using

mcoIRibbion.Invalide

and

mcoRibbion.InvalideCotnorl(strContlr)

Is that the 2nd example only runs the code for the ONE control. THIS IS THE
ONLY DIFFERENCE. In other words, the same piece code would get called in
both cases, but the InvalideContrll is better code because it will not call
each and every piece of code for each button on the ribbon.

So, keep in mind, the invalide call does not actually make the control
visible, or invisible...it is the setting for the function in the he call
back that determines this. and *further* keep in mind that the code called
will be the same both cases, but it better to use the Invalidecontrfll as
you not wind up having every control on the bar get called. (however, you
code MUST STILL work for that case).

eg:

Public Sub MyVisible(control As IRibbonControl, _
ByRef visible As Variant)

static m_buttion1 as boolean
static m_buttion2 as boolean
select case contorl.id

case "buttion1"

visible = m_buttion1

case "buttion2"

End Sub

In the above, "id" will be text string (buttion1 from above) in BOTH cases

eg:

mcoribbion.Invalide
or
mcoribbion.InvalideContorl("buttion1")

It is the *system* that provong the value for contorl.ID in the above. For a
small robbionk you could always use the Invalide, and never have to use the
InvalideContrl method.

So, what the above means is that you need a Boolean variable for each
control that I plan to turn off, and turn on to hold the state of that
control.

(or a separate "getVisible" function for each control that you plan to make
visible/non visible).

Without question, building a custom collection is a great idea. with a bit
of work you could near the same syntax of what we been using for years....

I have to run out the door right now. I *really* need to write an article on
this......


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal(a)msn.com


From: Guy on 23 May 2007 21:26
Hi Albert,

Thank you for your comprehensive response.

I wasn't aware that once the form was loaded/opened the forms public
functions would get called in preference to module public functions. This
could be useful so I will investigate this aspect further.

Ideally I would like to use just 3 Ribbons:

1) Home - Which allows quick access to key forms, reports and tools such as
database backup within the application and is shown only when the Main Menu
is active.

2) Form - Which shows for each form the "Home" button which takes you back
to the Main Menu and Home Ribbon and the standard Records/Filters/Cut and
Paste groups plus custom Command actions that are relevant to that form. So
for instance if it was the "Customer Invoice" form you could print the
Customer Invoice or if it was the "Budget" form you could export the budget
to Excel. Some forms would have many Command actions available.

3) Report - Which shows for each report the "Home" button plus the standard
print/export/send mail groups and so on. It is unlikely that many custom
Command actions would be required for this Ribbon.

Currently I have about 20 individual Form Ribbons, however, in your opinion
would it be better to have just 1 Form Ribbon and customise it appropriately
when each form becomes active?

Assuming that there are pros/cons to both the above approaches and that we
are happy to continue with the multiple Form Ribbon method. The next question
is maintaining references to each Ribbon so that we can hide/show or
enable/disable etc the Form Command actions.

As previously stated I have tried to maintain the Ribbon references in a
custom Collection which is loaded via the Ribbon "OnLoad" event. However, I
am having problems with:

1) Event timing
2) Populating and Accessing the reference in the Collection

If I can't use the Screen.ActiveForm mechanism to determine which Ribbon is
calling the "OnLoad" callback then how do I uniquely "Key" the collection,
the "Tag" XML attribute doesn't appear to be supported at the CustomUI level?

Is there no method by which the active form can refer to the active ribbon
without trying to access a stored reference passed by the "Onload" callback?

If I can populate the Collection then I also find that I am having problems
accessing the Ribbon IRibbonUI object from within the collection. It maybe
that I haven't populated the collection correctly or it maybe that I am not
using the incorrect syntax.

Like yourself I have searched Microsoft and the Web and looked at a number
of sample databases using RibbonX, but none of them tackle the issues of
multiple (more than one) Ribbon references. Which leads me to conclude that
perhaps 1 Ribbon and lots of customisation maybe the better method, although
onerous.

Yes I can think of solutions without using a collection such as a unique
Onload callback for each Ribbon along with a separate variable to maintain
each reference, but this
feels like a "kludge" option.

Are you able to assist with any further thoughts or code samples.

Your assistance appreciated

Guy



"Guy" wrote:

> Hi All,
>
> I am upgrading a large .ADP application from Office 2003 to Office 2007 and
> would like some feedback on the best way to manage Ribbons in Access.
>
> I am in the process of converting all the old custom menubar/toolbar
> functionality into equivalent/similar/improved functionality using the Ribbon
>
> Currently I have over 20 Ribbons (and growing) all use the "Start from
> Scratch=True" attribute. Obviously I would prefer to utilise less Ribbons,
> but by the same token the prospect of dynamically applying lots of
> customisation to fewer Ribbons when each form or report is opened appears
> equally onerous. Needless to say I am still learning about how to best manage
> the Ribbon and hence the reason for this posting
>
> The biggest problems I currently have are simple issues such as
> enabling/disabling controls on the Ribbon dynamically according to actions a
> user might take on any given form.
>
> To force a ribbon control refresh requires you to "Invalidate" that control
> on the ribbon, however, this requires a reference to the ribbon and I have
> over 20 of them.
>
> I have tried storing the references in a collection using the Ribbon
> "OnLoad" callback:
>
> Public mcolRibbon As New Collection
>
> Public Function OnLoad(ByVal Ribbon As IRibbonUI)
> mcolRibbon.Add Ribbon, Screen.ActiveForm.RibbonName
> End Function
>
> However when I try to retrieve the reference perhaps in the Form_Open event
>
> Dim objRibbon as IRibbonUI
> Set objRibbon = GetRibbon(me.RibbonName)
>
> Public Function GetRibbon(strRibbonName as String) As IRibbonUI
> GetRibbon = mcolRibbon.Item(strRibbonName)
> End Function
>
> or invalidate the control from the from by calling:
>
> Public Sub gsubInvalidateControl(strRibbonName As String, strControl As
> String)
> mcolRibbon.Item(strRibbonName).InvalidateControl (strControl)
> End Sub
>
> it fails.
>
> There appear to be two issues:
>
> 1) If the Ribbon has not yet been loaded the open Form action causes the
> Ribbon to Load, however, the Form_Open event occurs before the Ribbon_OnLoad
> event meaning that a Null Reference is returned.
>
> 2) I don't use custom collections very often and I may have the syntax
> incorrect, but I am also experiencing problems accessing the Ribbon object
> from within the collection.
>
> Can anyone assist in either resolving the issues I am having or suggest a
> better approach.
>
> Your Assistance Appreciated
> Guy
From: Guy on 23 May 2007 22:25
Albert,

Just thought of another good reason why I can use Screen.ActiveForm, apart
from the fact that it doesn't work, it maybe a report that is causing the
Ribbon "OnLoad" event.

Guy

"Albert D. Kallal" wrote:

> Ah...
>
> I like you idea of building a collection.
>
> I really feel bad that I don't have more time to write up a nice article.
>
> However, a few things:
>
> The active screen is not set until AFTER both on-open , and then on-load ARE
> COMPLETE.
> (I know this, because I have for years use the on-open, or even the on-load
> event of the form
> to pick up the PREVIOUS form by going:
>
> set frmPrevous = screen.ActiveForm.
>
> So, that puts a bit of damper on your approach to pick up the active
> screen...(you can't do that from on-load, or on-open events of a form...you
> get the previous screen!!!
>
> and, normally, a *great* trick is to place the public function in a form, as
> menu items will call that first before the same function name in a public
> module (but, again..the form is not yet consider loaded. -- DO NOTE that
> once the ribbon is loaded, then routines declared public in your form that
> HAS THE FOCUS will be called. This allows some really nice custom code for
> each form, but the SAME ribbon to be used.
>
> Anyway, you can't use screen.active.
>
> 2nd thing:
>
> > mcolRibbon.Item(strRibbonName).InvalidateControl (strControl)
>
> You do realize that invalidate method only causes the xml mark-up text to be
> re-executed for that control. So, for setting the controls visible property,
> you actually have to maintain that value in memory (in the old menu bars,
> the menu control it self held the state of the control. This is NOT the case
> with a ribbon. (it possible you realize this...as you not posted any xml
> ribbon code)..
>
> So, what this means is that you can hard code the mark-up, but that is of no
> use. The solution is thus to use a function, such as:
>
> <button id="button1" label="Buttion1"
> getVisible="MyVisible"
> onAction="=MyTest1()"/>
>
> While we at this, it important to note that the mark-up text IS CASE
> sensitive (I read 20+ articles about the ribbon, and NOT ONE has mentioned
> this -- I just trying to save you some lessons that I learned!!!.
>
> Note that we have a function called MyVisble. In theory, you need a
> variable declared for EACH control that you plan to have visible, or not
> visible.
>
> The ONLY difference between using
>
> mcoIRibbion.Invalide
>
> and
>
> mcoRibbion.InvalideCotnorl(strContlr)
>
> Is that the 2nd example only runs the code for the ONE control. THIS IS THE
> ONLY DIFFERENCE. In other words, the same piece code would get called in
> both cases, but the InvalideContrll is better code because it will not call
> each and every piece of code for each button on the ribbon.
>
> So, keep in mind, the invalide call does not actually make the control
> visible, or invisible...it is the setting for the function in the he call
> back that determines this. and *further* keep in mind that the code called
> will be the same both cases, but it better to use the Invalidecontrfll as
> you not wind up having every control on the bar get called. (however, you
> code MUST STILL work for that case).
>
> eg:
>
> Public Sub MyVisible(control As IRibbonControl, _
> ByRef visible As Variant)
>
> static m_buttion1 as boolean
> static m_buttion2 as boolean
> select case contorl.id
>
> case "buttion1"
>
> visible = m_buttion1
>
> case "buttion2"
>
> End Sub
>
> In the above, "id" will be text string (buttion1 from above) in BOTH cases
>
> eg:
>
> mcoribbion.Invalide
> or
> mcoribbion.InvalideContorl("buttion1")
>
> It is the *system* that provong the value for contorl.ID in the above. For a
> small robbionk you could always use the Invalide, and never have to use the
> InvalideContrl method.
>
> So, what the above means is that you need a Boolean variable for each
> control that I plan to turn off, and turn on to hold the state of that
> control.
>
> (or a separate "getVisible" function for each control that you plan to make
> visible/non visible).
>
> Without question, building a custom collection is a great idea. with a bit
> of work you could near the same syntax of what we been using for years....
>
> I have to run out the door right now. I *really* need to write an article on
> this......
>
>
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal(a)msn.com
>
>
>
From: Albert D. Kallal on 24 May 2007 07:26
"Guy" <Guy(a)discussions.microsoft.com> wrote in message
news:AAEC10E1-E16A-47DE-B72E-8E08116A4163(a)microsoft.com...
> Hi Albert,
>
> Thank you for your comprehensive response.
>
> I wasn't aware that once the form was loaded/opened the forms public
> functions would get called in preference to module public functions. This
> could be useful so I will investigate this aspect further.

Actually, a97 to a2003 worked this way also. So, most of my custom menu bars
in previous versions DID in fact call code in the current active form.

This does man that some forms did share the same menu bar. However, in all
practical use, most of my complex forms did have a custom menu bar, but that
menu bar for the most part did call code in the current active form (it
makes sense to place code for a form in the form code module).

>
> Ideally I would like to use just 3 Ribbons:
>
> 1) Home - Which allows quick access to key forms, reports and tools such
> as
> database backup within the application and is shown only when the Main
> Menu
> is active.

I think in the above case, I would simply build a xml ribbon,, and then
specify this ribbon in the forms "other" tab. This will load up the ribbon
for you, and you not need any code.

> 2) Form - Which shows for each form the "Home" button which takes you back
> to the Main Menu and Home Ribbon and the standard Records/Filters/Cut and
> Paste groups plus custom Command actions that are relevant to that form.
> So
> for instance if it was the "Customer Invoice" form you could print the
> Customer Invoice or if it was the "Budget" form you could export the
> budget
> to Excel. Some forms would have many Command actions available.

Sure, to include the standard paste, cut etc, you can either build you own
"group" that you add to each ribbon. However, it is MUCH easier to include
the standard cut/paste menu "group" in your ribbon that you build for the
current form.

To make you "own" clipboard, you could use:

<group id="Clipboard" label="Clipboard">
<control idMso = "Copy"/>
<control idMso = "Cut"/>
<control idMso = "Paste"/>
</group>

however, it much easier to include the built in ones. For example, lets
include both built in clipboard, and the "find" and filter group. You can
go:

<group idMso="GroupClipboard" />
<group idMso="GroupFindAccess" />
<group idMso="GroupSortAndFilter" />

So, it is really easy to add the standard cut/copy to your existing ribbon.

>
> 3) Report - Which shows for each report the "Home" button plus the
> standard
> print/export/send mail groups and so on. It is unlikely that many custom
> Command actions would be required for this Ribbon.

Right...and again, I would build a new ribbon, but simply include some of
the report groups. You do know how to "find" the above group names? (and as
I mentioned...remember...they are case sensitive).

To find the id of a command, or group, simply go:

office button->access options (lower right)
customize

Note in the customizing window if you hold your mouse over a command button,
you get the isoID of that button (it that last value enclosed in ()).

NOTE VERY clearly how in addition to the buttons, the "groups" for each of
those buttons are ALSO IN that list. So, you can easily grab the group for
your report....


> Currently I have about 20 individual Form Ribbons, however, in your
> opinion
> would it be better to have just 1 Form Ribbon and customise it
> appropriately
> when each form becomes active?

I actually think it ok to have a ribbon for each form, especially if that
ribbon has form specify commands. so, sure, "include" the standard clip
group, and perhaps a general close button group for each form. but, I would
certainly continue to build a ribbon for each form.

> Assuming that there are pros/cons to both the above approaches and that we
> are happy to continue with the multiple Form Ribbon method. The next
> question
> is maintaining references to each Ribbon so that we can hide/show or
> enable/disable etc the Form Command actions.

Ok, this is tough part! I just finished coding a solution that allows me to
use a collection to "enable" and "disable" a button by name. However, the
code is not finished, has some problems. However, it is *perfect* for
migration existing applications, and you can simple code in the form as you
always done.

eg:

MyRibbon.MyControls("button1").Enabled = True
or
MyRibbon.MyControls("button1").Visible = True
or
MyRibbon.MyControls("button1").Label = True

The MyContorls collection is actually automatically populated by the ribbon
load command I have. So, right now, this code is running as I write this,
but there are still some design issues that need to be addressed.

However, since my code is not ready, then your going to have to write code
for each button that you want to enable, and disable. And further, you going
to have to place that code in a standard code module (not the forms code
module). And, worse, you going to have to setup a piece of code for each
control that you need to enable and disable. Remember, the ribbon does NOT
store the state of your enabled/disabled button.

It was easy before, since if you enabled, or displayed a control in a menu
bar, you could, or change that value at will. In the case of a ribbon, your
code must hold these values in variables (which is why I love my code
example...since you don't have to write ANY additional code, but use the
above familiar syntax.

> As previously stated I have tried to maintain the Ribbon references in a
> custom Collection which is loaded via the Ribbon "OnLoad" event. However,
> I
> am having problems with:
>
> 1) Event timing
> 2) Populating and Accessing the reference in the Collection

Yes. I just wrote that code last night.

A few got ya's that I just learned:

The ribbon load event only fires once, and that is when the form loads.
Additional loads of the form does NOT fire the load event. so, I do need a
global collection of ribbons, and further, I need a routine that checks if
the ribbon is already loaded.

in a standard code module, I have for the global enabled, and disabled, I
have

Public Sub MyVisible(control As IRibbonControl, _
ByRef visible As Variant)
Dim f As Form

If Forms.Count > 0 Then
Set f = Screen.ActiveForm
visible = f.MyRibbon.MyControls(control.ID).visible
End If

End Sub


Public Sub MyEnable(control As IRibbonControl, _
ByRef visible As Variant)
Dim f As Form

Set f = Screen.ActiveForm

visible = f.MyRibbon.MyControls(control.ID).Enabled


End Sub

In addtion, my global load code in this same module is:

Public Sub SetMyRib(frm As Form, Optional strRibbonName As String = "")

Set f = frm

If strRibbonName = "" Then
strRibbonName = f.name
End If

f.RibbonName = strRibbonName
' the above code causes the ribbom to load, and fires the code below
End Sub


Public Sub MyRibbonLoad(ByRef nribbonUI As Office.IRibbonUI)

Set f.MyRibbon.m_ribbon = nribbonUI
Set f = Nothing

End Sub

In the above, I should be saving the ribbon into a global collection in
addition to the "myRibbom"

MyRibbom is a class object I built in code, and every form you make is going
to define this. So, in each form that needs a custom ribbbom, I go:

Option Compare Database
Option Explicit

Public MyRibbon As New clsRibbon


I leave the ribbon setting in he "other" tab blank, because I need to pass
the form name (the problem of screen focus).
(so, the callig code just sets the ribbom value for the form...it less
code).

So in my forms on-load event, I go:

Call SetMyRib(Me, "test1")

me is the form, and "test1" is the name of the ribbom I want to load

then, I have a class module for the robbom, it simply a colleciton of
contorls that gets auto built for me
clsRibbon

Option Compare Database
Option Explicit

Dim colControls As New Collection
Public m_ribbon As IRibbonUI


Public Property Get MyControls(strC As String) As clsRibContorl

Dim i As Integer
Dim intGotOne As Integer
Dim NewControl As New clsRibContorl

' look for contorl in colleciton, if not in, then add...
For i = 1 To colControls.Count
If strC = colControls(i).name Then
intGotOne = i
Exit For
End If
Next i

If intGotOne = 0 Then
' add contorl, set defaults
NewControl.Enabled = True
NewControl.visible = True
NewControl.Label = ""
NewControl.name = strC
colControls.Add NewControl, strC

Set MyControls = NewControl
Else

Set MyControls = colControls(intGotOne)
Me.m_ribbon.InvalidateControl (strC)
End If


End Property


And, for each member of the control collection, we do need to "save" those
values, so, I built a custom class control
to STORE values for each ribbom contorl (since the robbion don't store
them).

it is:
clsRibContorl

Option Compare Database
Option Explicit

Dim m_enabled As Boolean
Dim m_visible As Boolean
Dim m_Label As String
Dim m_name As String



Public Property Let Enabled(bol As Boolean)

m_enabled = bol

End Property

Public Property Get Enabled() As Boolean

Enabled = m_enabled

End Property

'-----------

Public Property Let visible(bol As Boolean)

m_visible = bol

End Property

Public Property Get visible() As Boolean

visible = m_visible

End Property

Public Property Let Label(str As String)

m_Label = str

End Property

Public Property Get Label() As String

m_Label = m_Label

End Property

Public Property Get name() As String

name = m_name

End Property

Public Property Let name(str As String)

m_name = str

End Property



That is it. The whole thing thus gives me a re-usable collection that is
auto populated.

You have to add the enabled, and visible call-backs to ONLY THOSE CONTROLS
that you need this feature:

eg:

<button id="button1" label="Buttion1"
getVisible="MyVisible"
getEnabled="MyEnable"
onAction="=MyTest1()"/>

<button id="button2" label="button2" onAction="=Mytest2()"/>

In the above, you can see that for buttion2, I never enable, or disable the
button, so I did NOT add the call-backs for the getvisble and get enabled.

And, I suppose you could always just include the above two call-backs for
all controls, but it will load up the custom collection.

The above is not a lot of code, as is something I threw together last night
in a very short time (I am just leaning this stuff too, and this is my first
try at this ribbon thing).

So, each form will have it own custom "ribbon" collection with the enable,
visible, and label property able to be set. While this means that you have
to declare that "myrobbon" for each form you use, it only one var and the
rest of the code will make this whole process automatic.

As mentioned, in testing,, the code loads, and runs....however, when you
un-load a form, and then re-load the form, it breaks the code because the
ribbon load event does not re-fire (this is good from a performance point of
view, but since the ribbon does NOT re-fire, I lost the reference to the
internal ribbon that is already loaded. So, either I build a collector for
this, or find a command to force the ribbon to re-fire the load event....

Really, less then 1 hour more on this...and it should be ready to go...

(however, I not have that 1 hour until about Monday!).


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal(a)msn.com


 |  Next  |  Last
Pages: 1 2
Prev: Conditional Formatting for Image box on Continuous Forms - Again
Next: Access 2007 Runtime Problems