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?
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?
ASKER
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.
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.
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.
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.
ASKER
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.
You need a function (or in this case it should be a sub-routine) that when passed the parameters will do the necessary actions.
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Generally I highly recommend staying away from macros, it make maintenance so much more complicated.
One way or another, every place you use this, your going to have to write a small bit to get it done.
Jim.