Late Binding of Office 12.0

[Keith]

I have an application that is developed in Access 2007, but some (or most) of
the users are running Access 2000. I created a ribbon menu for certain
things, and it worked fine on either 2000 or 2007 (or 2007 runtime) until I
added a button that opens a form. That button (or the code it called)
requires a reference to Microsoft Office 12.0 Object Library. Code below:

Public Sub OnOpenForm(Control As IRibbonControl)

DoCmd.OpenForm Control.Tag

End Sub

Since the Access 2000 users do not have this library available, they get
runtime errors. Is there a way I can late bind the Microsoft Office 12.0
Object Library so that it only gets called if the user calls a particular sub
or function? Alternatively, is there a way to install the Microsoft Office
12.0 Object Library on the machines that don't have Access 2007 installed on
them?

 

[Albert D. Kallal]

I have an application that is developed in Access 2007, but some (or most)
of
the users are running Access 2000. I created a ribbon menu for certain
things, and it worked fine on either 2000 or 2007 (or 2007 runtime) until
I
added a button that opens a form. That button (or the code it called)
requires a reference to Microsoft Office 12.0 Object Library. Code below:

Public Sub OnOpenForm(Control As IRibbonControl)

You should be able to remove the office 12 reference, and then go:

Public Sub OnOpenForm(Control As object)

The above would be effective late binding...

I would as a policy develop in the lowest common system (a2000) in your
case. Think fighting this development process in which you find out later
own that something don't work in a2000 is gong to cost you time wise....

We can't run a2007 on a dos machine, nor even in windows 3.1, or windows 95
for that matter...

On the other hand I can run the 1982 version of visi-calc on a brand new
windows 7 box. so, going forward is a good safe concept in development, but
developing in a2007 for software that going to run on a2000 is not a great
idea at all....

 

[Keith]

Albert,

I tried your suggestion, and I received the following error when clicking a
button on the ribbon that calls the sub (in Access 2007):

Run-time error '424':

Object required

I think I need to create the reference after the sub is called, and then
somehow set the Control variable as IRibbonControl. Any idea how to do this?

I realize that this isn't necessarily the best way to do things, but I'm
dealing with time and budget contraints that necessitate these kinds of
things.

Thanks,

Keith

 

[Albert D. Kallal]

You calling code, and the code that defines "control" will also have to be
change to object....

and, after you remove the office 12 reference, you are compiling the code
again to ensure there no syntax errors...right?

 

[Keith]

Albert,

Thanks again for the response. What I've decided to do is just define the
form that I want to open within the VBA code itself, by prompting the user to
specify the form they want. This eliminates the need to read the Tag of the
IRibbonControl, and therefore allows the application to run on Access 2000,
Access 2007 and Access 2007 runtime.

Eventually I will move all users off of Access 2000, but this workaround is
sufficient for the time being.

Thanks again,

Keith

 

[Keith]

Albert,

Thanks again for the response. What I've decided to do is just define the
form that I want to open within the VBA code itself, by prompting the user to
specify the form they want. This eliminates the need to read the Tag of the
IRibbonControl, and therefore allows the application to run on Access 2000,
Access 2007 and Access 2007 runtime.

Eventually I will move all users off of Access 2000, but this workaround is
sufficient for the time being.

Thanks again,

Keith

 

[Sky]

You should be able to remove the office 12 reference, and then go:

Public Sub OnOpenForm(Control As object)

The above would be effective late binding...

Have you been able to actually do this?

In my tests with Access 2007, I could not declare IRibbonControl as a
generic Object. Using the generic Object compiles fine in both Access 2007
and 2003, but the Access 2007 ribbon never actually finds the callback
function and calls it. It seems strange but apparently the late binding
searches for the exact IRibbonControl signature, and a generic Object does
not work, at least for me.

In order to use common code that runs under both Access 2003 and 2007 in a
single compiled .Mde, I was able to accomplish it only by completely
eliminating callback functions having the IRibbonControl argument. In the
ribbon XML each button is instead implemented with direct calls to VBA in
the onAction attribute, such as
onAction="=MyCustomVbaFunction('StringArgument')". The down side is that the
resulting ribbons are mostly static and you cannot dynamically modify the
ribbon display using the Invalidate method.

On the other hand, you CAN assign different ribbons to different forms and
reports, and the appropriate ribbons are brought forward dynamically with
the associated form or report**. For this (limited) dynamic behavior to
work, you do need these ribbons to be specified under the <tabSet
idMso="TabSetFormReportExtensibility" > element.

The other option is to create ribbon code with a #define and surrounding #if
that effectively removes the compilation under Access 2003. In this case you
must compile each version by manually changing the #define and distributing
separate release versions for Access 2003 and 2007.

- Steve

** Actually, the ribbon comes forward when a form first opens; unfortunately
if you have multiple forms open and switch between them, the ribbons do not
synchronize. Even in full Access, try going between query design and form
design; the ribbons do not synchronize properly. Oh well.

 

[Keith]

I never actually reference the ribbon in the callback. I just prompt the
user for what form they want to open after the ribbon button is clicked. I
know it's cheesy, but it buys me time to get my users upgraded.

Keith

 

[Sky]

I never actually reference the ribbon in the callback. I just prompt the
user for what form they want to open after the ribbon button is clicked.
I
know it's cheesy, but it buys me time to get my users upgraded.

Keith

Instead of prompting for a form, you could provide buttons on your ribbon to
open the forms.

For example, if you have two forms frmNode and frmLink, then your ribbon XML
would have buttons such as:

<button id="btnNode" label="Node" screentip="Node Data " supertip="long
description here" onAction="=OpenForm('frmNode')"/>

<button id="btnLink" label="Link" screentip="Link Data" supertip="long
description here" onAction="=OpenForm('frmLink')"/>

In your VBA you would have a function:

Function OpenForm(strFormName as String) as Boolean
On Error GoTo ErrExit
DoCmd.OpenForm acForm, strFormName
etc.

You would not need any ribbon callbacks at all. The code would compile under
both Access 2003 and 2007. In Access 2003 you display menus and toolbars
that call OpenForm; in Access 2007 you display a ribbon that calls OpenForm.

- Steve