Sending a variable between functions

  • Thread starter Thread starter Sue Cardwell via AccessMonster.com
  • Start date Start date
S

Sue Cardwell via AccessMonster.com

I am building a database and I have a rather large "Select Case" statement.
I have teachers enter a course their students have completed, the function
looks up the correct control and then places the information in that control.
Because the "Select Case" statement is so long, I would like to have one
function that contains the "Select Case" statement and then pass information
back and forth. I can pass a variable from the original function to the
"Select Case" function, but I don't know how to send the variable back to the
original function or even if you can. Can anyone help me?
 
If I understand your question correctly that would be done somewhat like this:

Sub OriginalSub(ByVal myParameter)
valueFromFunction = FuntionToPassValue(ByVal myOtherParameter)
End Sub

Function FunctionToPassValue(ByVal myOtherParameter) As Variant
....Your Select Case block...
FunctionToPassValue = myResultValue
End Function

"Sue Cardwell via AccessMonster.com" schreef:
 
There is no need to do anything to "send it back."

By default, VBA passes variables by reference, i.e. the child process has
the real variable, and can change it. When the child process ends and
control returns to the main procedure, it has the changed value.

If you do not want that behavior, you can pass the variable by value
instead, i.e. the child process only gets the value - a copy of the
variable - not the real thing. To do that, add the ByVal keyword in the
function declaration, e.g.:

Function MyFunc(ByVal SomeVariable)
 
Sometimes a large Case statement can be replaced by shorter code that
looks up stuff in a table. This has the advantage that a new case, or a
change in an existing one, just requires an edit to the table rather
than editing and testing code.

E.g. if you have a lot of stuff

Select Case Course
Case "Carpentry"
ControlName = "XXX"
Case "Brazing"
ControlName = "YYY"
...
End Select
Me.Controls(ControlName).Value = SomeVariable

you can replace it with a table
Course ControlName
Carpentry XXX
Brazing YYY

and a call to DLookup.
 
That's a great idea. The only problem I can see is that I'm having a
terrible time with DLookUps. Don't know why. I will try again with the
DLookUp in this database and see what happens. Thanks for the suggestion.

John said:
Sometimes a large Case statement can be replaced by shorter code that
looks up stuff in a table. This has the advantage that a new case, or a
change in an existing one, just requires an edit to the table rather
than editing and testing code.

E.g. if you have a lot of stuff

Select Case Course
Case "Carpentry"
ControlName = "XXX"
Case "Brazing"
ControlName = "YYY"
...
End Select
Me.Controls(ControlName).Value = SomeVariable

you can replace it with a table
Course ControlName
Carpentry XXX
Brazing YYY

and a call to DLookup.
I am building a database and I have a rather large "Select Case" statement.
I have teachers enter a course their students have completed, the function
Click to expand...
[quoted text clipped - 4 lines]
"Select Case" function, but I don't know how to send the variable back to the
original function or even if you can. Can anyone help me?
Click to expand...
Click to expand...
 
Back
Top