Code Reuse

[BarkersIT]

Hello All,

I am new to VBA and have been asked at work to rewrite an Inventory database.

I have designed the tables and then used the form wizard to create the forms. I have then edited the forms so that they include navigation buttons written in VBA.

however, all the forms now have the same code for the buttons. Is there a way/place that I can write the procedure once and then refer to this procedure on each form instead of having the same sub routine many times for each form?

I appreciate this may be an obvious question but being new to VBA i'm unsure about adding VBA code to each individual form or if I should use a module etc.

Any help very much appreciated as I start to learn VBA. Thanks in advance

[OBP]

Yes you can place the Individual sets of code in to general purpose "Modules" where the code can be called from.

Try this little test, on the VBA Editor click "Insert"> Module and then enter this

Public Sub test()
MsgBox "OK"
End Sub

Create a button on your form and in it's On Click" event procedure enter
Call test

[BarkersIT]

I inserted the module with the test sub routine, which if run from the VB editor displays the message box and "Ok".

I understand about the properties of a command button and the On Click proprerty in particular. But what do I write in the On Click line to point to the module and the test routine in particular.

If I click the code builder then it just opens the [Event Procedure] from the form that I had already added the code to.

Again,thanks for help and apologies for my lack of knowledge.

[OBP]

As shown in post #2 you use


Call test

I tested it with the "go to next record" code as well and it worked fine.
You would want to name your "Sub Routines" in the Module with meaningful names like

MoveNext()
MoveFirst()
MoveLast()
etc
Some code may have to stay in the Form's code though, I haven't checked if things like End Of File (EOF) checks will work in the Modules.

[BarkersIT]

That is what I had written but I keep getting error messages about naming of a macro group?

[OBP]

Sorry, I misunderstood your question, the On Click Line should have "Event Procedure" in it.
The "Call Test" goes in the event procedure's "VBA"

[BarkersIT]

I get ya. yeah that works. So I just make calls to the single method in the module from the buttons on the form.

Awsome. Thanks alot. That should make it look a lot neater.