Section 17.1. Exploring the VB Language | Access 2007[c] The Missing Manual

17.1. Exploring the VB Language

Although you now know enough to react to events and change control properties, there's still a lot to learn about the Visual Basic language itself. In the following sections, you'll learn how to use variables , conditional logic, and loops to write more powerful code. Finally, you'll see how to use these features to build a more complex code routine that checks for invalid credit card numbers .

17.1.1. Storing Information in Variables

Every programming language includes the concept of variables , which are temporary storage containers where you can keep track of important information.

Suppose you want to swap the content in two fields. On the surface, this operation seems fairly straightforward. All you need to do is take the text from one text box, place it in the other, and then insert the second box's text in the first box. Here's a first crack at a solution:

 TextBoxOne.Value = TextBoxTwo.Value TextBoxTwo.Value = TextBoxOne.Value

To make this code work, you need to put it in the right subroutine. In this example, the code runs when someone clicks a button in a form. You can create the sub-routine you want for the On Click event using the Property Sheet. (See Section 16.2 for a refresher.)

Sadly, this code's doomed from the start. Figure 17-1 illustrates the problem.

Figure 17-1. Top: Initially, each text box has its own information.
Bottom: After running your swap code routine, you don't get the result you expect. Once you paste the new content into the second text box, you end up overwriting the content you want to put in the first text box. The end result's two text boxes with the same content.

The easiest way around this problem is to use a variable to keep track of the information you need. To create a variable in VB, you use the oddly named Dim keyword (short for dimension , which is programmer jargon for "create a new variable"). After the word Dim, you enter the variable's name .

Here's how you'd create a variable named TextContent:

 Dim TextContent

POWER USERS' CLINIC
Using Smarter Variables

The example in Section 17.1 shows the simplest way to create a variable in VB code. It creates a variable that's known as a variant , which means it can store any type of content, including text, numbers, True/False values, and so on. Advanced VB programmers often prefer to be stricter and explicitly identify the data type for each variable they create. That way, nobody can accidentally store text in a variable that's intended for numeric content, and vice versa.

To create a variable that has a locked-in data type, you add the As keyword to your declaration. Here's how you create a TextContent variable for storing text only:

 Dim TextContent As String

And here's a variable that stores a large integer:

 Dim NumberContent As Long

This approach is good coding style, and it can help you catch certain types of mistakes. However, in order to use this approach, you need to be familiar with the various Visual Basic data types. The most commonly used ones are String, Date, Boolean (a True or False value), Long (an integer that can be very small or very big), Single (a number that can be fractional ), and Currency (a numeric data type that's ideal for storing financial amounts).

You can find a reference of all the VB data types in the Access Help. To get there, choose Help Microsoft Visual Basic help from the menu in the Visual Basic editor. Then, follow these links: Visual Basic for Applications Language Reference Visual Basic Language Reference Data Types.

Once you've created the variable, you're free to put information in it, and take information out. To perform both these operations, you use the familiar equal sign, just as you would with properties.

Here's an example that stores some text in a variable:

 TextContent = "Test text"

The following code puts all these concepts together. It uses a variable to swap the content of two text boxes.

 Dim TextContent ' Copy the text from the first text box for later use. TextContent = TextBoxOne.Value ' Change the text in the first text box. TextBoxOne.Value = TextBoxTwo.Value ' Change the text in the second text box, using the variable. TextBoxTwo.Value = TextContent

17.1.2. Making Decisions

Conditional logic, another programming staple, is code that runs only if a certain condition's true. There's no limit to the number of ways you can use conditional logic. You may want to prevent an update if newly entered data doesn't check out. Or you may want to configure the controls on a form differently, depending on its data. You can do all this, and more, using conditional logic.

All conditional logic starts with a condition : a simple expression that can turn out to be true or false (programmers call this process evaluating to true or false). Your code can then make a decision to execute different logic depending on the condition's outcome. To build a condition, you need to compare a variable or property using a logical operator like = (equal to), < (less than), > (greater than), and <> (not equal to). For example, Price = 10 is a condition. It can be true (if the Price field contains the number 10), or false (if Price contains something else). You've already used conditions to create validation rules (Section 4.3) and filter records in a query (Section 6.2.1.1). Visual Basic conditions follow a very similar set of rules.

On its own, a condition can't do anything. However, when used in conjunction with other code, it can become tremendously powerful. Once you've created a suitable condition, you can put it inside a special structure called the If block . The If block evaluates a condition, and runs a section of code if the condition's true. If the condition isn't true, Access completely ignores the code.

Here's an If Block that checks whether the Price field's value is greater than 100. If it is, Access displays a message:

 If Price > 100 Then MsgBox "I hope you budgeted for this." End If

Note that the If block always starts with If and ends with End If. Inside the If block, you can put as much code as you want. This is the conditional codeit runs only if the condition's true.

An If block can also evaluate several different conditions. Here's an example that calculates the fully taxed price of a product, and then displays that in a label. The trick's that the tax rate depends on another field (the Country), which is where the conditional logic comes into play.

 ' Store the tax rate you want to use in this variable. Dim TaxRate If Country = "U.S.A." Then     ' Taxes are charged for U.S. customers (7%).     TaxRate = 1.07 ElseIf Country = "Canada" Then     ' Even more taxes are charged for Canadian customers (14%).     TaxRate = 1.14 Else     ' Everyone else gets off with no tax.     TaxRate = 1 End If ' Display the final total in a label. TotalWithTax.Caption = Price * TaxRate

Only one segment of code runs in an If block. In this example, Access works its way through the block, testing each condition until one matches. As soon as it finds a match, it runs the conditional block of code, jumps down to the closing End If, and then continues with any other code that's in the subroutine. If no condition matches, then Access runs the code in the final Else clause (if you've added it). Figure 17-2 shows this code in action.

Figure 17-2. Thanks to conditional logic, this subform shows the correctly calculated total price at all times, taking into account both the current price and the customer's country. It works by responding to the On Current event, which occurs every time a record's displayed in the form.

These examples only scratch the surface of what careful conditional logic can do. You can use And and Or keywords to combine conditions, put one conditional block inside another, and much more.

In Chapter 15, you saw an example that performed a specific type of validation with customer records (Section 15.6.2). This validation worked using two fields: WantsEmail and EmailAddress. If the WantsEmail field was set to Yes, then the EmailAddress field couldn't be empty. However, if WantsEmail was set to No, then a blank EmailAddress was completely acceptable. You can implement the identical validation logic using VB code, but there's a twistit uses two If blocks (a line-by-line explanation follows the code):

 1 Private Sub Form_BeforeUpdate(Cancel As Integer)       ' Check if this person wants the email. 2     If WantsEmail = True Then           ' Make sure the EmailAddress isn't blank or null. 3         If EmailAddress = "" Or IsNull(EmailAddress) Then               ' This is considered invalid.               ' Cancel the change and show a message. 4             MsgBox "You can't be notified without an email address." 5             Cancel = True 6         End If 7     End If 8 End Sub

Here's how it works:

Line 1 declares a code routine that handles the Before Update event of the form. Notice that this event handler gets one piece of informationa true or false value named Cancel, which you can set to stop the update.
Line 2 starts an If block that checks if the WantsEmail checkbox has a check-mark in it.
Line 3 performs a second check. It's a bit more complex because there are two things that can cause the conditional code to run. It runs if the email address is a blank value (which happens if someone enters an email address, and then deletes it) or if the email address is null (which means that an email was never entered in the first place; see Section 4.1.2 for a discussion of null values).
Line 4 shows an explanatory error message. Remember, the code gets to this spot only if both the If blocks evaluate to true. If either check turns out false (the WantsEmail checkbox isn't turned on, or the EmailAddress is supplied), Access breezes right on past.

Note: Technically, you could combine both these If blocks into a single If block by writing a more complex condition that checks for everything at once. However, getting this right (and understanding what you've written later on) is more difficult. Veteran programmers know that it's always better to write code clearly, even if that makes the code a little more verbose.
Line 5 cancels the update using the Cancel parameter that the On Before Update event provides. That way, the change doesn't go ahead and the record remains in edit mode.
Lines 6 to 8 finish up by closing both If blocks, and ending the subroutine.

Access has many events you can cancel, like On Before Update. Look for the Cancel parameter between the parentheses after the subroutine name. If it's there, you can set it to True to stop the action that's about to occur.

17.1.3. Repeating Actions with a Loop

A loop is a tool that lets you repeat an operation as many times as you want. Visual Basic has several types of loops you can use. The most popular are the Do/Loop block, and the For/ Next block, both of which you'll see in this section.

Here's an example Do/Loop block that's sure to infuriate people:

 Do     MsgBox "Ever ever get that nagging deja vu feeling?" Loop

When Access enters this block of code, it starts by displaying the Message box and pausing your code. Once you click OK, the code continues until Access reaches the final Loop statement at the bottom of the loop. At this point, Access automatically jumps back to the beginning (the Do statement) and repeats your code, showing a second Message box. However, there's one problemthis process continues forever! If you make the mistake of running this piece of code, your database will be locked up indefinitely (until you press the emergency-stop key combination, Ctrl+Break).

To avoid this situation, you should build all loops with an exit condition , a condition that signals when the loop should end. Here's a rewritten version of the same loop that stops after it's shown a message five times:

 ' Keep track of how many times you've looped. Dim NumberOfTimes ' Start the count at 0. NumberOfTimes = 0 Do     MsgBox "Ever ever get that nagging deja vu feeling?"     ' Up the count by 1.     NumberOfTimes = NumberOfTimes + 1 Loop Until NumberOfTimes = 5

The important bit's the final clause at the end of the loop block, Until NumberOfTimes = 5 . This clause defines a condition, and as soon as it's true (the NumberOfTimes variables reaches 5) and Access reaches the end of the loop, it jumps out and continues running the rest of your subroutine.

If you have this type of code, where you're looping a fixed number of times, then you may be interested in the For/Next loop. The For/Next loop is exactly the same as the Do/Next loop, except that it has a built-in counter, which it increments for you.

Here's how you could rewrite the previous example in a more compact form with a For/Next loop:

 Dim NumberOfTimes For NumberOfTimes = 1 To 5     MsgBox "Ever ever get that nagging deja vu feeling?" Next

The important part's NumberOfTimes = 1 To 5 , which tells Access to start NumberOfTimes at 1, to increment it by 1 at the beginning of each pass through the loop, and to stop after the fifth pass.

The Do/Loop block works well when you need to move through a collection of data. You can use the loop to keep going until you run out of information, even though you don't know how much information there is when you first start the loop. You'll see an example of this technique at the end of this chapter (Section 17.4.5), when you perform a batch update on your database using code.

On the other hand, the For/Next loop shines when you can determine at the out-set exactly how many times you want to repeat a loop. You'll see an example where this is true later in this chapter (Section 17.1.5), when you test credit card numbers.

17.1.4. Creating Custom Functions

You've already learned how to create your subroutines. But you haven't yet seen how to create their big brother, functions .

Like a subroutine, a function's a self-contained piece of code that can hold as many or as few statements as you want. And like subroutines, you add functions to modules. In fact, any number of subroutines and functions can exist side by side in a module.

 Function DoSomething()     ' Function code goes here. End Function

The key difference between functions and subroutines is that a function produces a final result . In other words, functions give you a piece of information that you may need.

You set the result by writing a line of code that assigns the result value to the function name. (Essentially, you pretend that the function name's a variable where you can stuff some data.) Here's an example:

 Function GetMyFavoriteColor()    GetMyFavoriteColor = "Magenta" End Function

This function's named GetMyFavoriteColor. The result's the text string "Magenta".

Calling a function is slightly different than calling a subroutine. To call a subroutine, you use the module name, followed by a period, followed by the subroutine name. You can use the same technique with a function, as shown here:

 MyModule.GetMyFavoriteColor

However, there's a problem. This step triggers the GetMyFavoriteColor function, causing its code to run, but it tosses away the result (the string with the text "Magenta").

If you're interested in the result, then you can call your function as part of an assignment statement. The following code creates a variable, uses it to store the result, and then displays the result in a Message box:

 ' Create a variable to store the result in. Dim Color ' Call the function and hold on to the result in the variable. Color = MyModule.GetMyFavoriteColor ' Show the result in a Message box. MsgBox "Your favorite color is " & Color

If you're really clever, you can shorten this code to a single line and avoid using the Color variable altogether:

 MsgBox "Your favorite color is " & MyModule.GetMyFavoriteColor

The GetMyFavoriteColor function's particularly simple because it doesn't use any arguments. But there's no reason you can't get a little fancier. Consider the following custom function, which takes two argumentslength and widthand calculates the total area by multiplying them together:

 Function Area(Length, Width)     Area = Length * Width End Function

The two parameters are defined in the parentheses after the function name. You can add as many parameters as you want, as long as you separate each one with a comma.

Here's how you call this function and show the result. In this example, fixed numbers are used for the Length and Width parameters. However, there's no reason you can't substitute a field name, variable, or property that you want to use with the Area function instead.

 MsgBox "The area of a 4x4 rectangle is " & Area(4, 4)

This displays the message "The area of a 4x4 rectangle is 16."

Neither the GetMyFavoriteColor( ) nor Area( ) function shows you anything particularly impressive. But in the next section of this chapter, you'll build a much more powerful custom function that tests credit card numbers.

POWER USERS' CLINIC
Using a Custom Function in a Query

Once you've created a function, you can use it anywhere in your database to build queries and validation rules. The only requirements are that your function must be in a custom module that you've added (not a form module), and it can't include the word Private in the declaration. If your function meets these rules, then you can call upon it just as easily as a built-in Access function.

You could create a query with a calculated field like this ( assuming the query includes two fields named LengthOfRoom and WidthOfRoom, respectively):

 RoomArea: Area(LengthOfRoom, WidthOfRoom)

Or, you could build a table validation rule like this:

 Area(LengthOfRoom * WidthOfRoom) < 10000

See Chapter 7 for some more ideas about using functions in calculated fields, and Chapter 4 for more information about validation rules. And if you want to see this specific example in action, check out the MyHouse database that's included with the samples for this chapter.

17.1.5. Putting It All Together: A Function for Testing Credit Cards

Now that you've made your way around the Visual Basic language, it's time to wrap up with an example that demonstrates everything you've learned about VB (and a little bit more).

In this example, you'll consider a custom function called ValidateCard that examines a credit card number. The ValidateCard function returns one of two results: True (which means the card number's valid) and False (which means it's not).

It's important to understand that a valid credit card number's simply a number that meets all the not-so-secret rules of credit card numbering (the box in Section 17.1.5 tells you more). This number may be attached to a real credit card, or it may not. The ValidateCard function's just smart enough to catch inadvertent errors and not-so-bright computer hackers. Truly sneaky people can find programs that let them generate potentially valid credit card numbers.

Here's the full code for the ValidateCard function. Each code statement's numbered so you can break it down one piece at a time (a line-by-line explanation follows the code):

 1 Function ValidateCard(CardNumber As String)       ' This is the running total (created using Luhn's algorithm). 2     Dim SumOfDigits 3     SumOfDigits = 0       ' This keeps track of whether you're at an odd or even position.       ' You start on an odd number position (1).

UP TO SPEED
The Luhn Algorithm

The ValidateCard uses something called the Luhn algorithm , which was developed by an IBM scientist in the 1960s. The Luhn algorithm works because credit card companies follow its rules. In other words, they issue only numbers that are considered valid according to the Luhn algorithm.

For a complete explanation of the Luhn algorithm, check out http://en.wikipedia.org/wiki/Luhn_algorithm. Here's the Reader's Digest version of what it does:

Starting at the end of the end of the credit card number, double the value of every second digit. Leave the even-numbered digits alone. For example, 1111 becomes 2121.
If this doubling process produces a number larger than 9, add the two digits in that number together. For example, 1166 becomes 2136. The second-to-last number 6 was doubled (to 12) and the digits (1 and 2) were totaled (to make 3).
Add all these digits together. If you're currently left with 2136, then calculate 2+1+3+6 (which makes 12).
If the total ends in 0 (or, put another way, if the total's divisible by 10), the number's valid. Otherwise it's not.

The Luhn algorithm checks to see if the number you've supplied is a possible credit card number. However, the Luhn algorithm can only do so much. It can't catch a credit card number that's technically valid but not actually hooked up to an account (and it obviously can't determine whether someone's credit card account's in good standing and has the required purchasing limit).

 4       Dim OddNumbered 5       OddNumbered = True 6       Dim i 7       For i = Len(CardNumber) To 1 Step -1 8           Dim CurrentNumber 9           CurrentNumber = Mid(CardNumber, i, 1) 10          If OddNumbered = False Then                 ' Double the digit. 11              CurrentNumber = CurrentNumber * 2 12              If CurrentNumber >= 10 Then                     ' If this number is two digits, add them together.                     ' This is the wacky part, because you need to use                     ' string conversion functions. 13                  Dim NumText As String 14                  NumText = CurrentNumber 15                  CurrentNumber = Val(Left(NumText, 1)) + 16                    Val(Right(NumText, 1)) 17              End If 18          End If             ' Add the number to the running total. 19          SumOfDigits = SumOfDigits + CurrentNumber             ' Switch from odd to even or even to odd.             ' This line of code changes True to False or             ' False to True 20          OddNumbered = Not OddNumbered 21      Next         ' If the sum is divisible by 10, it's a valid number. 22      If SumOfDigits Mod 10 = 0 Then 23          ValidateCard = True 24      Else 25          ValidateCard = False 26      End If 27 End Function

Here's how it works:

Line 1 declares the function. Notice that the function takes one parameter, which is the text with the credit card number. This parameter's explicitly identified as a string with the As String clause. This way, you avoid errors where someone might try to pass in an actual number.
Lines 23 create the variable that stores the running total during the whole process.
Lines 45 create the variable that keeps track of whether you're on an odd number position or an even number position. Remember, all second numbers must be doubled.
Lines 67 start a For/Next loop. This loop looks a little different from the ones you saw earlier, because it has the Step -1 clause at the end. This clause tells the loop to subtract 1 from the counter after every pass (rather than adding 1, which is the standard behavior). You can work your way from the end of the number to the front.

Note: The For/Next loop uses another trick. The lower limit's set using the Len function (Section 7.2.5), which gets the length of a piece of text. In other words, if the credit card is 11 digits, this code runs 11 times (once for each digit).

Lines 89 grab the number at the current position, as indicated by the loop counter. The Mid function lets you snip out a single digit. (You learned about the Mid function in Section 7.2.5.)
Line 10 checks if you're on a second number.
Lines 1117 run only if you're on a second number. In this case, the number needs to be doubled (line 11). If the doubled number has two digits, these digits must then be combined (lines 1315).
Line 19 adds the current number to the running total. If you were in an even-numbered position, then the number hasn't been changed. If you were in an odd-numbered position, then it's been doubled and combined.
Line 20 makes sure that if you just processed an even-numbered position, you're switched to an odd-numbered position (and vice versa).
Line 21 returns to line 6 and repeats the loop for the next digit in the credit card number.
Lines 2226 check the final total. If it's divisible by 10, it's valid. To find out, this code uses the Mod operator, which performs division, and then gets the remainder. (If there's no remainder when you divide a number by 10, you know it divided evenly without a problem.)

It may take some time to work through the function and figure out exactly what's going on, but in the end it's all about VB basics like conditions, loops, and variables. If you really want to study this example, you can watch it in action, using the debugging techniques that are covered in Section 17.2.1.

Once you've finished creating a function like ValidateCard, you can call it to test whether a credit card checks out. Here's an example that reacts when credit card information's entered into a text box named CardNumber:

 Private Sub CardNumber_BeforeUpdate(Cancel As Integer)     If ValidateCard(CardNumber) Then         MsgBox "Your card is valid."     Else         MsgBox "Your card is invalid. " & _           "Did you forget a number, or are you trying to cheat us?"         Cancel = True     End If End Sub

To try it out, run this code and supply one of your credit card numbers in the CardNumber field, as shown in Figure 17-3. Or send your credit card numbers to the author so he can conduct his own extensive testing.