We help IT Professionals succeed at work.

I cannot find a solution to this Access 2007 ribbon problem so it is either a no-brainer or a known-bug.

ZQL
ZQL asked
on
2,305 Views
Last Modified: 2013-11-27
Hello..
I have encountered some known bugs in VBA app development for Access 07 before, so I searched extensively for this issue but to no avail.
I have built a custom ribbon, hand-coding the xml along with the schema and verified that the ribbon itself loads without a problem and the callbacks are set up to work properly.  

But the problem is that they are not working.  I am getting the error message about not being able to find the macro or callback function name, even though I have done the following:

- Put the callback in an attached module both as a public sub and function (even updating the xml's callback name string to "=Function()" as appropriate
- Loaded the ribbon by using the Current Database's custom ribbon field in Access Options ~and~ with the ribbon field in form properties.
- Loaded another 2007 database with a working custom ribbon stored in a table just like mine and could find absolutely no differences in the usage of xml or the method of calling an OnAction sub or function.

I would gladly post any of the code I have done if that would help solve this.  Switching to an MDI full-screen interface would greatly benefit this project but this custom ribbon bit is all that stands in the way, and I am truly stumped.
Thanks everyone in advance..
Comment
Watch Question

Would you please post the callback code.
-Chuck
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
<I have encountered some known bugs >
Can you post some references to these " known bugs" please?

If they are in fact "Bugs", then are you up to date with all of your Service Packs for Office *And* Windows?
ZQL

Author

Commented:
Yes, I am using XPSP3 and Office07SP2.
The known bugs were addressed and resolved in SP1 or 2, and they are not related to this issue.. but I'd be happy to dig up references just for curiosity's sake.

Here are some snippets of both the XML and the VBA (which current resides in a module).  I have tried individual subs for each button, and have tried using functions as well.. all public.
The ribbon loads and displays correctly, except for not being able to find any of the code, not even the loading sub.


<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="onRibbonLoad"> 
<group id="grpMain" label ="Main">
   <button id="cmdMainWindow" label="Main Window" onAction="OnActionButton"
    size="large" imageMso="OpenStartPage" />   
</group> 
Public gRibbon As IRibbonUI 
Public Sub OnRibbonLoad(ribbon As IRibbonUI)
    Set gRibbon = ribbon
End Sub 
Public Sub OnActionButton(control As IRibbonControl) 
On Error Resume Next
    Select Case control.ID
        Case Is = "cmdMainWindow"
            DoCmd.OpenForm "frmMain"
    End Select
End Sub

Open in new window

Cannot-find.png
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
Se here for troubleshooting perhaps?
http://office.microsoft.com/en-us/access/HA102114151033.aspx
ZQL

Author

Commented:
Almost all of the sites I have looked at (including that one) focus on the xml and implementing of the ribbon.  It hardly discusses the OnAction tag and VBA callbacks.  The link you posted from MS only mentions assigning macros.  I wasn't able to find anything there regarding my problem.  Thank you for trying though.
I use a similar set of functions and XML only without the onLoad="onRibbonLoad" (which seems to be what is giving you the error) and it works. I have  included a simplified version of this code in the snippet.
-Chuck
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
	<ribbon startFromScratch="false">
		<tabs>
			<tab id="tabMain" label="Main">
				<group id="grpForm" label="Forms">
					<button id="btnSearch" label="Search" onAction="OpenSearchForm"/>
				</group>
			</tab>
		</tabs>
	</ribbon>
</customUI>
 
Public Function OpenSearchForm(ctl As IRibbonControl)
    DoCmd.OpenForm "frmSearch"
End Function

Open in new window

ZQL

Author

Commented:
I just tried setting up the code similar to what you have, and it still did not work.  It can't run the macro or function because it cannot find it or the parameters are incorrect.  (see the image I attached above, but it is the OnAction function instead of OnLoad now)
I still have the function in a module, would it help to move it to a form or something? I need the ribbon to be database-wide though.  This truly has me stumped!

The OnActions of the ribbon simply cannot locate the public subs and functions I am telling it to call!
The function/sub must be in a separate module (not in a form). Can you please post your XML and OnAction code as they are now?
-Chuck
ZQL

Author

Commented:
Sure.  It is only slightly modified from what I posted above.  The OnAction code is in a separate module.
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="onRibbonLoad">
  <ribbon startFromScratch="true">
    <officeMenu>
      <button idMso="FileCompactAndRepairDatabase" insertBeforeMso ="FileCloseDatabase" />
      <button idMso="FileOpenDatabase" visible="false"/>
      <button idMso="FileNewDatabase" visible="false"/>
      <splitButton idMso="FileSaveAsMenuAccess" visible="false" />
    </officeMenu>
    <tabs>
      <tab id="tabMain" label="Home">
        <group id="grpMain" label ="Main">
          <button id="cmdMainWindow" label="Main Window" onAction="OnActionButton"
           size="large" imageMso="OpenStartPage" />   
        </group>
        <group id="grpCompany" label="Company">
          <button id="cmdEdit" label="Edit Mode" onAction="OnActionButton" size="large"
           imageMso="TextReflectionGallery" supertip="Toggle company editing mode."/>
          <button id="cmdNewCompany" label="New Company" imageMso="QueryShowTable" size="large"
          onAction="OnActionButton"/>
          <button id="cmdDeleteCompany" label="Delete This Company"
          imageMso="RecordsDeleteRecord" size="large" onAction="OnActionButton"/>
          <button id="cmdSearchCompany" label="Find Company's Website"
           imageMso="ViewOnlineConnection" size="normal" onAction="OnActionButton"/>
          <button id="cmdMapCompany" label="Maps and Pictures"
           imageMso="MapContactAddress" size="normal" onAction="OnActionButton"/>
          <menu id="mnuCompanyReports" label="Company Reports" imageMso ="ViewsReportView" itemSize="large" >
            <button id="cmdCompanyRegistration" label="Registered vs Unregistered"
            imageMso="AcceptInvitation" onAction="OnActionButton"/>
            <button id="cmdCompanyReadyToSell" label="Companies Ready To Sell"
            imageMso="AccountingFormat" onAction="OnActionButton"/>
          </menu>
        </group>
        <group id="grpContacts" label="Contacts">            
          <button id="cmdAddNewContact" label="Add New Contact" size="large"
           imageMso="RecordsAddFromOutlook" onAction="OnActionButton"/>
          <button id="cmdFindOnline" label="Find Contact Online" size="large"
           imageMso="OutlookGlobe" onAction="OnActionButton"/>
          <button id="cmdMoveorDelete" label="Move or Delete" imageMso="DistributionListRemoveMember"
           onAction="OnActionButton" size="large"/>
          <button id="cmdLookupContact" label="Search Database for Name" size="large"
           imageMso="AddressBook" onAction="OnActionButton"/>
        </group>
        <group id="grpFollowUps" label="Follow-Ups">         
          <button id="cmdPrimitiveFollowups" label="View Scheduled Follow-Ups" size="large"
           imageMso="NewMeetingWithContact" onAction="OnActionButton"/>
          <button id="cmdOutlookSync" label="Sync in Outlook" imageMso="AccessListEvents"
           onAction="OnActionButton" size="large"/>
        </group>
        <group id="grpOther" label="Misc">
          <button id="cmdBar" label="Switch to Bar-Mode" onAction="OnActionButton"
           size ="large" imageMso="ObjectsGroup" supertip="Switch to Bar Mode."/> 
        </group>
      </tab>      
    </tabs>
  </ribbon>
</customUI>  
Option Compare Database 
Public gRibbon As IRibbonUI 
Public Sub OnRibbonLoad(ribbon As IRibbonUI) 
    lngDropDownValue = 2
    Set gRibbon = ribbon
End Sub
Public Function OpenMainWindow(ctl As IRibbonControl)
    DoCmd.OpenForm "frmMain"
End Function
Public Sub OnActionButton(control As IRibbonControl) 
On Error Resume Next
    Select Case control.ID
        Case Is = "cmdMainWindow"
            DoCmd.OpenForm "frmMain"
    End Select
End Sub

Open in new window

1. Try removing onLoad="onRibbonLoad"> from line 1 (<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="onRibbonLoad">)
2. Try changing:
Public Sub OnActionButton(control As IRibbonControl)
   to:
Public Sub OnActionButton(ctl As IRibbonControl)
(I think control is a key word).
-Chuck
ZQL

Author

Commented:
Chuck, I made the changes you recommended but alas, I am getting the same message:

"can't run the macro or callback function 'OnActionButton'.
Make sure the macro or function exists and takes the correct parameters.

It is a public sub in an attached module.  I've included a screenshot.
module.png
Well, I used your XML exactly as is, used your module code exactly as is, copied one of my existing forms as "frmMain" and ... it works perfectly in my database. The only thing I had to add was:
Public lngDropDownValue As Long

1. Is the RibbonName in USysRibbons table the same as the ribbon name you selected in Access Options => Current Database => Ribbon and Toolbar Options => Ribbon Name?
2. Is the RibbonName field in the USysRibbons table a Text data type with a field size of 255 and set as the Primary Key?
3. Is the RibbonXml field in the USysRibbons table a Memo data type?
4. Have your compiled your code without error?
5. Are you using an .ACCDB database?

If all of these are true, I recommend you copy your USysRibbons table and basRibbonCallbacks to a new databasea, set the Ribbon Name in Access Options, and test just this code on its own.
-Chuck
ZQL

Author

Commented:
The answers to your questions,

1. Yes.  "Home" is the name.
2. Yes.  I have tried setting both the RibbonName and auto-number fields as PK
3. Yes.
4. Yes.
5. Yes.

I have created a new database and used the xml and module exactly as they are, just like you did.  But I am getting the very same error about not being able to find the sub or function.  I don't think I did anything differently than you did.

I have attached the new database with the xml and module intact.  Perhaps you could see if there is anything different from the one you made and had success with?

EE doesn't allow zip files with .accdb extensions, so I renamed it to .mdb.  You may want to change it back to .accdb

Thanks so much...
Database1.zip
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
ZQL

Author

Commented:
Yes, that was it.  Thank you.  When I first started working on the ribbon, I checked the references and saw Microsoft Access 12.0 Object Library and mistook it for the Office one.  I hadn't even thought of going back there to verify during all of this.  So thank you again!
Yeah, that one tripped me up when I started developing my ribbon and the only way I found it was that the IRibbonControl caused a compile error. You are welcome. Good luck with your project.
-Chuck
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.