You can create a function in a standard module and declare it as Public so it
can be called from anywhere in the database. If the code currently in the
form's module contains a reference to the current form using Me (strictly
speaking this is a reference to the current instance of the class, but think
of it as reference to the form) then you'll need to change this in the public
function. Similarly any references to a control on the form simply by the
control's name will need changing so that the function knows which control on
what form is referred to.
You can reference the form in a number of ways. One would be to include a
full reference to the form in the function; another would be to pass a
refernce to the form into the function as an argument.
I think the best way to illustrate this would be with a simple example.
Lets say you have an event procedure in a form's module which adds the values
in two controls together and assigns the result to another control:
Private Sub Form_AfterUpdate()
Dim dblTotal as Double
dblTotal =txtFirstNumber + txtSecondNumber
Me.txtTotal = dblTotal
End Sub
Note that I've used me when referencing the txtTotal control but not when
referencing the txtFirstNumber and txtSecondNumber controls. This is purely
to show how Me can be included or omitted in code in a form's module when
referencing controls; it could equally well have been included or omitted
when referencing any of them.
To do the same thing in a function in a standard module, including a full
reference to the form in the code:
Public Function AddNumbers()
Dim dblTotal as Double
dblTotal =Forms("MyForm").txtFirstNumber + Forms("MyForm").txtSecondNumber
Forms("MyForm").txtTotal = dblTotal
End Sub
This would be called from any code in the database simply by entering the
following line:
AddNumbers
To do it by passing a reference to the form into the function as an argument:
Public Function AddNumbers(frm As Form)
Dim dblTotal as Double
dblTotal =frm.txtFirstNumber + frm.txtSecondNumber
frm.txtTotal = dblTotal
End Sub
In this case the reference to the form would be passed to the function when
its called like so:
AddNumbers Forms("MyForm")
If the function is called from within MyForm's module you can use Me to
refer to the form as the argument:
AddNumbers Me
We can take this further and pass the references to all three controls into
the function:
Public Function AddNumbers(ctrl1 As Control, _
ctrl2 As Control, _
ctrl3 As Control)
Dim dblTotal as Double
dblTotal =ctrl1 + ctrl2
ctrl3 = dblTotal
End Sub
The underscore character _ is used here as a continuation character,
enabling us to write a single line over several lines for better readability.
In this case the references to the three controls be passed to the function
when its called like so:
AddNumbers Forms("MyForm").txFirstNumber, _
Forms("MyForm").txtSecondNumber, _
Forms("MyForm").txtTotal
In the above cases the form MyForm has to be open when the form is called of
course.
So far we've only used the function to carry out some action, adding two
numbers together and assigning the result to a control). For this a sub
procedure could have been used in exactly the same way. A function, however,
can return a value, so we could have used it to return the sum of the two
numbers:
Public Function AddNumbers(ctrl1 As Control, _
ctrl2 As Control) As Double
Dim dblTotal as Double
dblTotal =ctrl1 + ctrl2
AddNumbers = dblTotal
End Sub
The return value could then be assigned to the third control in some other
code with:
Forms("MyForm").txtTotal = _
AddNumbers Forms("MyForm").txtSecondNumber, _
Forms("MyForm").txtTotal
Also values passed into functions can be changed by the function. If we add
the following function to a module:
Public Function ChangeNumbers(intFirst As Integer, intSecond As Integer)
intFirst = intFirst * 2
intSecond = intSecond * 3
End Function
And call it like so:
Dim FirstNumber As Integer, SecondNumber As Integer
FirstNumber = 2
SecondNumber = 3
ChangeNumbers FirstNumber, SecondNumber
MsgBox "First:" & FirstNumber & _
vbNewLine & "Second:" & SecondNumber
We'll find that the message box reads:
First:4
Second:9
The function has changed the values of the variables, multiplying them by 2
and 3 respectively. This is because by default the arguments passed into the
function are ByRef, i.e. a reference to the original variables, so these are
changed by the function. We might or might not want this to happen, however;
if not the arguments should be passed ByVal (by the values of the variables,
not references to them), so if the function is changed to:
Public Function ChangeNumbers(ByVal intFirst As Integer, ByVal intSecond As
Integer)
intFirst = intFirst * 2
intSecond = intSecond * 3
End Function
the message box will now read:
First:2
Second:3
i.e. the original values remain unchanged.
I hope the above helps you understand how functions work, but if there's
anything you need clarifying let me know.
Ken Sheridan
Stafford, England
