[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2014-02-27
9
Medium Priority
?
2,525 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
Industry Leaders: 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 1000 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 1000 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

Industry Leaders: 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!

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
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 …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

649 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