I want to launch a VB module in Access 2007 via a marco

dave_sing
dave_sing used Ask the Experts™
on
I want to launch a VB module in Access 2007 via a marco. the marco would start a VB module and when the module completed its work, the marco would provide a message and execute 2 queries then another VB module. thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You do not execute a module.
You execute a procedure.

Within an Access macro you can use the RunCode action to call a Function procedure.  You cannot call a Sub procedure using RunCode.
You just enter the function name in the RunCode macro.
MyfunctionName()
Top Expert 2011

Commented:
I would add that I find it better to use  VBA procedure to do this. This way you can use sub's , error handling that a macro does not provide.

Author

Commented:
I'll give it a try tomorrow. on my way home from work.
thanks
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

RomoloIT Pro, Projects, Mentor, Trainer
Commented:
Typically you will run a private sub procedure on a button or load of a form.

So a procedure to run on the click of a button to say hello world and load a query etc..

Private Sub MYBUTTONNAME_Click()

     'Run message box

     msgbox ("Hello World")

      'Run a query

     Docmd.Openquery "MYQUERYNAME"

Exit Sub

Queries can be update/append/delete statements.. or even SELECT.. however.. select would be on a form or a report really wouldn't it.

R
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
dave_sing

The problem with your logic is that it assumes that code will complete successfully.
What if it doesn't?

My guess is that if the code fails for some reason, you do not want to then run the queries.
I will also presume that you would like to inform the user of this.

For these reasons I would also recommend using VBA, not Macros for this.

My suggestion?
Create a function that determines if the code ran successfully.
Based on the result of this function, you would either notify the user that the code failed and exit the code (not running the queries)
Or (if the code did run successfully), simply go on to run the queries...

JeffCoachman
MIS Liason
Most Valuable Expert 2012
Commented:
Something "Roughly" like this:

On your form button that runs this system:

'Code Start
Private Sub btnCodeRuns_Click()
   
    Call RunCode
    If blnpubCodeRan = False Then
        Exit Sub
    End If
   
    MsgBox "Code completed, ...proceeding to run Queries.", vbInformation
    'Your Run Query Code here

End Sub
'Code End


In a Module:

'Code Start
Public Function RunCode()
On Error GoTo Err_RunCode

    'This code will run without issue
'    Forms!Form1.SetFocus
'    blnpubCodeRan = True
   
    'This code will fail and generate the error
'    DoCmd.RunCommand acCmdAlignBottom

Exit_RunCode:
    Exit Function

Err_RunCode:
    blnpubCodeRan = False
    MsgBox "There was an error executing the code, ...exiting code.", vbCritical
    Resume Exit_RunCode
   
End Function
'Code end


Now un-commenting each code snippet in the public function will either generate the error, or simply run through the complete system.

There are many similar ways to do this (obviously), I just threw this together quickly to illustrate the logic.

Sample attached
Play around with it.


JeffCoachman

db353.mdb

Author

Commented:
thank yo to all for providing me with multiple solutions and rationale for using each method.
RomoloIT Pro, Projects, Mentor, Trainer

Commented:
Your Welcome :)

R

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial