Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources] نسخه متنی

Using the VBA Control Structures

VBA gives the developer several different constructs for looping and decision processing. The most commonly used ones are covered in the following sections and are found in the form called frmControlStructures.

If…Then…Else

The If…Then…Else construct evaluates whether a condition is True. In the following example, anything between If and Else will occur if the statement evaluates to True, and any code between Else and End If will be executed if the statement evaluates to False. The Else is optional.

Private Sub cmdIfThenElse_Click()
If IsNull(Me.txtName.Value) or IsNull(Me.txtAge.Value) Then
MsgBox "Name or Age is Blank"
Else
MsgBox "Your Name Is " & Me.txtName.Value _
& " And Your Age Is " & Me.txtAge.Value
End If
End Sub

This code tests whether the text box called txtName or the text box txtAge contains a Null. A different message is displayed depending on whether one of the text boxes contains a Null value.

One-line If statements are also permitted; they look like this:

If IsNull(Me.txtvalue.Value) Then MsgBox "You must enter a value"

However, this format for an If statement isn't recommended because it reduces readability.

Another useful form of an If statement is ElseIf, which enables you to evaluate an unlimited number of conditions in one If statement. The following code gives you an example (this example is not included in Chap7Ex):

Sub MultipleIfs(intNumber As Integer)
If intNumber = 1 Then
MsgBox "You entered a one"
ElseIf intNumber = 2 Then
MsgBox "You entered a two"
ElseIf intNumber >= 3 And intNumber <= 10 Then
MsgBox "You entered a number between 3 and 10"
Else
MsgBox "You entered some other number"
End If
End Sub

The conditions in an If statement are evaluated in the order in which they appear. For this reason, it's best to place the most common conditions first. After a condition is met, execution continues immediately after End If. If no conditions are met, and there's no Else statement, execution will also continue immediately after End If.

NOTE

If multiple conditions exist, it's almost always preferable to use a Select Case statement, described later in this chapter, rather than an If statement. Case statements generally make your code easier to read and maintain.

The Immediate If (IIf)

An Immediate If (IIf) is a variation of an If statement. It's actually a built-in function that returns one of two values, depending on whether the condition being tested is true or false. Here's an example (this code is not included in Chap7Ex):

Function EvalSales(curSales As Currency) As String
EvalSales = IIf(curSales >= 100000, "Great Job", "Keep Plugging")
End Function

This function evaluates the curSales parameter to see whether its value is greater than or equal to $100,000. If it is, the function returns the string "Great Job"; otherwise, the function returns the string "Keep Plugging".

CAUTION

Both the true and false portions of the IIf are evaluated, so if there's a problem with either part of the expression (for example, a divide-by-zero condition), an error occurs.

The IIf function is most often used in a calculated control on a form or report, or to create a new field in a query. Probably the most common example is an IIf expression that determines whether the value of a control is IsNull. If it is, you can have the expression return a zero or an empty string; otherwise, you can have the expression return the value in the control. The following expression, for example, evaluates the value of a control on a form:

=IIf(IsNull(Forms!frmOrders.txtFreight.Value),0,Forms!frmOrders.txtFreight.Value)

This expression displays either a zero or the value for freight in the control called txtFreight.

NOTE

Although the IIf function can be used to handle Nulls, the built-in NZ function is a more efficient solution to this problem and avoids the inherent pitfalls of IIf.

CAUTION

The IIf function is rather slow. It is best to avoid using it whenever possible by replacing it with a properly formed If…Then…Else block.

The Conditional If: Conditional Compilation

Conditional compilation enables you to selectively execute blocks of code. This feature is useful in several situations:

• When you want certain blocks of code to execute in the demo version of your product and other blocks to execute in your product's retail version

• When you're distributing your application in different countries and want certain blocks of code to apply to some countries but not to others

• When you want certain blocks of code to execute only during the testing of your application

Conditional compilation is done by using the #If…Then…#Else directive, as shown here and found under the Conditional Compilation command button on the frmControlStructures form:

Sub cmdConditionalCompilation_Click()
#If Language = "Spanish" Then
MsgBox "Hola, Que Tal?"
#Else
MsgBox "Hello, How Are You?"
#End If
End Sub

You can declare the compiler constant, in this case, Language, in one of two places: in a module's General Declarations section or in the Project Properties dialog box. A compiler constant declared in the General Declarations section of a module looks like this:

#Const Language = "Spanish"

The disadvantage of this constant is that you can't declare it as Public. It isn't possible to create Public compiler constants by using the #Const directive. This means that any compiler constants declared in a module's Declarations section can be used only within that module. The major advantage of declaring this type of compiler constant is that it can contain a string. For example, the compiler constant Language, defined in the previous paragraph, is given the value "Spanish".

Public compiler constants can be declared by modifying the Project Properties. Because they are Public in scope, compiler constants declared in the Project Properties can be referred to from anywhere in your application. The major limitation on compiler directives set up in Project Properties is that they can contain only integers. For example, you would have to enter Language = 1.

To define compiler constants using the Project Properties dialog box, right-click within the Project window and select

projectx Properties, where

projectx is the name of the project you are working with. You can now enter the values you need into the text box labeled Conditional Compilation Arguments. You can enter several arguments by separating them with a colon, such as Language = 1 : Version = 2.

With the compiler directive Language=1, the code would look like this:

Sub ConditionalIf()
#If Language = 1 Then
MsgBox "Hola, Que Tal?"
#Else
MsgBox "Hello, How Are You?"
#End If
End Sub

NOTE

For this code to execute properly, you must remove the constant declaration from the previous example.

Notice that ConditionalIf now evaluates the constant Language against the integer of 1.

It's important to understand that using conditional constants isn't the same as using regular constants or variables with the standard If…Then…Else construct. Regular constants or variables are evaluated at runtime, which requires processing time each time the application is run. Conditional constants and conditional If…Then…Else statements control which sections of code are actually compiled. All resolution is completed at compile time; this eliminates the need for unnecessary processing at runtime.

Select Case

Rather than using multiple If…Then…Else statements, it's often much clearer to use a Select Case statement, as shown here and found under the Select Case command button of the frmControlStructures form:

Private Sub cmdCase_Click()
Dim intAge As Integer
intAge = Nz(Me.txtAge.Value, 0) Select Case intAge
Case 0
MsgBox "You Must Enter a Number"
Case 1 To 18
MsgBox "You Are Just a Kid"
Case 19, 20, 21
MsgBox "You are Almost an Adult"
Case 22 to 40
MsgBox "Good Deal"
Case Is > 40
MsgBox "Getting Up There!"
Case Else
MsgBox "You Entered an Invalid Number"
End Select
End Sub

This subroutine first uses the Nz function to convert a Null or empty value in the txtAge control to 0; otherwise, the value in txtAge is stored in intAge. The Select Case statement then evaluates intAge. If the value is 0, the code displays a message box with You Must Enter a Number. If the value is between 1 and 18 inclusive, the code displays a message box saying You Are Just a Kid. If the user enters 19, 20, or 21, the code displays the message You are Almost an Adult. If the user enters a value between 22 and 40 inclusive, the code displays the message Good Deal. If the user enters a value greater than 40, the code displays the message Getting Up There!; otherwise, the user gets a message indicating that she entered an invalid number.

Looping

Several looping structures are available in VBA; most are discussed in this section. Take a look at the following example of a looping structure (found under the Do While…Loop command button of the frmControlStructures form):

Sub cmdDoWhileLoop_Click()
Do While Nz(Me.txtAge.Value)< 35
Me.txtAge.Value = Nz(Me.txtAge.Value) + 1
Loop
End Sub

In this structure, if the value in the txtAge text box is greater than or equal to 35, the code in the loop is not executed. If you want the code to execute unconditionally at least one time, you need to use the following construct (found under the Do…Loop While command button of the frmControlStructures form:

Sub cmdDoLoopWhile_Click()
Do
Me.txtAge = Nz(Me.txtAge.Value) + 1
Loop While Nz(Me.txtAge.Value) < 35
End Sub

This code will execute one time, even if the value in the txtAge text box is set to 35. The Do While…Loop in the previous example evaluates before the code is executed, so it doesn't ensure code execution. The Do…Loop While is evaluated at the end of the loop, and therefore guarantees execution.

Alternatives to the Do While…Loop and the Do…Loop While are Do Until…Loop and Do…Loop Until. Do Until…Loop (found under the Do Until…Loop command button of the frmControlStructures form) works like this:

Sub cmdDoUntil_Click()
Do Until Nz(Me.txtAge.Value) = 35
Me.txtAge.Value = Nz(Me.txtAge.Value) + 1
Loop
End Sub

This loop continues to execute until the value in the txtAge text box becomes equal to 35. The Do…Loop Until construct (found under the Do…Loop Until command button of the frmControlStructures form) is another variation:

Sub cmdLoopUntil_Click()
Do
Me.txtAge.Value = Nz(Me.txtAge.Value) + 1
Loop Until Nz(Me.txtAge.Value) = 35
End Sub

As with the Do…Loop While construct, the Do…Loop Until construct doesn't evaluate the condition until the end of the loop, so the code in the loop is guaranteed to execute at least once.Chapter 17, it is not a good idea to reference a control over and over again in a loop. Notice that, in the looping examples, the txtAge control is referenced each time through the loop. This was done to keep the examples simple. To eliminate the performance problem associated with this technique, use the code that follows (found under the cmdEfficient command button on the frmControlStructures form):

Private Sub cmdEfficient_Click()
Dim intCounter As Integer
intCounter = Nz(Me.txtAge.Value)
Do While intCounter < 35
intCounter = intCounter + 1
Loop
Me.txtAge.Value = intCounter
End Sub

CAUTION

With

any of the looping constructs, it's easy to unintentionally cause a loop to execute endlessly, as is shown in this example and can also be illustrated with the code samples shown previously (this code is not included in Chap7Ex):

Sub EndlessLoop()
Dim intCounter As Integer
intCounter = 5
Do
Debug.Print intCounter
intCounter = intCounter + 1
Loop Until intCounter = 5
End Sub

This code snippet sets intCounter equal to 5. The code in the loop increments intCounter, and then tests to see whether intCounter equals 5. If it doesn't, the code in the loop executes another time. Because intCounter will never become equal to 5 (it starts at 6 within the Do loop), the loop executes endlessly. You need to use Ctrl+Break to exit the loop; however, Ctrl+Break doesn't work in Access's runtime version.

For…Next

The For...Next construct is used when you have an exact number of iterations you want to perform. It looks like this and is found under the For…Next command button of the frmControlStructures form:

Sub cmdForNext_Click()
Dim intCounter As Integer
For intCounter = 1 To 5
Me.txtAge.Value = Nz(Me.txtAge.Value) + 1
Next intCounter
End Sub

Note that intCounter is self-incrementing. The start value and the stop value can both be variables. A For…Next construct can also be given a step value, as shown in the following (the counter is incremented by the value of Step each time the loop is processed):

Sub ForNextStep()
' Note that this code is not in database Chap7ex.mdb
Dim intCounter As Integer
For intCounter = 1 To 5 Step 2
Me.txtAge.Value = Nz(Me.txtAge.Value) + 1
Next intCounter
End Sub

With…End With

The With…End With statement executes a series of statements on a single object or user-defined type. Here's an example (found under the With…End With command button of the frmControlStructures form:

Private Sub cmdWithEndWith_Click()
With Me.txtAge
.BackColor = 16777088
.ForeColor = 16711680
.Value = "40"
.FontName = "Arial"
End With
End Sub

This code performs four operations on the txtAge text box, found on the form it's run on. The code modifies the BackColor, ForeColor, Value, and FontName properties of the txtAge text box.

TIP

The With…End With statement offers two main benefits. The first is simply less typingyou don't need to repeat the object name for each action you want to perform on the object. The more important benefit involves performance. Because the object is referred to once rather than multiple times, this code runs much more efficiently. The benefits are even more pronounced when the With…End With construct is found in a loop.

For Each…Next

The For Each…Next statement executes a group of statements on each member of an array or collection. The following example (found under the For Each…Next command button of the frmControlStructures form) illustrates the use of this powerful construct:

Private Sub cmdForEachNext_Click()
Dim ctl As Control
For Each ctl In Controls
ctl.FontSize = 8
Next ctl
End Sub

This code loops through each control on the form, modifying the FontSize property of each control.

As in the following example, the With…End With construct is often used along with the For Each…Next construct:

Private Sub cmdForEachWith_Click()
Dim ctl As Control
For Each ctl In Controls
With ctl
.ForeColor = 16711680
.FontName = "Arial"
.FontSize = 14
End With
Next ctlEnd Sub

This code loops through each control on a form; the ForeColor, FontName, and FontSize properties of each control on the form are modified.Chapter 8, "Objects, Properties, Methods, and Events Explained," you will learn how to determine the type of an object before you perform a command on it. Knowing the type of an object before you try to modify its properties can help you prevent errors.