Solved

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

Posted on 2014-02-27
9
2,282 Views
Last Modified: 2014-02-27
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?
0
Comment
Question by:rick_danger
  • 3
  • 3
  • 3
9 Comments
 
LVL 57
ID: 39891552
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
 

Author Comment

by:rick_danger
ID: 39891557
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
 
LVL 57
ID: 39891573
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:rick_danger
ID: 39891576
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
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39891578
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
 

Author Comment

by:rick_danger
ID: 39891589
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
 
LVL 27

Assisted Solution

by:MacroShadow
MacroShadow earned 250 total points
ID: 39891615
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
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
ID: 39891715
<<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
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39891724
Generally I highly recommend staying away from macros, it make maintenance so much more complicated.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question