X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Custom Ribbon & Button - Run Code On Click, Access 2010    
post Sep 28 2017, 10:44 AM

Posts: 17
Joined: 13-October 16

NOTE: This question was originally posted as a reply to this previous discussion.

Curious, if the table is linked, how do you update the XML code (which is quite large)? In Access 2010 adp I just pasted it into the table from within access. Thanks.
Go to the top of the page
post Sep 29 2017, 07:43 AM

UtterAccess VIP
Posts: 2,551
Joined: 12-April 07
From: Edmonton, Alberta Canada

As a “general” rule there is not much need to use a ribbon callback and public Subroutines placed in a standard code module (that callbacks require).

Just like most buttons have code placed in the current form (where such code often belongs), then if you dump the use of call backs, then the ribbon (and button code) can be placed RIGHT in the forms code module.

And even better is you can pass "parameters" from the onAction if you do this (again much better than using call backs).

The result is a whole lot less code, far more flexible, and far less work.

In some of my larger applications some of which have in excess of 160 forms, looking at such only some of the major forms need their own custom ribbon. I think I count about a dozen in the whole application.

Furthermore, if you use this approach then in future applications when you pull one form and one ribbon from another application you simply pull the form, and ALL the code in the form that belongs to the ribbon ALSO comes along for the ride.

The trick to this is to not use callbacks?

You simply code the same as how custom menus work!

So in place of using a public Sub, you use a PUBLIC function in your form.

All you need to do is to declare any function you want called as a public function.

You then set the on action in the ribbon as follows


Note CAREFULLY how we have = and () (you must have these), and they must be under the quotes.

E.g. for the xml we have:


Note how the above is DIFFERENT than a call back for a ribbon (and callbacks use sub, where this is a function). Even better is the above means you do NOT have to place the code in a standard code module, but can place the function it in the current form.

So, no macro needed, no callback, and the code can go in the current form (just like it does for a command button).

And bottoms to dollar, in 9 out of 10 cases, the code you need for particular form and button belongs in that particular forms code module anyway. Note that this approach also allows one to have multiple instances of that same form.

As noted when moving forms between applications, or even making a copy of the form within the same application means that you REMOVE outside code dependencies that we normally as access developers do not expect or want.

We assume for the most part is that the code we're using for the form belongs in the forms code module and I 100% agree.

Keep in mind the above function call idea is the same format we can and would have been using since near day one with menu bars in previous editions of access. So, if you are wanting to change menu bar code to ribbon, use the above idea. (It is a direct one to one conversion).

Also, if you have several buttons that runs code in a given form, then again the above syntax allows one to KEEP the code in the current form and simply declare the button code as a public function (you can thus VERY easy move buttons from a form to a ribbon if you trying to de-clutter a form and build a ribbon).

Bonus part:

If the function name you specified in the menu or ribbon was named as public in the form's code module, then the CURRENT FORM with the CURRENT FOCUS is where the function will be first looked for to execute. This is SIGNIFICANTLY important because it means you can use one custom menu bar for five different forms, but each of the five different forms will run a custom code, such as a delete routine.

This approach thus removes all kinds of messy case statements as required with call backs.

In fact, all of the code stays in the form where it likely was or belongs in the first place, and that is in the forms code module.

And note that if you need + want global ribbon code, the SAME approach works.

So, if you have specific and specialized delete code that might be required for the given form that has the focus, then that's forms function code in the forms module will be run when it is called from the on action in the menu bar, or now ribbon.

However, for the other 20 forms? They will call + default to the global public function in a standard code module.

So in all my forms, I have a public functions for the delete button such as

Public function MyDelete()
    Code here to delete the record
End function

Also, note that you also pass values directly from the ribbon.

So, ribbon xml might be:

<button id="MyDelete" label="Delete Record"
imageMso="Delete" size="large"
supertip="Delete this record"

Note in the above, I passed the table name, and a prompt text of Staff.

And the public catch all function in a standard code module will be:

   Public Function MyDelete(strPrompt As String, strTable As String)
      Dim strSql        As String
      Dim f             As Form
      Set f = Screen.ActiveForm
      If MsgBox("Delete this " & strPrompt & " record?", _
                    vbQuestion + vbYesNoCancel, "Delete?") = vbYes Then


Note again how I passed two parameters to the delete routine (the text must be under single quotes). The prompt part so the msgbox command will say "Delete this staff?".
Now of course if the delete code routine is in the current form, then I would not need to "pass" values as per above - but the above is just again to show passing of values.
So, this "focus" trick, and that of using the expression service as opposed to call backs will save AMAZING amounts of code.

And, even better as noted this means is your whole overall general approach to building and using custom menu bar code in pre 2007 days will now work exactly the same for ribbons (they are 100% compatible with each other in how you call VBA code from a menu bar or now a ribbon.

Also keep in mind that the OnAction in the ribbon (or menu bar) you can pass information to your VBA routines.

For example here's a common one I use in which I pass the name of the report:
Public Function MyOpenReport(strR As String)
   DoCmd.OpenReport strR, acViewPreview

End Function

Now in a menu bar (or ribbon) the on action will be set to:


Note the above is taken from one of my older applications (10 years old). Yet the SAME code works for the ribbon as it did for custom menu bars.

So, no macros or call backs are needed!

You can call functions directly form the onAction of the ribbon and that code called will be in the current form.

It is a lot less code and hassle then that of ribbon callbacks which must be placed in a public code module and don't travel with the form as above does.

Albert D. Kallal (Access MVP, 2003-2017)
Edmonton, Alberta Canada
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    14th December 2017 - 11:23 PM