Using variable to call function

[fredk]

Is it possible to use a variable name to call a function- In the following example, I am trying to use the strSACName variable to call a function - The function that will be called will depend on the username... since each user has different assignments I am tailoring each function to the user.

The function will set the query criteria for me depending on who the user is.

Please be kind if I am way off base here.

When I use the following and use the specific function name I am ok - However, when I replace the function name with the variable "strSACName" I get a compile error saying "expected array"

Sorry if this looks sloppy but I am learning the vba side.



Private Sub cmdRunRpt_Click()
Dim strSACName As String
Dim strUser As String
Dim lngOptionValue As Long
Dim lngX As Long
Dim strRestrict As String


strUser = GetUserName
strSACName = strUser & "Brokers"
'assign the value of the option group to lngX
lngX = Forms!SACSpecific!SACBrokerRun.Value
'use getusername function to get windows user name
strRestrict = strSACName(lngX)

End Sub

Thank you very much!!!!

 

[GoodOmens]

Change your function call to

strRestrict = Application.Run(strSACName, lngX)

 

[fredk]

Thank you very much for your help GoodOmens!!! I appreciate it!!

Fred

 

[danvlas]

...or:
strRestrict = Eval(strSACName & "(" & lngX & &quot&quot

Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[fredk]

Thanks Danvlas - I will look up eval (I am guessin a function in the help to get more - I appreciate it!!!!

Fred

 

[fredk]

Can I please ask one follow-up question- I am trying to set the criteria of the query using code - I am ok until I have to use more than one result - For example, if I want the criteria to be only one name I am ok - However, if I want an "all" field I am running into problems - For example:

This code works all but the Case All - Because the criteria is not correct when entered in the query - How do I correct this so the query reads it as "Dascit" or "TRA" etc ?????

Thank you very much!!!!

Select Case lngOptionGroupValue
Case Dascit:
strTest = "Dascit"
Case TRABenefits:
strTest = "TRA"
Case MidMonmouth:
strTest = "Mid"
Case FotekWalsh:
strTest = "Fot"
Case DirectSouth:
strTest = "Direct"
Case All:
strTest= "Dasict or TRA or Mid or Fot or Direct "
End Select

 

[GoodOmens]

I would need to see your query statement to be sure but I think Case All: should read something like
"'Dasict' or [valueTesting]='TRA' or [valueTesting]='Mid' or [valueTesting]='Fot' or [valueTesting]='Direct'"

Or, if you do not need to restrict to those values you could use
"*"
as long as your query WHERE clause uses a
"[valueTesting] LIKE '" & strTest & "'"
and not
"[valueTesting]='" & strTest & "'"

 

[fredk]

Thanks GoodOmens - I have gotten sidetracked off of this. Hopefully, I will be able to get to it this week - I do need to restrict to just those values - I appreciate you responding!!!!!

Thanks for your help!!!

Fred