AngusT
asked on
Calling a variable VBA procedure from a string in an Access Database
I have an Access database and I'm currently a dynamic switchboard menu system, which has a table to lookup which report, form,to run and then run it. Its fairly simple by using Docmd. Openreport or openform, with rhe report or form from the table run as a parameter.
I find myself in the situation where I need to be able to call a VBA function (with parameters) from this menu. is there the equivalent of a docmd.openreport equivalent which allows the calling of procedures. I'm familiar with the macro runcode, but I'm not sure if/how it is possible to pass arguments to it dynamically.
I find myself in the situation where I need to be able to call a VBA function (with parameters) from this menu. is there the equivalent of a docmd.openreport equivalent which allows the calling of procedures. I'm familiar with the macro runcode, but I'm not sure if/how it is possible to pass arguments to it dynamically.
Hello AngusT,
You cannot pass a function name dynamically. However you won't need that kind of flexibility for a switchboard. If your table is now:
ID Action Parameter
1 frm frmBuildKite
2 frm frmPaperPlane
3 rpt rptFlyKite
Just add a record
4 vb1 Beach
and amend the corresponding code. E.g.
Select Case Action
Case "frm"
DoCmd.OpenForm Parameter
Case "rpt"
DoCmd.OpenForm Parameter
Case "vb1"
MyAction1 Parameter
End Select
And of course:
Function MyAction1(strArg As String)
MsgBox "Let's go to the " & strArg
End Function
Does that help?
(°v°)
You cannot pass a function name dynamically. However you won't need that kind of flexibility for a switchboard. If your table is now:
ID Action Parameter
1 frm frmBuildKite
2 frm frmPaperPlane
3 rpt rptFlyKite
Just add a record
4 vb1 Beach
and amend the corresponding code. E.g.
Select Case Action
Case "frm"
DoCmd.OpenForm Parameter
Case "rpt"
DoCmd.OpenForm Parameter
Case "vb1"
MyAction1 Parameter
End Select
And of course:
Function MyAction1(strArg As String)
MsgBox "Let's go to the " & strArg
End Function
Does that help?
(°v°)
ASKER
It doesn't really...I already have something like the below (heavily simplified):
Private Sub MenuSelect(intmnuitem As Integer)
Select Case intAction
Case 1 'Open report
DoCmd.OpenReport strParameter
Case 2 'Open forms
DoCmd.OpenForm strParameter
Case 3 'Run Function
End Select
End Sub
Function TransferXL() As Boolean
Dim objComDlg As New ComDlg
Dim strquery As String
strquery = "qryEstimateSubteamPortfol
'Choose filename and location to save report into
With objComDlg
.DialogTitle = "Save as"
.Extension = "xls"
.Filter = "Microsoft Excel Workbooks *.xls"
If Not .ShowSave Then
Else
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strquery, .FileName
MsgBox .FileName & "has been created successfully.", vbInformation, "Microsoft Excel Spreadsheet Export"
End If
End With
TransferXL = True
End Function
Private Sub MenuSelect(intmnuitem As Integer)
Select Case intAction
Case 1 'Open report
DoCmd.OpenReport strParameter
Case 2 'Open forms
DoCmd.OpenForm strParameter
Case 3 'Run Function
End Select
End Sub
Function TransferXL() As Boolean
Dim objComDlg As New ComDlg
Dim strquery As String
strquery = "qryEstimateSubteamPortfol
'Choose filename and location to save report into
With objComDlg
.DialogTitle = "Save as"
.Extension = "xls"
.Filter = "Microsoft Excel Workbooks *.xls"
If Not .ShowSave Then
Else
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strquery, .FileName
MsgBox .FileName & "has been created successfully.", vbInformation, "Microsoft Excel Spreadsheet Export"
End If
End With
TransferXL = True
End Function
ASKER
Sorry, the above coment was accidentally posted before it was finished and I dot' know how to delete it. Ignore that last comment.
Here's the revised version:
It doesn't really...I already have something like the below (heavily simplified):
I have a form with a list box of menu items, which are stored in a table. Each is given a type id (representing form, report, etc) . its a bit more complex than that, but those aspects aren't relevant.
On the dblclick event it runs the below procedure, taking that type id as the argument.
Private Sub MenuSelect(intmnuitem As Integer)
Select Case intAction
Case 1 'Open report
DoCmd.OpenReport strParameter
Case 2 'Open forms
DoCmd.OpenForm strParameter
Case 3 'Run Function
'Trying to figure out how to do this
End select
I don't want to hardcode which reports/functions to call, since the menu items change quite often and to hardcode the running of them into forms means I can only add those items with exclusive access, and is generally messier.
It would be great if I could figure out something which would be equivalent to Docmd.RunFunction method which would call a procedure named by the string argument, but as you suggested, I dont think it exists.
I already know of other less dynamic options, but I'm loathe to use them if I don't have to.
Here's the revised version:
It doesn't really...I already have something like the below (heavily simplified):
I have a form with a list box of menu items, which are stored in a table. Each is given a type id (representing form, report, etc) . its a bit more complex than that, but those aspects aren't relevant.
On the dblclick event it runs the below procedure, taking that type id as the argument.
Private Sub MenuSelect(intmnuitem As Integer)
Select Case intAction
Case 1 'Open report
DoCmd.OpenReport strParameter
Case 2 'Open forms
DoCmd.OpenForm strParameter
Case 3 'Run Function
'Trying to figure out how to do this
End select
I don't want to hardcode which reports/functions to call, since the menu items change quite often and to hardcode the running of them into forms means I can only add those items with exclusive access, and is generally messier.
It would be great if I could figure out something which would be equivalent to Docmd.RunFunction method which would call a procedure named by the string argument, but as you suggested, I dont think it exists.
I already know of other less dynamic options, but I'm loathe to use them if I don't have to.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry. Perhaps I oversimplified my example to the point where it was a bit vague. Nevertheless Application.Run was exactly what I was looking for.
Thanks bigtime.
Thanks bigtime.
Should have thought of that. Thanks for stepping in and saving the day, Leigh!
(°v°)
(°v°)
lol Stepping back out of the phone box wearing a shirt, tie and a cunning disguise consisting of glasses without lenses in them.
My secret identity remains so. :-)
(Hmm. Actually I prefer Spiderman. Am sure I could squeeze a "web" pun in there somewhere :-S)
My secret identity remains so. :-)
(Hmm. Actually I prefer Spiderman. Am sure I could squeeze a "web" pun in there somewhere :-S)
b. Add InputBox actions to your procedure for the variables.