Solved

use VBA to load a Ribbon in Access 2010

Posted on 2010-11-13
6
3,575 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

705 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

18 Experts available now in Live!

Get 1:1 Help Now