DataBase - Display custom Ribbon/Toolbar in Query Design

Asked By CP

12-Jan-10 04:18 PM

We have an Access 2003 application that we are converting to Access
2007.  I have (finally) figured out how to create customized ribbons
to replace the customized menus in the older version.  And I have
figured out how to
associate specific ribbons with specific forms.  Now I have to figure
out a way to load another customized ribbon during Query & Report
Design which do not appear to have actual forms.

The code that loads the Query Design form and a custom Toolbar in the
Access 2003 version
is:
DoCmd.OpenQuery strQueryFullName, acViewDesign, acEdit
DoCmd.RunCommand acCmdDesignView
DoCmd.ShowToolbar "CLS Query Design", acToolbarYes

I have tried replacing the name of the old Toolbar with one of my new
ones but nothing shows.  I also tried replacing it with "Ribbon" but
still nothing shows.

I have read through a lot of information about using the LoadCustomUI
command to make the list of all my customized ribbons available.  BUT
I cannot find anything about how to actually access the Recordset that
is created.  I think I can do Application.LoadCustomUI (customUIName,
customUIXML) for the particular ribbon but what goes in the
customUIXML parameter (I am guessing that the customUIName is the name
of my Ribbon or is the customUIName = the name of the table that
contains all the ribbons and the customUIXML is the name of the
particular ribbon)?


I am going around and around in circles so I hope someone can help me
get out.


Thanks in advance.  Carol.

11-Jan-10 02:36 PM

I would question the wisdom of allowing uses to modify sql.

What happens if someone types in:

drop table customers

and hits ctrl-> (ctrl key and the ">" key to run this?)

There should be VERY little reason for users to see the sql in
design mode.

For reports, the suggestion is best to supply an form with an

this concept is outlined here:
http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html

In 20 years of writing software for customers, I never
had to allow customers every to see or type in dangerous sql.

Anyway, for forms + reports, you can specify the ribbon name in
the "other" tab.

For the application ribbon, you can simply go:

RibbonName = "Name of Ribbon Goes here"




The table of custom ribbons you build NEVER needs to be loaded as some
type of reocord set (so, your question seems like a wild goose chase).


No, you only need the above if you NOT going to use the USYSRibbon table and
want to load xml in code, or load xml from an external file. As long as your
USysRibbon table is created correctly, then you can use the above code, or
for forms + reports simply use the Ribbon Name setting in the property sheet
for the form or report.

You can also re-purpose the built in query design ribbon also. However, the
above code would be a drop in replacement for your code that displayed a
particular custom menu bar.

So, the above should work for you.

Keep in mind that allowing users to actually modify and change the design of
sql queries means they can create bad things like delete quires etc. I would
use great caution in allowing users to see the query builder. Parameters
etc. for reports can better be handled by just building an form that prompts
the users for the necessary parameters as the above link + screen shot
shows.

Anway, you can display any ribbon with the VBA code  of:

eg:

RibbonName = "Name of Ribbon from USYSRibbons Goes here"


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

12-Jan-10 04:18 PM

Thanks, Albert.

I am pretty sure that my next question is one that I should know the
answer to but...
When I use the RibbonName =3D "Name of Ribbon from USysRibbons..." code,
I get "Variable RibbonName not found".  When I put RibbonName as a
CommandBar or as an Object, I get error #91 (the dreaded "Object
variable or With Block Variable not set").  How should it be declared?

Re: giving users access to SQL, I will just say that this is a legacy
system that uses ODBC connections to Sybase tables and the users are
blocked from doing anything destructive to the database in any number
of different ways.

Thanks again for your help.  Carol.
---------------------
esrpt/ridesrpt.html
and
our
r
eet
he
of
uld
pts

13-Jan-10 11:30 AM

Thanks, Albert.

I am pretty sure that my next question is one that I should know the
answer to but...
When I use the RibbonName = "Name of Ribbon from USysRibbons..." code,
I get "Variable RibbonName not found".  When I put RibbonName as a
CommandBar or as an Object, I get error #91 (the dreaded "Object
variable or With Block Variable not set").  How should it be declared?

Let me try this....

Ok, Yes, you are right. The RibbonName ONLY works if you have a form open
(ribbonname is an property of an form..and you can use this to
switch/change the ribbon that is displayed but only with a form)

However, in your case, the "whole" problem (and I now realize your
original problem) is you DON'T have a form open here, but just the
query builder in design mode...

Ok, that means we have to simply allow the built in ribbon to display
(and you hint you do not want that).

You have to re-purpose the built in ribbon in this case. That means you have
to decide what features/options you want from the original design ribbon to
display. Another way to handle this
would be to use "start from scratch = true" to hide built in, and then
enable some query design groups you create in this replacement ribbon.

I never re-purposed built in ribbons. How to do this is outlined here:
http://www.accessribbon.de/en/index.php?Access_-_Ribbons:Ribbon_XML___Controls:Manipulate_existing_Access_Ribbons

Because I not done the above, then I would suggest you simply build your own
ribbon, and specify this ribbon as the Application wide ribbon. (and, then
simply use visible settings (call backs) to hide parts of this main ribbon
and turn on (display) the parts you need for the sql design mode. I
suggesting this as an somewhat easer path then trying to re-purpose all of
the query design ribbon (I think it less work).

it not clear, but I would assume you now have a system wide "default" ribbon
for your application? (this ribbon is where I would add in the ribbon design
parts you need/want into a custom ribbon group..and the you can hide/show
this group for query desing mode).


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