Results 1 to 8 of 8

Calling a module from a command button

This is a discussion on Calling a module from a command button within the Microsoft Access forums, part of the Question Forums category; I have written a module in Access VBA that creates a query. It is written in the "modules" section. It ...

  1. #1
    New Member
    Join Date
    Dec 2005
    Posts
    17

    Default Calling a module from a command button

    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.

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    66,050

    Default

    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.
    If posting code please use code tags.

  3. #3
    New Member
    Join Date
    Dec 2005
    Posts
    17

    Default

    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?

  4. #4
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    66,050

    Default

    Instead of the name of the module put the name of the sub in the code above.
    If posting code please use code tags.

  5. #5
    New Member
    Join Date
    Dec 2005
    Posts
    17

    Default

    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.

  6. #6
    New Member
    Join Date
    Dec 2005
    Posts
    17

    Default

    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.

  7. #7
    Board Regular
    Join Date
    Apr 2003
    Location
    Jacksonville, FL
    Posts
    1,344

    Default

    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.

  8. #8
    New Member
    Join Date
    Dec 2005
    Posts
    17

    Default

    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.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com