Anita Smith
anita at ddisolutions.com.au
Tue Apr 29 22:37:33 CDT 2014
Sorry, I didn't know anyone else needed the adp version.
For the moment - while I'm still working on the program I have an AutoExec Macro that fires off the code to load the ribbons from the table - it goes something like this:
Public Function LoadRibbons()
Dim strSQL As String
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset
strSQL = "SELECT * FROM [MyCustomization]"
Set rst = GetRecordset(strSQL, adUseClient, adOpenKeyset)
cnn.Open CurrentProject.Connection
rst.Open strSQL, cnn, adOpenDynamic, adLockOptimistic
Do Until rst.EOF
Application.LoadCustomUI rst("RibbonName").value, rst("RibbonXml").value
rst.MoveNext
Loop
End Function
I'm letting this AutoExec macro fire up the LoadRibbons code when I open the program to work on it so that the Ribbons are ready and loaded for when I need to assign them to forms and reports. Later I will load the ribbons from my startup form.
Anita Smith
-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bill Benson
Sent: Wednesday, 30 April 2014 1:20 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Ribbon
Hi Anita, I have actually never used this but your post got me reading:
http://msdn.microsoft.com/en-us/library/office/ff198313(v=office.15).aspx .
As you pointed out, XML can be loaded through VBA dynamically. But you did not state where or how you called the LoadCustomUI method. I tried this out but got an error message that my customization was already loaded. Here is what I did to recreate this situation:
(1) I copied uSysRibbons to a new table called MyCustomization
(2) I deleted all the records in uSysRibbons.
(3) I deleted the value in the drop down in the startup options
File / Options / Current Database / Ribbon and Toolbar Options setting
(4) I closed and re-opened the database, observing that there was nothing in the
File / Options / Current Database / Ribbon and Toolbar Options setting.
(5) I created a public function in a standard module:
Function MyAutoOpen()
On Error Resume Next
Application.LoadCustomUI _
CustomUIName:="MyHide", _
CustomUIXML:= _
DLookup("RibbonXML", "MyCustomization", "RibbonName =
'MyHide'")
If Err.Number <> 0 Then MsgBox Err.Description
On Error GoTo 0
End Function
(6) I created an AutoExec macro called MyAutoOpen and saved the module.
(7) I closed and re-opened the database.
"RibbonReportPDF.accdb cannot load customization 'MyHide'. This customization name was already loaded"
Additional remarks:
(a) There does not seem to be a requirement to have an ID field, that was just someone's convention.
(b) If you use the uSysRibbons table, it causes the ribbons to be selectable in the database options - so I would advocate using a different table or, as you have done, use code.
(c) If I ever get this to work, would not a good place to put the LoadCustomUI method be in the Activate event of each Form and Report, to prevent inappropriate ribbons when the database's Document Window Option is set to Tabbed Documents?
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Anita Smith
Sent: Tuesday, April 29, 2014 8:17 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Ribbon
Ok this is the crux of the method.
A system table USysRibbons (that you create yourself - or copy form the link
below) holds the Ribbon Info. You have to enable viewing of system objects in the Options/Current Database/Navigation to see this table.
This table has 3 fields
ID
RibbonName
RibbonXML
Once this table is in the database it is automatically loaded when the program loads and the ribbons within it can be used on forms and reports.
I have two ribbons in mine - one that essentially hides the normal ribbon and a print preview ribbon.
As for the XML it is not too hard to suss out once you have a sample to look at.
Anyhow here is the link to the file download where you can see this
demonstrated:
http://www.kallal.ca/msaccess/DownLoad.htm
The one to get is - Access 2007 report ribbon example (with PDF and email).
I had to do it a bit differently as I have an adp with SQL server back end - I had to create a regular sql table and load the ribbons with code manually
- not too hard but a little more work.
Anita Smith
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
Sent: Wednesday, 30 April 2014 9:52 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Ribbon
Hi Anita:
Please do. :-)
Jim
----- Original Message -----
From: "Anita Smith" <anita at ddisolutions.com.au>
To: "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com>
Sent: Tuesday, April 29, 2014 4:47:38 PM
Subject: [AccessD] Ribbon
Hi fellow earthlings,
While solving a problem one of my users have encountered after updating to Access 2010 runtime I came across some nifty ribbon shenanigans that I have filed under useful stuff.
Her problem was that the Access 2010 runtime Print Preview toolbar was missing exports to Word and Excel among other items. I had no option than to dive into research on how to solve this highly annoying 'feature'. Anyhow - I came across a way to quickly create custom ribbons using a system table and XML. In no time I had a new Print Preview Ribbon up and running and as an added bonus I also got rid of the main ribbon for the whole application using this method.
As I have not had time to read and follow all AccessD threads, I don't know if this is common knowledge here.
Anyhow - I'll be happy to elaborate further should anyone have a need.
Anita Smith
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com