2

I have several buttons coded with VBA behind an Access form. I will have a number of forms which will have these buttons in their footer (new, next, prev, duplicate etc). These buttons are not standard macros, but are simple enough to have the same code work across all of my forms.

How might I go about coding these buttons (which have the same names across all forms.. kind of template-like) so I don't have to copy-paste the code in all my forms? Is there a way to write these buttons in a Module and have the form import that module's text (kind of like most other languages)?

Just curious. Makes the code a lot cleaner/easier, as the beef of the programming is my own but others will be making small edits to the form code.

Improve this question

2 Answers 2

Reset to default
3

The simplest thing to do would be to create a Function for each button's functionality in a standard code module. Then change the Click property of each button from [Event Procedure] to the name of the function that corresponds with that button (eg, =MyPrevFunction([Form])).

Note that in order for this to work you must specifically use Function and not Sub. There is no requirement in VBA for a function to return anything, so simply substituting the word Function for Sub is all that is required to make the change.

screen shot of Property Sheet showing =MyButtonFunction([Form]) in On Click event

A picture is worth a thousand words.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

I'd thought about this, but the buttons have extensive use of the "Me" keyword, so I'll have to pass the form to the function all the time. Not difficult. I figured maybe if there were some way to "import" a set of code (like PHP can), I could have one "master script" with one import. VBA isn't quite as flexible I guess, judging by these answers. Thanks!
That's really not any harder. Just call your function like this: =MyPrevFunction([Form]) and the form object will be passed along to your function. I've updated my answer accordingly.
Not hard at all (I'm using MyButtonFunction(Me) to call it), but I still have to write Private Sub myButton_Click() MyButtonFunction(Me) End Sub for 20+ forms and 10+ buttons. Inconvinient and I am lazy, but still a correct solution. Now major code changes take no time at all after the reference set-up
No no no....you're missing the point. No wonder you didn't like the solution. Don't call MyButtonFunction(Me) from inside each form's code module. You call it from the Property Sheet on the form Design view. If you go to Form Design view, select one of your buttons, then press [F4] the property sheet will open. Go to the events tab. The line that says "On Click......" most likely has the following text in it: [Event Procedure]. This is telling Access to go look in the form's code module to find out what to do when the button is clicked. You can bypass that altogether....
....by changing [Event Procedure] to =MyButtonFunction(Form). No code required.
|
2

As well as the idea suggested by @mwolfe02, you can have your buttons in a subform, which means that only one set of buttons need to be edited, or better yet, set up your own custom menus. These can be invoked on a per form basis or as 'standard' menus for your application. You can even have your own right-click menus.

Improve this answer

1 Comment

I'll look into this when I get a little more time; maybe as a simple update. Thanks!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.