Call Function in Access VBA.

[Thomas]

I have a form with multiple option buttons on it. Each option has a
corrosponding function that will run if it is selected and a command button
is clicked. I normaly would do it as an If statement but due to the large
number of options it is making the code a bit unwieldy. I have each function
named Create_Single and then the option number (example: Create_Single1,
Create_Single2, Create_Single3, etc).
Each number ending the function name corrosponds to the frame value (1, 2,
3, etc). The code I have tried is as follows:

'Declare Variables
Dim Sel As Integer
Dim Report As Variant

'Determine which opton was selected.
Sel = fra_ReportSelectionsSingle.Value
Report = "Create_Single" & Sel
Call Report

This of course gives an error stating that the function is not defined as it
appears that it is trying to call a function called "Report" rather than
using the variable value for the function name. Anyone have any ideas or is
an If statement going to be the only option for this?

 

[Clif McIrvin]

I have a form with multiple option buttons on it. Each option has a
corrosponding function that will run if it is selected and a command
button
is clicked. I normaly would do it as an If statement but due to the
large
number of options it is making the code a bit unwieldy. I have each
function
named Create_Single and then the option number (example:
Create_Single1,
Create_Single2, Create_Single3, etc).
Each number ending the function name corrosponds to the frame value
(1, 2,
3, etc). The code I have tried is as follows:

'Declare Variables
Dim Sel As Integer
Dim Report As Variant

'Determine which opton was selected.
Sel = fra_ReportSelectionsSingle.Value
Report = "Create_Single" & Sel
Call Report

This of course gives an error stating that the function is not defined
as it
appears that it is trying to call a function called "Report" rather
than
using the variable value for the function name. Anyone have any ideas
or is
an If statement going to be the only option for this?

Thomas, I have two thoughts ... neither a direct answer to your
question:

(I have no idea if there is a method to create a function name 'on the
fly' or not.)

1. Would Select Case be a simpler construct than IF Then?

2. Try posting your question at microsoft.public.access.forms where
there are a number of VBA / Access experts active.

 

[Karl E. Peterson]

'Determine which opton was selected.
Sel = fra_ReportSelectionsSingle.Value
Report = "Create_Single" & Sel
Call Report

This of course gives an error stating that the function is not defined as it
appears that it is trying to call a function called "Report" rather than
using the variable value for the function name. Anyone have any ideas or is
an If statement going to be the only option for this?

Take a look at the CallByName function in your helpfile. Not ideal, but if typing's
not your idea of fun, this might taste better.

 

[AndyM]

Rather than a long If statement, you can use Application.Run(functionName).
This will allow you to use a variable when calling a function.

For your example, the code may look like the following:
Application.Run("Create_Single" & optNum)

 

[Thomas]

Unfortunately it doesn't work and gives me an error stating "Database can't
find the prcedure 'Create_Single3.'

The code I am using to call the function is:

Private Sub cmd_RunReportSingle_Click()
'This sub will determine what report option was selected and will run the
Function
'to create that report.

'Declare Variables
Dim Sel As Integer
Dim Report As String

'Determine which opton was selected.
Sel = fra_ReportSelectionsSingle.Value
Report = "Create_Single" & Sel
Application.Run Report

End Sub 'cmd_RunReport_Click()

The function I am having it refer to at this time is:

Private Function Create_Single3()

MsgBox "3 Selected", vbInformation, "3 Selected"

End Function 'Create_Single3()

 

[AndyM]

Change the Create_Single3 function from Private to Public. It needs to be
Public in order to be called by Application.Run.

 

[Thomas]

No effect, still gives the same error. I am using Access 2002. I wonder if
that version is not capable of this.

 

[Clif McIrvin]

No effect, still gives the same error. I am using Access 2002. I
wonder if
that version is not capable of this.

Where are your Create_Single1...n functions located? I 'think' they need
to be in a standard code module, not a class (form) module for
Application.Run. If that doesn't solve your problem, I'd suggest
re-posting in microsoft.public.access ... I see a lot of coding
discussion in that group.

 

[Curt]

Try using...

Call eval(report)

This evaluates the variable before and should call the function. when
setting the value for report i add the parenthesis.. e.g. report =
Myfunction()

As well this only works for me if the function being called is in a module
and not part of the code module on the form..