Solved

use VBA to load a Ribbon in Access 2010

Posted on 2010-11-13
6
3,824 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

717 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