Calling a module from a command button

kklewis

New Member
Joined
Dec 5, 2005
Messages
17
I have written a module in Access VBA that creates a query. It is written in the "modules" section. It runs correctly when Im in VBA. I created an "event procedure" by:
right clicking on the command button, then selecting properties and under the "on click" field I double clicked the "..." button which brough up VBA and the following code:
Private Sub PullData_Query1_Click()

End Sub

This code is not in the module section, but as code behind the form where my command button is located.
I put my module name in between the first and last line, but when i try and run the query from the command button itself I get the following message: "Sub of function not defined". If I copy the code below the code for the button it runs fine. The only problem is that I run this module on mulitple sheets and I don't want to have to keep copying and pasting the module to each sheet. Is there any way to trouble shoot this? Thanks.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,275
Well for a start you don't run or call modules.

Modules are containers for subs/functions etc.

What you need to run/call is the sub.
 

kklewis

New Member
Joined
Dec 5, 2005
Messages
17
Sorry ... im new to VBA and lacking the vocabulary.
I am trying to call a sub that is saved in a module from the code for a command button which is in the forms section (Class Objects). How can I save one sub in a module, but call it using command buttons?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,275
Instead of the name of the module put the name of the sub in the code above.
 

kklewis

New Member
Joined
Dec 5, 2005
Messages
17
I was calling the sub, not the module.

I have a sub called CRPullData saved in the module named "Module1". The first line of the sub is "Private Sub Analysis_Results_Click()". In the Class Objects section I have the code for the command button, which is located on the PullDataForm. The code is in Form_PullDataForm and looks like this:
Private Sub PullData_Query1_Click()
CRPullData
End Sub

This code runs fine if the CRPullData sub is copied below this code, but does not run (get error message "Sub or Function not defined") if the sub is saved in Module1.

Is there a away to call a sub from a module?
Thanks.
 

kklewis

New Member
Joined
Dec 5, 2005
Messages
17
I was calling the sub, not the module.

I have a sub called CRPullData saved in the module named "Module1". The first line of the sub is "Private Sub Analysis_Results_Click()". In the Class Objects section I have the code for the command button, which is located on the PullDataForm. The code is in Form_PullDataForm and looks like this:
Private Sub PullData_Query1_Click()
CRPullData
End Sub

This code runs fine if the CRPullData sub is copied below this code, but does not run (get error message "Sub or Function not defined") if the sub is saved in Module1.

Is there a away to call a sub from a module?
Thanks.
 

mdmilner

Well-known Member
Joined
Apr 30, 2003
Messages
1,352
First thing I'd ask is - is there any chance the function/subroutine that you're calling is set to a local scope or that the default is PRIVATE?

Routines in a different module are only accessible if they are set PUBLIC (there are other keywords like Global but Public is the most commonly used).
On the off chance that the default is set to PRIVATE, try explictly putting the keyword PUBLIC ahead of the routine.

Second - you could also try the syntax:

Call routineNameHere

And yes, you can call a Sub or Function with identical syntax, doesn't matter. The main difference is that a Function is able to return a variable(s) back to the calling routine. It's possible to pass objects (which includes things like arrays)

The need to use Private/Public is actually part of a concept known as Encapsulation which is a key component of Object Oriented code design. VB in general has sort of a half-azzed implementation of it with it's Classes and Modules, but the principle is still the same...I really can't do justice to everything under this topic without writing a massive article.
 

kklewis

New Member
Joined
Dec 5, 2005
Messages
17
Thanks for your help ... the problem was that the sub's were Private. I set them to public and the command buttons work perfectly now.
 

Forum statistics

Threads
1,077,917
Messages
5,337,163
Members
399,129
Latest member
ericosman

Some videos you may like

This Week's Hot Topics

Top