Microsoft Access 2010 VBA - use a macro to pass parameters to VBA

I have this code:
Function store_PDF(rpt As Report, JobNumber As Integer)
Dim folderLoc, Msg, directory As String
Dim file_location$
Dim sendFile As Boolean
sendFile = False
file_location = "C:\test\" & JobNumber & "\"
If sendFile = False Then
    ChooseFile rpt, file_location
    If Msg = "" Then Exit Function
    If Right(Msg, 1) <> "\" Then Msg = Msg & "\"
    file_location = Msg
    sendFile = True
End If
If sendFile Then
    create_pdf rpt, file_location
End If
End Function

which is called by this event on a form:
Private Sub btn_createPDF_Click()
store_PDF Me, Me.Job_ID
End Sub

But, I would like the above event code (btn_createPDF) to be replaced by a macro. Then I can call that macro by using a button and easily replicate it throughout the system.

How do I replace the event code with a macro, and still pass the parameters please?
Rick DangerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I don't see how a macro is going to help.   You need to "hook up" something to the function (to get the parameter values) whether you use a macro or VBA.

 One way or another, every place you use this, your going to have to write a small bit to get it done.

Jim.
0
Rick DangerAuthor Commented:
Jim
A macro would pass the parameters to the VBA code. I just wanted the event VBA above (one line) to be converted to a macro.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
There is no way to do what you want.   You can't pass arguments to a macro.

However you can call the function directly from the property page just as you would call a macro.

Just put an ='s sign in front of it:

=store_PDF(Me, Me.Job_ID)

Jim.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Rick DangerAuthor Commented:
I don't want to pass code to a macro, I want pass the 2 parameters from a macro to the VBA code at the top of my question.
0
MacroShadowCommented:
The ideal way to do what you need, is exactly the way you're doing it now.

You need a function (or in this case it should be a sub-routine) that when passed the parameters will do the necessary actions.
0
Rick DangerAuthor Commented:
MacroShadow
Yes, but I want to do it using a macro, which will pass the parameters to the VBA code.

When I create a macro, and select RunCode from the list of available actions, I select my code "create_pdf" as above and it gives me the option to add parameters.

I just don't know what to enter.
0
MacroShadowCommented:
Try
Forms![form name]
and
Forms![form name]!Job_ID

for the first and second parameters respectively.

Don't know if it will work but it's worth a shot.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<I don't want to pass code to a macro, I want pass the 2 parameters from a macro to the VBA code at the top of my question. >>

 I understand what your asking, but your not understanding what I'm saying:

1. You cannot pass arguments to a macro.

2. In your macro, the best you can do is reference something, like a form control.  That reference would be "hard coded".   You can't change it on the fly.

  So you could open a hidden form, put two controls there, one for the report and one for the ID.  Then from your form, fill those controls in at some point.   Then call the macro.  The macro would look at that hidden form and those controls and pass those to the function.

  But why even bother?  You could just modify the procedure to read those controls on the hidden form directly and forget about the macro.

 But even with that, where it leaves you is that:

1. Your re-locating your current click logic to someplace else in the form.
2. Your calling the macro from the property sheet, which in turn is calling the procedure (if you stick with the macro).
3. or your still having to call the procedure directly either from the property sheet or in a click procedure..

  What your currently doing is pretty much the simplest.  The only way to make that simpler is to call the procedure from the property sheet directly.  So instead of:

[Event Procedure]

You will have:

=store_PDF(Me, Me.Job_ID)

Your not going to make it simpler by insisting that you need to use a macro.  Without a macro, the code is reusable and callable from everywhere already (assuming you've got this in a standard module).

Using/calling a macro buys you nothing.

Jim.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MacroShadowCommented:
Generally I highly recommend staying away from macros, it make maintenance so much more complicated.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.