This is a training exercise for me to learn how to:
* Connect to tables and process data
[quoted text clipped - 3 lines]
I would really appreciate the help
Here is what I consider some important advice for writing
functions/subs:
1. don't create a function unless you want to return a value.
Otherwise, create a sub. This is contrary to Allen Browne's advice
-- he says to create everything as a function, but I find that
confusing, as a function that returns nothing looks to me like a
design error (it actually does return something, a Null variant,
which is pretty useful).
2. strongly type the input parameters and the output value. Do this:
Public Function NameFromID(lngPersonID As Long) As String
Don't do this:
Public Function NameFromID(lngPersonID)
By default, a variable without a data type defined is a Variant.
Variants are extremely valuable in certain contexts, but when you
are always passing the same value and don't want to accept a Null
(in this case you'd be passing a PK, which can never be Null, so
there's no utility in using Variant data type for it).
Likewise, a function with an undefined return type returns a
Variant, and if you read the help file on the Variant data type,
you'll see that it's very complicated (because Variants have
sub-types that you can't really control -- they are assigned
implicitly by VBA), and you should avoid it using it except
completely intentionally.
3. some people use the same naming conventions for functions that
they use for their variables, e.g., Leszynski/Reddick, or Hungarian
notation. So, a function returning a Long Integer would be named
starting with "lng", and a string function with "str". I have done
this sometimes, but I find that it makes using Intellisense more
complicated, as you then have to type "str" before you get to a
possible match.
4. on all coding, be sure you choose your scope appropriately. Any
function in a standalone module is public by default, but I think
it's best to be completely explicit about this. In a standalone
module, this is public:
Function NameFromID(lngPersonID As Long) As String
It's also public by default in a class module attached to a form or
report. In a standalone module, that's likely what you want, but in
a form or report, it's fairly unusual to have a public function.
It's best to always specify scope:
Public Function NameFromID(lngPersonID As Long) As String
...is for all the functions you want visible outside the module
where it's defined, and:
Private Function NameFromID(lngPersonID As Long) As String
...is for all functions you want private to the specific module
where they are defined. My default for standalone modules is Public
and for form/report modules Private.
5. it's a good idea to understand passing values By Value and By
Reference. By default, VBA passes By Reference. This means that a
pointer to the memory location where the variable/object being
passed is stored is handed off to the function/sub you're calling.
Public Function NameFromID(lngPersonID As Long) As String
...is equivalent to:
Public Function NameFromID(ByRef lngPersonID As Long) As String
... what it means when you call it:
Dim strName As String
strName = NameFromID(Me!PersonID)
...is that a pointer to the memory location of Me!PersonID is
passed. If you pass a control reference:
strName = NameFromID(Me!cmbPerson)
...an implicit reference to the cmbPerson combo box is created and
passed. This can lead to problems that are very difficult to
troubleshoot. So, basically, you should try to use ByRef only when
you really need it. Those situations would be:
1. when you really want to pass an object:
Public Function ShowHideControls(ByRef col As Collection, _
bolVisible As Boolean) As Boolean
Dim ctl As Control
For Each ctl In col
ctl.Visible = bolVisible
Next ctl
Set ctl = Nothing
ShowHideControls = True
End Function
You'd use that thus:
If Not ShowHideControls(Me.Controls, True) Then
MsgBox ("Something went wrong showing/hiding controls!")
End If
The reason you pass By Reference is because you want to operate on
the thing that you've passed within the function/sub itself.
This applies to simple variables and is a useful technique when you
want to return multiple values:
Public Sub GetFormColors(ByRef lngBackground As Long, _
ByRef lngLabel As Long, ByRef lngControlBackground As Long, _
ByRef lngControlText As Long)
lngBackground = -2147483633 ' system color
lngLabel = 0 ' black
lngControlBackground = 16777215 ' white
lngControlText = 0 ' black
End Sub
When you call it, you'd do something like this:
Dim lngFormBackground As Long
Dim lngLabelColor As Long
Dim lngTextBoxBackground As Long
Dim lngTextBoxForeground As Long
Dim ctl As Control
Call GetFormColors(lngFormBackground, lngLabelColor, _
lngTextBoxBackground, lngTextBoxForeground)
Me.Section(0).Background = lngFormBackground
For Each ctl In Me.Controls
Select Case ctl.ControlType
Case acLabel
ctl.ForeColor = lngLabelColor
Case acTextBox, acComboBox, acListBox
ctl.BackColor = lngTextBoxBackground
ctl.ForeColor = lngTextBoxForeground
End Select
Next ctl
Set ctl = Nothing
What you've done is make one call to the subroutine, passing your
local variables, which get values assigned to them that are then
usable after the subroutine has finished.
I often use a function for code that executes a SQL
update/insert/delete, and have the function return the
RecordsAffected value (i.e., how many records were
updated/inserted/deleted). However, I also frequently pass a
variable for the same value so I can use it in code differently:
Public Function SetPersonUpdated(dteDate As Date, _
lngUpdateCount As Long) As Long
On Error GoTo errHandler
Dim db As DAO.Database
Dim strSQL As String
Set db = CurrentDB
strSQL = "UPDATE tblPerson SET tblPerson.Updated = #"
strSQL = strSQL & _
DateSerial(Day(dteDate), Month(dteDate), Year(dteDate)
strSQL = strSQL & "#;"
db.Execute strSQL, dbFailOnError
lngUpdateCount = db.RecordsAffected
SetPersonUpdated = lngUpdateCount
exitRoutine:
Set db = Nothing
Exit Function
errHandler:
MsgBox(Err.Number & ": " & Err.Description, vbExclamation, _
"Error in SetPersonUpdated()"
Resume exitRoutine
End Function
Now, this is not the best example here, as the function returns the
same value as the passed in variable, but if the function returns
something different, it allows you to get something else from the
internals of the function/sub (all of this about ByRef/ByVal applies
to both functions and subs).
Now, where to use ByVal? Well, any time you're operating on the
value from a control, you should pass ByVal. Take the example I
started with:
Public Function NameFromID(lngPersonID As Long) As String
If you do this:
Me!txtLastName = NameFromID(Me!cmbPerson)
...then you're passiong the CONTROL to the function, and that
implicit control reference can cause problems. You aren't going to
be operating on the *control* itself, but on the value that control
returns. Here are couple of ways to avoid the implicit reference
with the ByRef parameter:
Me!txtLastName = NameFromID(Me!cmbPerson.Value)
Me!txtLastName = NameFromID((Me!cmbPerson))
Even though the default property of a control is .Value, when you
pass a control to a function where the parameter is defined ByRef
(implicitly or explicitly), you'll be passing a reference to the
control. By explicitly specifying the .Value, you're avoiding that.
The parens do the same thing, forcing evaluation of the value of the
control before it's passed to the function.
But you can avoid needing to do either of those by simply defining
your parameter as ByVal:
Public Function NameFromID(ByVal lngPersonID As Long) As String
That takes care of any problems, and it's really what you want,
anyway.
I was going to go on about using static variables and functions with
no parameters, but I think I've already said too much!
