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?
Who is Participating?
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor PresidentCommented:
<<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 Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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.

rick_dangerAuthor Commented:
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.
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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)

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.
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.
rick_dangerAuthor Commented:
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.
MacroShadowConnect With a Mentor Commented:
Forms![form name]
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.
Generally I highly recommend staying away from macros, it make maintenance so much more complicated.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.