Solved

use VBA to load a Ribbon in Access 2010

Posted on 2010-11-13
6
3,611 Views
Last Modified: 2012-05-10
Hi,

i have a database that is used by different users.

i have create the USysRibbon table and created 2 ribbons.

at the moment in Options.. Curent Database.. i can select a Ribbon to use.

What i hope is that there is some VBA that i can use instead of using the database options so i can load a ribbon.

This way i can load different ribbons for different users.

i have tried DoCmd.ShowToolbar but that did not work.

thanks

jack
0
Comment
Question by:jackbenson
  • 3
  • 2
6 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 34128067
You can try changing the database property

CurrentDb.Properties("CustomRibbonID").Value = "MyRibbonName"

db usually requires a restart to see the changes though
0
 
LVL 1

Author Comment

by:jackbenson
ID: 34128209
excellent

that works.

you have to have a value set in Options.. Curent Database.. Ribbon Name

otherwise i get an error saying that the Property Does Not Exist when i try to set it.

would be great if there was a way that did not require a re-start.

thanks

jack
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 34128270
if its a new property you can create one. We can check the error code then create the property if it dont exist.

when you select it manually like you did via Options, Access itself tells you right? Perhaps its because it is something that is loaded on application startup (access internals) and it is not easily configurable when using access. So
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 75
ID: 34129701
Here is a function to create a new Property:

Public Function SDB_SetProperty(argPrpName As String, argPrpType, argPrpVal)

'ExtName:Set Property
'Actions:Sets the specified database property
   
    On Error GoTo SDB_SetProperty_Error
   
    Dim prp As Property
    CurrentDb.Properties(argPrpName) = argPrpVal
    SDB_SetProperty = True
   
SDB_SetProperty_Exit:
    On Error Resume Next
    Set prp = Nothing
    Err.Clear
    Exit Function

SDB_SetProperty_Error:
    If Err.Number = 3270 Then   'Property not found
        Set prp = CurrentDb.CreateProperty(argPrpName, argPrpType, argPrpVal)
        CurrentDb.Properties.Append prp
        SDB_SetProperty = True
    Else
        SDB_SetProperty = False
        MsgBox "An unexpected error has occurred: " & Err.Number & "  " & Err.Description
    End If
    GoTo SDB_SetProperty_Exit

End Function

mx
0
 
LVL 1

Author Comment

by:jackbenson
ID: 34130549
THANKS MX

i am calling the function as follows:

DB_SetProperty "CustomRibbonID", dbText, "TOOLS"

where "TOOTS" is the name of my ribbon

but i get the following message - Type Missmatch

the problem is caused on this line of code:
Set prp = CurrentDb.CreateProperty(argPrpName, argPrpType, argPrpVal)

what is the correct value for argPrpType that i should be passing?

thanks

jack
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 125 total points
ID: 34131177
Here is sample code I have that defines a property. I have changed it to your needs.


    Dim p As DAO.Property
   
    On Error Resume Next
   
    CurrentDb.Properties("CustomRibbonID").Value = "TOOLS"
    If Err.Number = 3270 Then
        Err.Clear
        CurrentDb.Properties.Append CurrentDb.CreateProperty("CustomRibbonID", dbText, "TOOLS")
        if err.number <> 0 then msgbox err.description
    End If

0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now