You declare a user-defined type by using a Type statement that must be placed in the module’s Declarations section. Types can be declared as Public or Private within a standard module. Types can be used, but can’t be declared in Form or Report modules.

A Type variable is an instance of the type in memory; it must be declared before you can use the type. To declare a Type variable, create a Local, Private, Module-Level, or Public variable based on the type. Depending on where you place this declaration and how you declare it (using keywords Dim, Private, or Public), you determine its scope. The same rules for any other kind of variable apply to Type variables. The Dim statement in the code that follows creates a variable called mtypTimeCardData. If you place this Dim statement in the module’s General section, it’s visible to all routines in that module (notice the m, indicating that it is declared at the module level). If you place it in a subroutine or function, it’s local to that particular routine:

Dim mtypTimeCardData As TimeCardInfo

After a Type variable has been declared, you can store data into each of its elements. The following code in the frmTimeCardHours form stores information from the form into a Type variable called mtypTimeCardData. The Type variable is declared as a Private variable in the General Declarations section of the form. The Type structure is declared in basDataHandling.

Private Sub cmdWriteToType_Click()

    'Retrieve control values and place them in the type structure
    mtypTimeCardData.TimeCardDetailID = Me.txtTimeCardDetailID
    mtypTimeCardData.TimeCardID = Me.txtTimeCardID
    mtypTimeCardData.DateWorked = Me.txtDateWorked
    mtypTimeCardData.ProjectID = Me.cboProjectID
    mtypTimeCardData.WorkDescription = Me.txtWorkDescription
    mtypTimeCardData.BillableHours = Me.txtBillableHours
    mtypTimeCardData.BillingRate = Me.txtBillingRate
    mtypTimeCardData.WorkCodeID = Me.cboWorkCodeID
End Sub

The code for this chapter can be found in the CHAP12EX.MDB database on the book’s CD-ROM. The advantage of this code is that, rather than creating eight variables to store these eight pieces of related information, it creates one variable with eight elements. This method keeps things nice and neat.

To retrieve information from your Type variable, simply refer to its name, followed by a period, and then the name of the element. The following code displays a message box containing all the time card–hour information:

Private Sub cmdDisplayFromType_Click()
    'Retrieve information from the type structure
    MsgBox "Timecard Detail ID Is " & mtypTimeCardData.TimeCardDetailID &
Chr(13) & _
        "Timecard ID Is " & mtypTimeCardData.TimeCardID & Chr(13) & _
        "Date Worked Is " & mtypTimeCardData.DateWorked & Chr(13) & _
        "Project ID Is " & mtypTimeCardData.ProjectID & Chr(13) & _
        "Work Description Is " & Trim(mtypTimeCardData.WorkDescription) &
Chr(13) & _
        "Billable Hours Is " & mtypTimeCardData.BillableHours & Chr(13) & _
        "Billing Rate Is " & mtypTimeCardData.BillingRate & Chr(13) & _
        "Workcode ID Is " & mtypTimeCardData.WorkCodeID
End Sub

As mentioned, a symbolic constant is declared by using the Const keyword. A constant can be declared in a subroutine or function or in the General section of a Form, Report, or Class module. Unlike in previous versions of Access, constants can be strong-typed in Access 97, Access 2000, and Access 2002. There are several naming conventions for constants. One of them is to use a suitable scoping prefix, the letter c to indicate that you’re working with a constant rather than a variable, and then the appropriate tag for the data type. The declaration and use of a Private constant would look like this:

Private Const mccurTaxRate As Currency = .0875

The naming convention that I prefer is the use of a scoping prefix and typing the name of the constant in all uppercase. The example given previously is changed to appear as follows:

Private Const MTAXRATE as Currency = .0875

This code, when placed in a module’s Declarations section, creates a Private constant called MTAXRATE and sets it equal to .0875. Here’s how the constant is used in code:

Function TotalAmount(curSaleAmount As Currency)
   TotalAmount = curSaleAmount * MTAXRATE
End Function

This routine multiplies the curSaleAmount, received as a parameter, by the constant MTAXRATE. It returns the result of the calculation by setting the function name equal to the product of the two values. The advantage of the constant in this example is that the code is more readable than TotalAmount = curSaleAmount * .0875 would be.

Scoping Symbolic Constants

Just as regular variables have scope, user-defined constants have scope. In the preceding example, you created a Private constant. The following statement, when placed in a module’s Declarations section, creates a Public constant:

Public Const GTAXRATE As Currency = 0.0875

Because this constant is declared as Public, it can be accessed from any subroutine or function (including event routines) in your entire application. To better understand the benefits of a Public constant, suppose that you have many functions and subroutines, all referencing the constant GTAXRATE. Imagine what would happen if the tax rate were to change. If you hadn’t used a constant, you would need to search your entire application, replacing the old tax rate with the new tax rate. However, because your Public constant is declared in one place, you can easily go in and modify the one line of code where this constant is declared.

Note

By definition, the value of constants can’t be modified at runtime. If you try to modify the value of a constant, you get this VBA compiler error:

Variable Required - can't assign to this expression

Figure 12.1 illustrates this message box. You can see that an attempt was made to modify the value of the constant called GTAXRATE, which resulted in a compile error.

Figure 12.1. An error message resulting from trying to modify the value of a constant.

If you must change the value at runtime, you should consider storing the value in a table rather than declaring it as a constant. You can read the value into a variable when the application loads, and then modify the variable if needed. If you choose, you can write the new value back to the table.

Microsoft Access declares a number of intrinsic constants that can be used in Code, Form, and Report modules. Because they’re reserved by Microsoft Access, you can’t modify their values or reuse their names; however, they can be used at any time without being declared.

You should use intrinsic constants whenever possible in your code. Besides making your code more readable, they make your code more portable to future releases of Microsoft Access. Microsoft might change the value associated with a constant, but it isn’t likely to change the constant’s name. All intrinsic constants appear in the Object Browser; to activate it, simply click the Object Browser tool on the Visual Basic toolbar while in the VBE. To view the constants that are part of the VBA language, select VBA from the Object Browser’s Project/Library drop-down list. Click Constants in the Classes list box, and a list of those constants is displayed in the Members of ‘Constants’ list box. (See Figure 12.2.)

Figure 12.2. Using the Object Browser to view intrinsic constants.

In Figure 12.2, all the constant names begin with vb. All VBA constants are prefixed with vb; all ActiveX Data Object constants, with ad; all Data Access Object (DAO) constants, with db; and all constants that are part of the Access language are prefixed with ac. To view the Access language constants, select Access from the Project/Library drop-down list and Constants from the Classes list box. To view the ActiveX Data Object constants, select ADODB from the Project/Library drop-down list. The constants are categorized by their function into various classes (for example, LockTypeEnum and ExecuteOptionEnum). Select the appropriate class from the Classes list box, and its members appear in the Members Of list box.

Another way to view constants is within the context of the parameter you’re working with in the Code window. Right-click after the name of a parameter and select List Constants to display the constants associated with the parameter. This feature is covered in detail in Chapter 7, “VBA: An Introduction” in the section entitled “Tools for Working in the Visual Basic Editor.”

When declaring a fixed array, you give VBA the upper bound and the type of data that it will contain. The following code creates an array that holds six string variables:

Dim astrNames(5) As String

Fixed means that this array’s size can’t be altered at runtime. The following code gives an example of how you can loop through the array:

Sub FixedArray()
    'Declare an array of six elements
    Dim astrNames(5) As String
    Dim intCounter As Integer

    'Populate the first four elements of the array
    astrNames(0) = "Dan"
    astrNames(1) = "Alexis"
    astrNames(2) = "Brendan"
    astrNames(3) = "Zachary"

    'Use a For...Next loop to loop through the
    'elements of the array
    For intCounter = 0 To UBound(astrNames)
        Debug.Print astrNames(intCounter)
    Next intCounter
End Sub

This code starts by storing values into the first four elements of a six-element array. It then loops through each element of the array, printing the contents. Notice that the For...Next loop starts at zero and goes until the upper bound of the array, which is (5). Because the array is made up of strings, the last two elements of the array contain zero-length strings. If the array was composed of integers, the last two elements would contain zeros.

Another way to traverse the array is to use the For Each...Next construct. Your code would look like this:

Sub ArrayWith()
    'Declare an array of six elements
    Dim astrNames(5) As String
    Dim intCounter As Integer
    Dim vntAny As Variant

    'Populate the first four elements of the array
    astrNames(0) = "Dan"
    astrNames(1) = "Alexis"
    astrNames(2) = "Brendan"
    astrNames(3) = "Zachary"

    'Use a For...Each loop to loop through the
    'elements of the array
    For Each vntAny In astrNames
        Debug.Print vntAny
    Next vntAny
End Sub

This code declares a Variant variable called vntAny. Instead of using a loop with Ubound as the upper delimiter to traverse the array, the example uses the For Each...Next construct.

Dir astrNames(1 to 6)

Often, you don’t know how many elements your array needs to contain. In this case, you should consider declaring a dynamic array, which can be resized at runtime. Using this type of array can make your code more efficient because VBA preallocates memory for all elements of a fixed array, regardless of whether data is stored in each of the elements. However, if you aren’t sure how many elements your array will contain, preallocating a huge amount of memory can be quite inefficient.

To create a dynamic array, you declare it without assigning an upper bound. You do this by omitting the number between the parentheses when declaring the array, as shown in this example:

Sub DynamicArray()
    'Declare a dynamic array
    Dim astrNames() As String
    Dim intCounter As Integer
    Dim vntAny As Variant

    'Resize the array to hold two elements
    ReDim astrNames(1)
    'Populate the two elements
    astrNames(0) = "Dan"
    astrNames(1) = "Alexis"

    'Use a For...Each loop to loop through the
    'elements of the array
    For Each vntAny In astrNames
        Debug.Print vntAny
    Next vntAny
End Sub

However, there’s a potential problem when you try to resize the array:

Sub ResizeDynamic()
    'Declare a dynamic array
    Dim astrNames() As String
    Dim intCounter As Integer
    Dim vntAny As Variant

    'Resize the array to hold two elements
    ReDim astrNames(1)

    'Populate the two elements
    astrNames(0) = "Dan"
    astrNames(1) = "Alexis"

    'Use a For...Each loop to loop through the
    'elements of the array
    For Each vntAny In astrNames
        Debug.Print vntAny
    Next vntAny
End Sub

Sub ResizeDynamic()
    'Declare a dynamic array
    Dim astrNames() As String
    Dim intCounter As Integer
    Dim vntAny As Variant

    'Resize the array to hold two elements
    ReDim astrNames(1)

    'Populate the two elements
    astrNames(0) = "Dan"
    astrNames(1) = "Alexis"
    'Resize the array to hold four elements
    ReDim astrNames(3)

    'Populate the last two elements
    astrNames(2) = "Brendan"
    astrNames(3) = "Zachary"

    'Use a For..Each loop to loop through the
    'elements of the array
    For Each vntAny In astrNames
        Debug.Print vntAny
    Next vntAny
End Sub

You might expect that all four elements will contain data. Instead, the ReDim statement reinitializes all the elements, and only elements 2 and 3 contain values. This problem can be avoided by using the Preserve keyword. The following code behaves quite differently:

Sub ResizePreserve()
    'Declare a dynamic array
    Dim astrNames() As String
    Dim intCounter As Integer
    Dim vntAny As Variant

    'Resize the array to hold two elements
    ReDim astrNames(1)

    'Populate the two elements
    astrNames(0) = "Dan"
    astrNames(1) = "Alexis"

    'Resize the array to hold four elements
    ReDim Preserve astrNames(3)

    'Populate the last two elements
    astrNames(2) = "Brendan"
    astrNames(3) = "Zachary"

    'Use a For...Each loop to loop through the
    'elements of the array
    For Each vntAny In astrNames
        Debug.Print vntAny
    Next vntAny
End Sub

In this example, all values already stored in the array are preserved. The Preserve keyword brings its own difficulties, though. It can temporarily require huge volumes of memory because, during the ReDim process, VBA creates a copy of the original array. All the values from the original array are copied to a new array. The original array is removed from memory when the process is complete. The Preserve keyword can cause problems if you’re dealing with very large arrays in a limited memory situation.

Many people are unaware that you can pass an array as a parameter to a function or subroutine. The following code provides an example:

Sub PassArray()
    'Declare a six-element array
    Dim astrNames(5) As String
    Dim intCounter As Integer

    'Call the FillNames function, passing a reference
    'to the array
    Call FillNames(astrNames)

    'Use a For...Next loop to loop through the
    'elements of the array
    For intCounter = 0 To UBound(astrNames)
        Debug.Print astrNames(intCounter)
    Next intCounter
End Sub

The code begins by declaring a fixed array called astrNames. The FillNames routine is called. It receives the array as a parameter and then populates all its elements. The PassArray routine is then able to loop through all the elements of the array that was passed, displaying information from each element. The FillNames routine looks like this:

Sub FillNames(varNameList As Variant)
    'Populate the elements of the array
    varNameList(0) = "Alison"
    varNameList(1) = "Dan"
    varNameList(2) = "Alexis"
    varNameList(3) = "Brendan"
    varNameList(4) = "Zachary"
    varNameList(5) = "Sonia"
End Sub

Notice that the routine receives the array as a variant variable. It then populates each element of the array.

By default, parameters in Access are passed by reference. This means that a memory reference to the variable being passed is received by the function. This process is best illustrated by an example:

Sub PassByRef()
    'Declare string variables
    Dim strFirstName As String
    Dim strLastName As String

    'Assign values to the string variables
    strFirstName = "Alison"
    strLastName = "Balter"

    'Call a subroutine that receives the two variables as
    'parameters by reference
    Call FuncByRef(strFirstName, strLastName)

    'Print the changed values of the variables
    Debug.Print strFirstName
    Debug.Print strLastName
End Sub
Sub FuncByRef(strFirstParm As String, strSecondParm As String)
    'Modify the values of the parameters
    strFirstParm = "Bill"
    strSecondParm = "Gates"
End Sub

You might be surprised that the Debug.Print statements found in the subroutine PassByRef print "Bill" and "Gates". This is because strFirstParm is actually a reference to the same location in memory as strFirstName, and strSecondParm is a reference to the same location in memory as strLastName. This violates the concepts of black-box processing, in which a variable can’t be changed by any routine other than the one it was declared in. The following code eliminates this problem:

Sub PassByVal()
    'Declare the string variables
    Dim strFirstName As String
    Dim strLastName As String

    'Assign values to the string variables
    strFirstName = "Alison"
    strLastName = "Balter"

    'Call a subroutine that receives the two variables as
    'parameters by value
    Call FuncByVal(strFirstName, strLastName)

    'Print the unchanged values of the variables
    Debug.Print strFirstName
    Debug.Print strLastName
End Sub

Sub FuncByVal(ByVal strFirstParm As String, _
ByVal strSecondParm As String)
    'Change the values of the parameters
    'Since they are received by value,
    'the original variables are unchanged
    strFirstParm = "Bill"
    strSecondParm = "Gates"
End Sub

This FuncByVal subroutine receives the parameters by value. This means that only the values in strFirstName and strLastName are passed to the FuncByVal routine. The strFirstName and strLastName variables, therefore, can’t be modified by the FuncByVal subroutine. The Debug.Print statements print "Alison" and "Balter".

The following example illustrates a great reason why you might want to pass a parameter by reference:

Sub GoodPassByRef()
    'Declare variables
    Dim blnSuccess As Boolean
    Dim strName As String

    'Set the value of the string variable
    strName = "Microsoft"

    'Set the boolean variable equal to the value
    'returned from the GoodFunc function
    blnSuccess = GoodFunc(strName)

    'Print the value of the boolean variable
    Debug.Print blnSuccess
End Sub

Function GoodFunc(strName As String)
    'Evaluate the length of the value received
    'as a parameter
    'Convert to Upper Case and return true if not zero-length
    'Return false if zero-length
    If Len(strName) Then
        strName = UCase$(strName)
        GoodFunc = True
    Else
        GoodFunc = False
    End If

End Function

In essence, the GoodFunc function needs to return two values. Not only does the function need to return the uppercase version of the string passed to it, but it also needs to return a success code. Because a function can return only one value, you need to be able to modify the value of strName within the function. As long as you’re aware of what you’re doing and why you’re doing it, there’s no problem with passing a parameter by reference.

After reading this section, you might ask yourself whether it is better to pass parameters by reference or by value. Although in terms of “black-box” processing, it is better to pass by value, code that involves parameters passed by reference actually executes more quickly than those passed by value. As long as you and the programmers that you work with are aware of the potential problems with passing parameters by reference, in general, I feel that it is better to pass parameters by reference.

Access 97, Access 2000, and Access 2002 allow you to use optional parameters. In other words, it isn’t necessary to know how many parameters will be passed. The ReturnInit function in the following code receives the second two parameters as optional; it then evaluates whether the parameters are missing and responds accordingly:

Function ReturnInit(ByVal strFName As String, _
       Optional ByVal strMI, Optional ByVal strLName)
    'If strMI parameter is not received, prompt user for value
    If IsMissing(strMI) Then
        strMI = InputBox("Enter Middle Initial")
    End If

    'If strLName parameter is not received, prompt user for value
    If IsMissing(strLName) Then
        strLName = InputBox("Enter Last Name")
    End If

    'Return concatenation of last name, first name,
    'and middle initial
    ReturnInit = strLName & "," & strFName & " " & strMI
End Function

This function could be called as follows:

strName = ReturnInit("Bill",,"Gates")

As you can see, the second parameter is missing. Rather than causing a compiler error, as in earlier versions of Access, this code compiles and runs successfully. The IsMissing function, built into Access, determines whether a parameter has been passed. After identifying missing parameters, you must decide how to handle the situation in code. In the example, the function prompts for the missing information, but here are some other possible choices:

Listing 12.1 and Listing 12.2 illustrate how to carry out these two alternatives.

Function ReturnInit2(ByVal strFName As String, _
       Optional ByVal strMI, Optional ByVal strLName)
    'If middle initial is not received, set it to "A"
    If IsMissing(strMI) Then
        strMI = "A"
    End If

    'If last name is not received, set it to "Roman"
    If IsMissing(strLName) Then
        strLName = "Roman"
    End If

    'Return concatenation of last name, first name,
    'and middle initial
    ReturnInit2 = strLName & "," & strFName & " " & strMI
End Function

This example uses a default value of "A" for the middle initial and a default last name of "Roman". Now take a look at Listing 12.2, which illustrates another method of handling missing parameters.

Function ReturnInit3(ByVal strFName As String, _
       Optional ByVal strMI, Optional ByVal strLName)
    Dim strResult As String

    'If middle initial and last name are missing,
    'return first name
    If IsMissing(strMI) And IsMissing(strLName) Then
        ReturnInit3 = strFName

    'If only the middle initial is missing
    'return last name and first name
    ElseIf IsMissing(strMI) Then
        ReturnInit3 = strLName & ", " & strFName

    'If only the last name is missing
    'return first name and middle initial
    ElseIf IsMissing(strLName) Then
        ReturnInit3 = strFName & " " & strMI

    'Otherwise (If nothing is missing),
    'return last name, first name and middle initial
    Else
        ReturnInit3 = strLName & "," & strFName & " " & strMI
    End If
End Function

This example manipulates the return value, depending on which parameters it receives. If neither optional parameter is passed, just the first name displays. If the first name and middle initial are passed, the return value contains the first name followed by the middle initial. If the first name and last name are passed, the return value contains the last name, a comma, and the first name. If all three parameters are passed, the function returns the last name, a comma, a space, and the first name.

The declaration of the ReturnInit3 function shown in Listing 12.2 can easily be modified to provide default values for each optional parameter. The following declaration illustrates this:

Function ReturnInit4(Optional ByVal strFName As String = "Alison", _
       Optional ByVal strMI As String = "J", _
       Optional ByVal strLName As String = "Balter")

ReturnInit4 has three optional parameters. The declaration assigns a default value to each parameter. The function uses the default value if the calling routine does not supply the parameter.

In all the examples you’ve seen so far, the parameters of a procedure have been supplied positionally. Named parameters allow you to supply parameters without regard for their position, which is particularly useful in procedures that receive optional parameters. Take a look at this example:

strName = ReturnInit3("Bill",,"Gates")

Because the second parameter isn’t supplied, and the parameters are passed positionally, a comma must be used as a placemarker for the optional parameter. This requirement can become unwieldy when you’re dealing with several optional parameters. The following example greatly simplifies the process of passing the parameters and also better documents what’s happening:

strName = ReturnInit3(strFName:= "Bill",strLName:= "Gates")

When parameters are passed by name, it doesn’t even matter in what order the parameters appear, as shown in the following example:

strName = ReturnInit3(strLName:= "Gates",strFName:="Bill")

This call to the ReturnInit3 function yields the same results as the call to the function in the previous example.

A recursive procedure is one that calls itself. If a procedure calls itself over and over again, it will eventually render an error. This is because it runs out of stack space. Here’s an example:

Function Recursive(lngSomeVal)
    'Return value based on another call to the function
    Recursive = Recursive(lngSomeVal)
End Function

There are practical reasons why you might want to call a function recursively. Here’s an example:

Function GetFactorial(intValue as Integer) as Long
    'If value passed is less than or equal to one, we're done
    If intValue <= 1 Then
        GetFactorial = 1

    'If value passed is greater than one,
    'call function again with decremented value
    'and multiply by value
    Else
        GetFactorial = GetFactorial(intValue - 1) * intValue
    End If
End Function

The code receives an input parameter (for example, 5). The value is evaluated to see if it is less than or equal to 1. If it is, the function is exited. If the value is greater than 1, the function is called again, but is passed the previous input parameter minus 1 (for example, 4). The return value from the function is multiplied by the original parameter value (for example, 4*5). The function calls itself over and over again until the value that it passes to itself is 2 minus 1 (1), and the function is exited. In the example where 5 is passed to the function, it multiplies 5*4*3*2*1, resulting in 120, the factorial of 5.

Using a parameter array, you can easily pass a variable number of arguments to a procedure. Here’s an example:

Sub GetAverageSalary(strDepartment As String, _
    ParamArray currSalaries() As Variant)

    Dim sngTotalSalary As Single
    Dim sngAverageSalary As Single
    Dim intCounter As Integer

    'Loop through the elements of the array,
    'adding up all of the salaries
    For intCounter = 0 To UBound(currSalaries())
        sngTotalSalary = sngTotalSalary + currSalaries(intCounter)
    Next intCounter

    'Divide the total salary by the number of salaries in the array
    sngAverageSalary = sngTotalSalary / (UBound(currSalaries()) + 1)

    'Display the department and the average salary in a message box
    MsgBox strDepartment & " has an average salary of " & _
        sngAverageSalary
End Sub

The routine is called like this:

Call GetAverageSalary("Accounting", 60000, 20000, 30000, 25000, 80000)

The beauty of the ParamArray keyword is that you can pass a variable number of parameters to the procedure. In the example, a department name and a variable number of salaries are passed to the GetAverageSalary procedure. The procedure loops through all the salaries that it receives in the parameter array, adding them together. It then divides the total by the number of salaries contained in the array.

Variant variables are initialized to the value of Empty. Often, you need to know whether a value has been stored in a Variant variable. If a Variant has never been assigned a value, its value is Empty. As mentioned, the Empty value is not the same as zero, Null, or a zero-length string.

It’s important to be able to test for Empty in a runtime environment. This can be done by using the IsEmpty function, which determines whether a variable has the Empty value. The following example tests a String variable for the Empty value:

Sub StringVar()
    Dim strName As String
    Debug.Print IsEmpty(strName) 'Prints False
    Debug.Print strName = "" 'Prints True
End Sub

The Debug.Print statement prints False. This variable is equal to a zero-length string because the variable is initialized as a String variable. All String variables are initialized to a zero-length string. The next example tests a Variant variable to see whether it has the Empty value:

Sub EmptyVar()
    Dim vntName As Variant
    Debug.Print IsEmpty(vntName) 'Prints True
    vntName = ""
    Debug.Print IsEmpty(vntName) 'Prints False
    vntName = Empty
    Debug.Print IsEmpty(vntName) 'Prints True
End Sub

A Variant variable loses its Empty value when any value has been stored in it, including zero, Null, or a zero-length string. It can become Empty again only by storing the keyword Empty in the variable.

Null is a special value that indicates unknown or missing data. Null is not the same as Empty, nor is one Null value equal to another one. Variant variables can contain the special value called Null.

Often, you need to know whether specific fields or controls have never been initialized. Uninitialized fields and controls have a default value of Null. By testing for Null, you can make sure fields and controls contain values.

If you want to make sure that all fields and controls in your application have data, you need to test for Nulls. This can be done by using the IsNull function:

Sub NullVar()
    Dim vntName As Variant
    Debug.Print IsEmpty(vntName) 'Prints True
    Debug.Print IsNull(vntName) 'Prints False
    vntName = Null
    Debug.Print IsNull(vntName) 'Prints True
End Sub

Notice that vntName is equal to Null only after the value of Null is explicitly stored in it. It’s important to know not only how variables and Null values interact, but also how to test for Null within a field in your database. A field contains a Null if data hasn’t yet been entered in the field, and the field has no default value. In queries, you can test for the criteria "Is Null" to find all the records in which a particular field contains a Null value. When dealing with recordsets (covered in Chapter 14, “What Are ActiveX Data Objects and Data Access Objects, and Why Are They Important?”), you can also use the IsNull function to test for a Null value in a field. Here’s an example:

Sub LoopProjects()
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset

    'Open a recordset based on the projects table
    rst.Open "tblProjects", CurrentProject.Connection

    'Loop through all of the records in the recordset
    Do Until rst.EOF

        'Print the ProjectID and the ProjectName
        Debug.Print rst!ProjectID, rst!ProjectName

        'If the ProjectBeginDate field is null,
        'display a message to the user
        If IsNull(rst!ProjectBeginDate) Then
            Debug.Print "Project Begin Date Contains No Value!!"
        End If

       'Move to the next row in the recordset
       rst.MoveNext
    Loop
End Sub

Alternatively, you could use the more compact Nz function to detect Nulls and print a special message:

Sub LoopProjects2()
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset

    'Open a recordset based on the projects table
    rst.Open "tblProjects", CurrentProject.Connection

    'Loop through all of the rows in the recordset
    Do Until rst.EOF

        'Print the ProjectID and the ProjectName
        Debug.Print rst!ProjectID, rst!ProjectName

        'Print the ProjectBeginDate, or a message if
        'the ProjectBeginDate is null
        Debug.Print Nz(rst!ProjectBeginDate, _
            "Project Begin Date Contains No Value!!")
        rst.MoveNext
    Loop
End Sub

All the concepts of recordset handling are covered in Chapter 14. For now, you need to understand only that this code loops through each record in tblProjects. It uses the IsNull function to evaluate whether the ProjectBeginDate field contains a Null value. If the field does contain a Null, a warning message is printed to the Immediate window. Here is another example:

Private Sub Form_Current()

    Dim ctl as Control

    'Loop through each control in the form's
    'Controls collection
    For Each ctl In Controls

        'If the control is a TextBox
        If TypeOf ctl Is TextBox Then
           'If the value in the control is null,
           'change the BackColor property to cyan
           If IsNull(ctl.Value) Then
               ctl.BackColor = vbCyan

           'If the value in the control is not null
           'change the BackColor property to white
           Else
               ctl.BackColor = vbWhite
           End If
       End If
    Next ctl
End Sub

The code in this example (found in the frmProjects form in CHAP12EX.MDB) loops through every control on the current form. If the control is a text box, the routine checks to see whether the value in the text box is Null. If it is, the BackColor property of the text box is set to Aqua; otherwise, it’s set to White.

You should know about some idiosyncrasies of Null:

The following example shows how Null values are propagated:

Sub PropNulls()
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset

    'Open a recordset based on the Projects table
    rst.Open "tblProjects", CurrentProject.Connection

    'Loop through the recordset
    Do Until rst.EOF

        'Print the ProjectID and the value of the
        'ProjectBeginDate plus one
        Debug.Print rst!ProjectID, rst!ProjectBeginDate + 1

        'Move to the next row
        rst.MoveNext
    Loop
End Sub

Figure 12.3 illustrates the effects of running this routine on a table in which the first and third records contain Null values. Notice that the result of the calculation is Null for those records because the Null propagated within those records.

Figure 12.3. The result of running the PropNulls routine.

Notice the difference from the previous example if the value in the field is Empty:

Sub EmptyVersusNull()
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset

    'Open a recordset based on the Projects table
    rst.Open "tblProjects", CurrentProject.Connection

    'Loop through the recordset
    Do Until rst.EOF

        'Print the ProjectID and the PurchaseOrderNumber
        'combined with the word "Hello"
        Debug.Print rst!ProjectID, rst!PurchaseOrderNumber + "Hello"

        'Move to the next row
        rst.MoveNext
    Loop
End Sub

In this example, the tblProjects table has several records. The PurchaseOrderNumber for the first record contains a Null; for the third record, it contains an Empty. Notice the different effects of the two values, as shown in Figure 12.4.

Figure 12.4. The result of running the EmptyVersusNull routine.

Looking at Figure 12.4, you can see that Null printed for the first record, and Hello printed for the third record.

Note

The EmptyVersusNull routine uses a numeric operator (+). As discussed, the effect of Null used in a calculation is a resulting Null. In text strings, you can use an ampersand (&) instead of a plus (+) to eliminate this problem. Figure 12.5 illustrates the same code with an ampersand to concatenate rather than add. You can see that no Null values result from the concatenation.

Figure 12.5. The result of changing plus (+) in the EmptyVersusNull routine to an ampersand (&).

It’s very common to create a generic routine that receives any value, tests to see whether it’s Null, and returns a non-Null value. An example is the CvNulls function:

Function CvNulls(vntVar1 As Variant, vntVar2 As Variant) _
      As Variant
    'If first variable is null, return the second variable
    'otherwise, return the first variable
    CvNulls = IIf(IsNull(vntVar1), vntVar2, vntVar1)
End Function

This routine would be called as follows:

Sub TestForNull(vntSalary As Variant, vntCommission As Variant)
    'Add the result of calling the CVNulls function,
    'passing the salary and zero to the
    'result of calling the CVNulls function
    'passing the commission and zero
    curTotal = CvNulls(vntSalary, 0) + CvNulls(vntCommission, 0)

    'Display the total of salary plus commission
    MsgBox curTotal
End Sub

The TestForNull routine receives two parameters: salary and commission. It adds the two values to determine the total of salaries plus commissions. Ordinarily, if the value of either parameter is Null, the expression results in Null. This problem is eliminated by the CvNulls function, which also receives two parameters. The first parameter is the variable being tested for Null; the second is the value you want the function to return if the first parameter is determined to be Null. The routine combines the Immediate If (IIf) function and the IsNull function to evaluate the first parameter and return the appropriate value.

A collection is created using a Collection object. After the Collection object is declared, items can be added to the collection. The code necessary to create a custom collection looks like this:

Dim colNames as Collection

The Add method of the Collection object is used to add items to a custom collection. The Add method receives a value or object reference as its first parameter, and a unique key to that element of the collection as a second parameter. The Add method appears as follows:

colNames.Add "Alexis", "Alexis"

The code shown previously adds the name Alexis to a collection called colNames. The key to the item in the collection is the name Alexis. In the following code example, the collection colNames is first declared and instantiated. Then several names are added to the custom collection colNames.

Sub AddToCollection()
    'Declare a Collection object
    Dim colNames As Collection
    'Instantiate the Collection object
    Set colNames = New Collection

    'Add items to the collection
    colNames.Add "Alison", "Alison"
    colNames.Add "Dan", "Dan"
    colNames.Add "Alexis", "Alexis"
    colNames.Add "Brendan", "Brendan"
    colNames.Add "Sonia", "Sonia"
    colNames.Add "Sue", "Sue"
End Sub

After items have been added to a collection, the Item method is used to access them via either their ordinal position, or the key designated when they were added. Accessing an item in a collection using the ordinal position looks like this:

Debug.Print colNames.Item(1)

Because the Item method is the default method of the Collection object, the code can be shortened to this:

Debug.Print colNames(1)

I usually prefer to refer to an item in a collection using its unique key. The code appears as follows:

Debug.Print colNames("Alexis")

The Remove method of the Collection object is used to remove items from a collection. The syntax looks like this:

colNames.Remove 2

The preceding syntax would remove the second element of the collection. Using the key, the code is changed to this:

colNames.Remove "Sonia"

You can easily remove all the elements of a collection in two ways:

Set colNames = New Collection

or

Set colNames = Nothing

The For...Each loop is used to iterate through the items in a collection. The code looks like this:

Sub IterateCollection()
    'Declare a Collection object
    Dim colNames As Collection

    'Declare a variant variable for looping
    'through the collection
    Dim varItem As Variant

    'Instantiate the Collection object
    Set colNames = New Collection

    colNames.Add "Alison", "Alison"
    colNames.Add "Dan", "Dan"
    colNames.Add "Alexis", "Alexis"
    colNames.Add "Brendan", "Brendan"
    colNames.Add "Sonia", "Sonia"
    colNames.Add "Sue", "Sue"

    'Use the variant variable and a For..Each
    'loop to loop through each element in
    'the collection, printing its value
    For Each varItem In colNames
        Debug.Print colNames(varItem)
    Next varItem
End Sub

Notice that in addition to the declaration of the Collection variable, a Variant variable is declared. The Variant variable is used in the For...Each loop to loop through each item in the collection. The Variant variable is the subscript within the For...Each loop for accessing a particular item within the collection.

By default, VBA compilesyour code only when the code in the module changes or when a procedure in one module is called by another module. Although this default setting can dramatically speed the compilation process, it can leave you wondering whether you have a hidden time bomb lurking somewhere in your application.

Here’s a typical scenario: You open a form, make some simple changes, save the changes, and close the form. You repeat this process for a few additional forms. You also open a couple of modules to make some equally simple changes. During the testing process, you forget to test one or more of the forms and one or more of the modules. With the Compile On Demand option set to True (its default value), errors aren’t identified until the offending code is accessed.

To disable the Compile On Demand feature, choose Tools|Options from the VBE. Click the General tab and remove the check from Compile On Demand. You might notice some degradation in performance each time your code compiles, but this is time well spent.

The following event routine illustrates how you could view all the projects associated with the selected client. It illustrates the importance of the ability to work with Null values and intrinsic constants.

Private Sub cmdViewProjects_Click()
On Error GoTo Err_cmdViewProjects_Click

    'Evaluate the ClientID text box to determine if it is null
    'If it is null, display a message to the user
    'Otherwise, save the current record and open the
    'projects form (which is set up to only show projects
    'related to the selected client)
    If IsNull(Me.txtClientID.Value) Then
        MsgBox "You Must Enter Client Information Before " & _
            "Viewing the Projects Form"
    Else
        DoCmd.RunCommand acCmdSaveRecord
        DoCmd.OpenForm FormName:="frmProjects"
    End If

Exit_cmdViewProjects_Click:
    Exit Sub
Err_cmdViewProjects_Click:
    MsgBox Err.Description
    Resume Exit_cmdViewProjects_Click
End Sub

The routine first invokes error handling (discussed in Chapter 16, “Error Handling: Preparing for the Inevitable”); then uses the IsNull function to test whether a ClientID has been entered. The IsNull function returns a True if the value in the txtClientID control is Null. If it is, an error message is displayed. If the txtClientID control contains a non-Null value, two methods are performed on the DoCmd object.

The first method performed on the DoCmd object is the RunCommand method. This method receives the constant associated with the name of the menu command you want to execute. The use of intrinsic constants makes this code more readable, and the RunCommand method makes it much easier to call menu commands from code. The second method performed on the DoCmd object is OpenForm, which opens the frmProjects form. The RecordSource property of the frmProjects form is programmatically set to a query that only displays projects associated with the currently selected customer.

If many parts of an application require the same information that is stored in a specific table, it would be inefficient to read the data from this table each time the application needs it. It would be much more efficient to read this data once, when the application loads, and store it in a type structure. Because it remains in memory at all times, you can efficiently retrieve it whenever needed. The type structure is defined, and a Public Type variable based on the type structure is declared in a module’s Declarations section. It looks like this:

Type CompanyInfo
    SetUpID As Long
    CompanyName As String * 50
    Address As String * 255
    City As String * 50
    StateProvince As String * 20
    PostalCode As String * 20
    Country As String * 50
    PhoneNumber As String * 30
    FaxNumber As String * 30
    DefaultPaymentTerms As String * 255
    DefaultInvoiceDescription As String
End Type
Public typCompanyInfo As CompanyInfo

You must build a subroutine that is invoked when your startup form is first loaded. This routine populates all the elements of the type structure. The routine looks like this:

Sub GetCompanyInfo()

    Dim strSubName As String
    Dim rst As ADODB.Recordset

    'Instantiate and open a recordset
    'based on the tblCompanyInfo table
    Set rst = New ADODB.Recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.Open "Select * from tblCompanyInfo", Options:=adCmdText

    'Populate the elements of the type structure
    'with data from the table
    With typCompanyInfo
        .SetUpID = rst!SetUpID
        .CompanyName = rst!CompanyName
        .Address = rst!Address
        .City = rst!City
        .StateProvince = rst!StateOrProvince
        .PostalCode = rst!PostalCode
        .Country = rst!Country
        .PhoneNumber = rst!PhoneNumber
        .FaxNumber = rst!PhoneNumber
    End With

    'Close the recordset and destoy the object
    rst.Close
    Set rst = Nothing
End Sub

Don’t be concerned with the recordset handling included in this routine. Instead, notice that the value from each field in the first (and only) record of the tblCompanyInfo table is being loaded into the elements of the Global Type variable. Here’s an example of how the Type variable is used:

Sub PopulateControls()
    'Populate the text boxes on the report
    'with data from the type structure
    txtCompanyName.Value = Trim(typCompanyInfo.CompanyName)
    txtAddress.Value = Trim(typCompanyInfo.Address)
    txtCityStateZip.Value = Trim(typCompanyInfo.City) & ", " & _
        Trim(typCompanyInfo.StateProvince) & _
    " " & Format(Trim(typCompanyInfo.PostalCode), "!&&&&&-&&&&")
txtPhoneFax.Value = "PHONE: " & _
    Format(Trim(typCompanyInfo.PhoneNumber), "(&&&)&&&-&&&&") & _
    "       FAX: " & _
    Format(Trim(typCompanyInfo.FaxNumber), "(&&&)&&&-&&&&")
End Sub

This routine is populates four different controls on a form with the company information retrieved from the elements of the Global Type variable.