Solved

use VBA to load a Ribbon in Access 2010

Posted on 2010-11-13
6
3,702 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

809 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