Solved

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

Posted on 2014-02-27
9
2,385 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
9 Comments
 
LVL 58
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 58
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 58

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

717 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