Variables

One of the most important concepts in programming is the use of variables. A variable is a location in memory where you can store a value while your code is running. VBA only needs to find an item's location the first time it is used in code; it does not need to look up the location each time. For example, if you need to specify a particular state repeatedly in your code, it can be much faster and cleaner to create a variable strState than to repeatedly use Washington in your code. Not only will the code run faster, but if you ever need to switch states and use California instead, all you have to do is change the value of your variable rather than find all instances of Washington in your code.

Using variables can also make your code easier to interpret. They not only define what VBA allows in the field, but they let you and other developers know what the field can be used for.

Variables hold a variety of types of data including strings, numbers, and objects. As you'll see shortly, a string variable is basically any combination of alpha or alphanumeric data, such as a phrase or name. Although it can store numbers, the numbers will not function as numbers in equations. This is explained a little later, too. The number types store values that can be used in mathematical equations. Objects include database objects and collections.

Properly declaring variables is one of the most important tasks you need to master to program in VBA. It's not hard, but it is a critical skill, and you'll explore the major rules and recommendations for variables in the next few sections.

Variable Data Types

In VBA, you can declare many different types of variables. Variables are named by the type of data that they are designed to hold. Make it a point to declare your variables with the correct type so that your code runs as efficiently as possible and provides more descriptive error messages. If you choose to not declare a data type, the variable is created as a variant. Variant variables are slower then explicitly defined data types and require significantly more space in memory than other types of variables. The following table lists the variable data types, the amount of memory they take, and the range of values that they can store.

Data Type

Size in Memory

Possible Values

Byte

1 byte

0 to 255.

Boolean

2 bytes

True or False.

Integer

2 bytes

-32,768 to 32,767.

Long (long integer)

4 bytes

-2,147,483,648 to 2,147,483,647.

Single (single-precision real)

4 bytes

Approximately -3.4E38 to 3.4E38.

Double (double-precision real)

8 bytes

Approximately -1.8E308 to 4.9E324.

Currency (scaled integer)

8 bytes

Approximately -922,337,203,685,477.5808 to 922,337,203,685,477.5807.

Decimal*

14 bytes

With no decimal places, the range is +/-79,228,162,514,264,337,593,543,950, 335 or with decimal places, the range is +/- 1E-28 (one to the 28th power). The smallest possible non-zero number is 0.000,000,000,000,000,000,000,000,000,1 written +/-1E-28.

Date

8 bytes

1/1/100 to 12/31/9999.

GUID

16 bytes

_2,147,483,648 to 2,147,483,647.

Object

4 bytes

Any object reference.

String - variable length

10 bytes + string length

Variable length: < about 2 billion.

String - fixed length

String length

Up to 65,400.

Variant - Number

16 bytes for numbers

Same as double.

Variant - String

22 bytes + string length

Same as string.

User-defined

Varies

Defined by user.

*The Decimal data type is included in this table although it cannot be used as a variable in a Dim statement. Rather, the Decimal data type can be a Variant subtype using the CDec() function.

*The Decimal data type is included in this table although it cannot be used as a variable in a Dim statement. Rather, the Decimal data type can be a Variant subtype using the CDec() function.

Notice that the VBA data types are similar to the data types in an Access table. The major differences between Access data types and VBA data types are that there is no equivalent to the variant or object data types in Access data types, and the Access Number data type has a field size property that enables you to specify the field as Byte, Integer, Long, Single, Decimal, or Double. Access has one other number data type that VBA doesn't: GUID, which can be 16 bytes. It's used for Replicationld. Properly specifying the field size this way saves memory and improves performance.

Number Data Types

When working with numeric data, you have a choice of seven different data types: Long, Double, Byte, Integer, Single, Currency, and Boolean. This section provides a brief introduction to data types and will provide the information you need to choose the proper data type for your purpose.

Each numeric data type provides a different level of accuracy, and they also use differing amounts of space. You can follow some general rules of thumb when choosing the right data type for your variable:

□ When creating a variable to store whole numbers, choose the Long data type.

□ When creating a variable to store fractional numbers, choose the Double data type.

□ To store a negative value, use Integer, Single, Double, Long, Currency, or Decimal data type.

□ If you need to store a value outside the range of -32768 to 32767, do not use the Integer data type because it can't handle numbers that large or small.

□ To control loops, always use an Integer data type.

□ The Boolean data type is often used in Yes/No or True/False scenarios. Any non-zero number evaluates to True (or Yes).

□ ReplicationlD is used only for replicated databases. Although Access 2007 supports replication using the MDB file format, the new ACCDB file format does not support replication.

There are a few key differences between the Single and Double data types. The Single data type allows you a precision level of 7 digits, whereas the Double data type gives you approximately 15 digits of precision. Both data types allow you to work with numbers larger than 2 billion.

You should never use Double or Single data types to represent fractional numbers. These cannot do so with extreme accuracy. (Although you may not notice the error for a while, those things tend to come back to haunt you at the most inopportune times.) Here's an example of using a Double data type with a fraction in a loop:

Sub TestFractionalDivision() 'Do not actually run this code!

'If need be, use Ctrl+Break to stop code execution Dim dblResult As Double dblResult = 1 / 5

'Demonstrates that dblResult will never truly equal 1. Do Until dblResult = 1

'The following line is and alternative comparison 'that will evaluate and stop execution.

'To test this, comment out the previous Do statement and 'Remove the comment mark from the following Do statement.

'Do Until dblResult >= 1

'Do something interesting -

'In this case, start with 1/5 and keep adding 1/5 to the result. dblResult = dblResult + 1 / 5

Loop End Sub

The loop will run forever because the value of dblResult never actually equals 1. You'll have to use Ctrl+Break to end this code loop. That's why you're always better off using integers for looping operations.

An alternative is to use a comparison of greater than or equal to one (>= 1), as shown in the comment in the preceding example. This approach may result in an extra iteration, but it does stop.

The Single and Double data types are floating-point data types. So they can handle numbers with the decimal point in a variety of places. The Currency data type is a fixed-point number, meaning that there are always four decimal places in a currency number to handle most types of international currency (of course, you don't need to display four decimal places). Just because the currency data type is generally used for dealing with dollars and cents, it's not limited to financial transactions. You can use the currency data type for any number with up to four decimal places.

Because the decimal is always in the same place, VBA actually performs a little trick with currency calculations. It removes the decimal points, performs the calculation on the integer numbers, and then puts the decimal points back. This speeds up the calculation while retaining the four-decimal-place accuracy.

Boolean Data Type

In addition to typical numeric data types, you can use a variety of other data types. Boolean variables can take only two values, True and False. If you've ever used VBScript, you're probably familiar with using numeric values to represent true and false. When you refer to a Boolean data type in VBScript, 0 is false and -1 is true. You can still use these values with Boolean data types. VBA interprets 0 as false, but it is critical to remember that any non-zero value is always interpreted as true. The following two code examples, for instance, produce the same result:

Sub TestIsTrue() Dim blnlsTrue As Boolean blnIsTrue = True If blnlsTrue = True Then

MsgBox "True" End If End Sub

Sub TestIsTrue() Dim blnIsTrue As Boolean blnIsTrue = 2 If blnIsTrue = True Then

MsgBox "True" End If End Sub

The Date Data Type

Remember the Y2K bug? Computers used to store dates with only two-digit years, which caused a problem when computers needed to start storing dates past the year 1999. After all, 01 could be a two-digit year code for 1901 or for 2001. Programmers scrambled for several years to fix the problem. So now computers store dates with a four-digit year. However, that wasn't the only problem with dates. Different countries represent dates in various formats. For example, the date 4/1/2007 could represent either April 1, 2007, or January 4, 2007, depending on the region of the world in which you live. To work around this challenge, VBA has a nifty way of dealing with dates. All dates are represented as a floatingpoint number (one with a flexible number of decimal places). When working with a date and time, the date portion is converted to an integer. The time is represented by the decimal portion of the number and is calculated as a percentage of a day. For example, noon would be half of a day and represented by 0.5. To determine the integer value for the date, an initial date is needed for calculations. Access uses December 30, 1899 as day 0, so April 1, 2007 would be represented as 39173, so it follows that 6:00 p.m. on that date is represented as 39173.75.

Rest assured that when you're working with dates in VBA, you do not have to perform conversions between 39173.75 and April 1, 2007, 6:00 p.m. VBA is aware of the regional settings specified in the user's Control Panel Regional Settings applet and converts the date to the proper format. So, if you're in Australia, VBA converts 39173.75 to 01/04/07. If you're in the United States, VBA displays 04/01/07 (or 04/01/2007 depending on your settings). VBA's use of calculated numbers for dates ensures that dates are always calculated correctly regardless of the specific regional settings used on the local computer. And, of course, you can further control how dates and times are displayed through code, as well as with a control's Format property.

Here's a brief example to illustrate how to work with VBA date calculations. As mentioned, a variable declared as a date has an initial value of December 30, 1899 (or 0 when converted to the floating-point decimal value). The following code produces a value of December 31, 1899.

Sub AddADay()

Dim dtInitialDate As Date

dtlnitialDate = DateAdd("d",

1, dtlnitialDate)

Debug.Print dtlnitialDate

End Sub

You can work with dates directly in VBA by assigning the literal date value to a date variable. To do so, use # to delimit your date. For example, to assign a value of April 1, 2007, to the variable dtlnitialDate, use the following code:

dtlnitialDate = #04/01/2007#

That ensures that VBA will recognize the date properly no matter what your regional settings are. However, if you're in a region of the world that enters dates with d/m/yyyy, you'll need to enter the literal date in the format m/d/yyyy when using this method. Otherwise, VBA won't recognize it properly.

The String Data Type

The String data type is fairly straightforward. You use it for all types of alphanumeric data including names, sentences, or phrases. Numbers stored as a string data type respond to sorts and calculations as characters rather than numbers, as illustrated in the following code snippet:

Sub FunWithStrings() Dim strBikes As String Dim strCost As String Dim intBikes As Integer Dim curCost As Currency strBikes = "5"

strCost = "100"

intBikes = 5

curCost = 100

Debug.Print strBikes +

strCost

Debug.Print intBikes +

curCost

End Sub

The first operation, Debug.Print strBikes + strCost produces a result of 5100. Because these are text values, the operation concatenates the two string variables. The second operation involves numeric data types, so Debug.Print intBikes + curCost actually performs the mathematical calculation and produces a result of 105. You'll immediately notice the impact when sorting a string of numbers.

When creating a string variable, the default value of the variable is a zero-length string. It isn't the same as a Null value; it's more like an empty value. You can assign pretty much any value to the string variable. The trickiest problem you're likely to encounter while using string variables in VBA is dealing with quotation marks within the content of the string. For example, the following is a valid way to assign a string value to a variable:

strCustomerName = "ABC Textiles"

The same syntax works if you need to store a value with a single quote in the name, such as the following:

strCustomerName = "Johnny's Cycles"

What if you need to store a value with a double-quoted character in the name? If you follow the same rules as the previous two examples, you end up with something like the following:

strCustomerName = "The "W" Hotel, New York City"

It might look like a valid line of code, but when you actually type that into your code, you'll get a compile error before you run the code. The problem is that VBA sees the second double quote and thinks you want to end the string. VBA doesn't quite know what to do with the text that follows that quote. To work around the issue, you use a double set of double quotes within your string, basically quoting the quote, so that your actual assignment statement looks like the following:

strCustomerName = "The ""W"" Hotel, New York City"

If you enter all strings with double quotes using that method, you'll get predictable results every time.

String Comparisons

You can't have a discussion about string variables without discussing how to work with strings. Keeping in mind that strings are typically text or alpha-numeric, some of the most common tasks you'll perform with strings include comparing two strings, finding matches to a partial string, or determining the length of a string. The next few paragraphs describe some of the tricks you can use to work with string variables.

When comparing two strings, you'll find that VBA is not case sensitive by default. In other words, California and CALIFORNIA are considered to be the same string. You can change this default behavior by editing the first line of your VBA code. When opening a module in VBA, the first line is Option Compare Database. Changing this line to Option Compare Binary has one immediate effect: All string comparisons are now case sensitive. The following table provides a summary of the three options you can select for the Option Compare statement in VBA. Keep in mind that these statements are module specific, so they only affect the module that they are in.

Compare Statement

Definition

Option Compare Database

String comparisons are case insensitive. Local settings of the current database are used.

Option Compare Binary

String comparisons are case sensitive. Local settings of the current database are ignored.

Option Compare Text

String comparisons are case insensitive. Local settings specified in Control Panel are used. This setting is seldom used.

Unless you have good reason to change the Option Compare statement, leave it at the default value of Option Compare Database. (You'll probably find that the default value is used in more than 99 percent of the modules.)

You don't always have to compare entire strings to each other. You can search for strings based on one or more characters in the string. For example, the following code illustrates a couple of types of string comparisons.

Sub CompareStrings() Dim strStringl As String strStringl = "Microsoft"

If strStringl Like "Micr*" Then

Debug.Print "True" End If

If strStringl Like "Mic*t" Then

Debug.Print "True" End If

End Sub

Both of these comparison operations return True. The first returns True whenever the first four letters of strStringl are Micr. The second comparison returns True whenever the first three letters of strStringl are Mic and the last letter is t.

The following table lists the key wildcards used for string comparisons.

Character

Description

? or _ (underscore)

Single character

* or %

Any number characters

#

One digit, 0-9

[] One character from the enclosed list

[!] One character not in the enclosed list

Keep in mind that characters are alpha, numeric, and special. The following table shows the variety of comparison operations that you can use in VBA.

Comparison Expression

Strings That Match

Strings That Do Not Match

Like "Mi*"

Microsoft, Michigan

MapPoint, Monochrome

Like "sol*d"

sold, solid

solids

Like "s?t"

sit, sat

seat

Like "177#"

1776, 1777, 1778, and so on

1977, 177, 1077

Like "s[ea]t"

set, sat

sit, seat

Like "s[!ea]t"

sit

set, sat

Like "s[aeio][aeio]t"

seat, soot

set, sat

As you can see from the table, the rules are fairly straightforward. The last three examples are the most confusing. Using two characters (or more) within the brackets tells VBA that any of the characters within the brackets can be used within the string; that means that a match can contain any one of the characters. Putting the exclamation point inside the brackets and before the characters tells VBA that any character except those within the brackets can be used to compare the string. Using two sets of bracketed characters tells VBA to match two characters—one from each set. You can also enclose a range in the brackets— for example, 3-6 would indicate the numbers 3, 4, 5, and 6.

Variants

The Variant data type is probably the most flexible data type within VBA. Unlike other variable types, which can only hold one type of data, the variant data type can hold many different types of data. You can use it to hold text, numbers, dates, and user-defined types. The only type of data a variant data type cannot hold is a fixed-length string. As previously explained, there is a price to be paid for such flexibility. As tempting as it might seem to use variant data types for all of your coding, the practice results in much higher memory use and means a performance hit compared to using the proper data type. For example, using a variant to hold a string of data requires 11 extra bytes of data for every string you store. Over the course of an entire application, these extra bytes can have a significant performance impact.

There are times, however, when you need to use a variant data type. For example, if you're not sure what type of information a variable needs to hold, it is likely best to use the variant data type. Most often, this type of unknown or unpredictable data configuration occurs when users are entering data in a relatively unstructured manner, such as on Web forms. If you're not sure whether users will enter a date, a number, or a string, you can create a variant data type and store the input in that variable. Another place to use a variant is for the variable of a function return. Look at this code segment:

MyField = DLookup("Lastname", "Employees", "[Lastname] = 'Doe'")

Even though you know the answer will be a string, if Doe does not exist, DLookup will return a Null and an error will be generated if MyField is declared as a string. Of course, there are other ways to handle Nulls, but this will work and it provided such a good segue.

Nulls

We should discuss another important concept when talking about variables, and that is Null. When you first learn programming, you learn that Null is the value of a field with no data. If you create a field in a table and don't fill it with any data, the value of the field is Null. So what is Null? Null is nothing, but Null is also something. It sounds a bit confusing, because it is. Here's an example to help demonstrate just how mind-boggling Null can be.

Sub CompareNulls()

Dim varValuel As Variant

Dim varValue2 As Variant varValuel = Null varValue2 = Null

If varValuel = varValue2 Then

Debug.Print "Nulls are equal" End If End Sub

If you run this code, you'll see that the phrase never prints in the Immediate window. But how can that be? You just set the two variables equal to the same value. Well, you might think you set them to the same value, but really, two Nulls never equal each other.

There are two rules to remember when working with Null. First, you cannot assign Null to anything other than a Variant data type. So, you can never have a Null string; instead you have a zero-length string. You cannot have a Null single or double (numeric data type); instead you have a variable with value 0. The second rule of Null is, as previously demonstrated, that no two Nulls match. Null doesn't match zero; it doesn't match a zero-length string; it doesn't even match itself. The only comparison operation you can run on Null is to determine whether it is Null. But, as mentioned previously, Null never equals Null. If you're thinking, great, now what can I do?, you'll be relieved to learn that there are relatively straightforward processes to test for and work with Null. And, by slightly modifying the previous code sample, you get a completely different result and the print phrase will appear in the Immediate window, as shown in the following example:

Sub CompareNulls2() Dim varValuel As Variant

Dim varValue2 As Variant varValuel = Null varValue2 = Null

If IsNull(varValuel) And IsNull(varValue2) Then

Debug.Print "Both variables are Null" End If End Sub

This code sample prints the phrase in the Immediate window. By independently testing each variable for Null, you can compare the results. You test for the Null condition by using the IsNull function, which evaluates to True if the value of the variable is Null and to False if the value of the variable is not Null.

Just remember the two basic rules of Null. Null doesn't equal anything and Null equals nothing.

Nulls also react differently when used in mathematical formulas instead of string operations. To quickly test what happens, enter these simple lines with the question marks in the Immediate window. For illustration purposes, the results are included after each example. Remember that "5" is a string.

?5 +

Null

' number with

a

math formula

Null

?"5"

+ Null

' string with

a

math formula

Null

?5 &

Null

' number with

a

string concatenation

5

?"5"

& Null

' string with

a

string concatenation

As you can see, in the math formula using addition (+), Null is the result. However, in the string concatenation operation (&), Null drops off, which is kind of like adding a zero. Keeping this in mind can be useful when working with strings. Consider the following code that concatenates fields for first, middle, and last names plus tag line, and adds spaces and punctuation:

Public Function

Which2Use()

Dim strMI As Variant

'Declared as

a

variant so

that you can use a Null value.

strMI

= Null

Debug.

Print 1

'John"

& " "

& strMI

& 1

' . " & 1

Doe

- Null with & (string operator)"

Debug.

Print 1

'John"

& " "

& strMI

+ 1

' . " & 1

Doe

- Null with + (math equation)"

Debug.

Print

strMI

= "P"

Debug.

Print 1

'John"

& " "

& strMI

& 1

' . " & 1

Doe

- P with & (string operator)"

Debug.

Print 1

'John"

& " "

& strMI

+ 1

' . " & 1

Doe

- P with + (math equation)"

End Function

John . Doe John Doe

Null with & (string operator) Null with + (math equation)

The first example with Null uses a string operator to concatenate the values, so the period and extra spaces print. The second line uses the numeric plus function; Null added to a period and space returns Null, so the period and space are removed.

In the second set of examples, Null is not a factor, so adding and concatenating text yield the same result.

User-Defined Data Type

VBA provides a way to group different data types under one heading. This creates an object with a set of properties. You define the group with the word Type on a Module level and by default it is Public. You can use the Private keyword if you want to restrict it to the module only. Here's an example:

Option Compare Database Option Explicit

Public Type WeatherElements Type As String Temp As Single Protect As Boolean End Type

Public Function Action()

Dim Yesterday As WeatherElements

Dim Today As WeatherElements

Today.Type = "Sunny" Today.Temp = 64.3 Today.Protect = False

Yesterday.Type = "Snow" Yesterday.Temp = 31.2 Yesterday.Protect = True

Debug.Print "The weather took a turn for the better!" Debug.Print "Readings: Yesterday Today" Debug.Print "Weather: " & Yesterday.Type & " Debug.Print "Temperture: " & Yesterday.Temp & " Debug.Print "Protection: " & Yesterday.Protect & "

" & Today.Protect

End Function

As you can see, several elements (data types) are grouped under the object WeatherElements and a new data type is assigned to two different variables. By using the variable name followed by the dot and then the element name, you get the value of that element. When you run the preceding code sample, this is what you should see in the Immediate window.

The weather took a turn for the better! Readings: Yesterday Today Weather: Snow Sunny

Temperature: 31.2 64.3

Protection: True False

Using Variables

If you're using a large number of variables in your application, it's easy to forget that the last line of code you just entered contained a new variable. Fortunately, VBA has an option that requires you to declare all of your variables before you use them—it causes VBA to produce an error for every undeclared variable. You'll see what it is in just a minute. Keep in mind that VBA's gentle reminder (error message) occurs when you run your code, not when you are writing it.

As mentioned earlier, it is good programming practice to declare all of your variables. This practice ensures that your variables have the proper types and can speed code execution. Additionally, if you don't declare your variables, they can actually get you into trouble. The following code sample illustrates this:

Sub BuyNewBike()

Dim curBikePrice as Integer curBikePrice = InputBox("Please enter bike price.", "Enter Bike Price")

If curVikePrice > 100 Then

MsgBox "Don't Buy the Bike! It's too expensive!", _ vbCritical, "Bike Purchase"Else

Else

MsgBox "You can buy the bike. It's within your budget.", _ vbOKOnly, "Bike Purchase"

End If End Sub

This code sample looks simple enough. You enter the price of the bike in the Input box that appears on the screen when you run this code. If the price of the bike is greater than $100, the program tells you not to buy the bike. If the price is less than $100, you can buy the bike.

However, the code won't actually work. Examine it carefully and you'll see that the variable that accepts the price of the bike, curBikePrice, isn't the same as the variable used in the If...Then statement, curVikePrice. A mistake like that is quite easy to make. You're typing along and you hit the wrong key. You probably wouldn't even notice it until you run the code and nothing happens. As it's written, there's actually nothing wrong with the code. It won't produce any errors; it just won't produce the required results.

There is one easy way to prevent this type of mistake, and that's the VBA option mentioned earlier. Just add a single line of code to the General Declarations section of your code: Option Explicit. These two words tell VBA that all variables used in your module must be declared before they can be used. If you force variable declaration, then typing the wrong name for a variable causes VBA to display an error when the code is compiled, as shown in Figure 5-1.

[(General) ~ jBuyNewBike

Option Corrpare Ddtstsse

Option Explicit_

■this rf.odule demonstrates use of Option Explicit

Sub BuytIewBike()

Dirr. curBikeErice As Integer curBikeFrice — InputBox ("Flease enter the tike price."r _ "Enter Bike Frice")

■Note that Vike instead of Bike causes a corf.pile error

Zf curVikeFrice > 100 Then Microsoft Visual Basic

MacBcx "Don't Buy the B vbCritical, "Bike F

Else Compile error:

vbOKOnly, "Bike Fur Variable not defined

End If

OK Help

Figure 5-1

Once the error message is displayed, VBA highlights the undeclared variable(s), so that you can quickly see and correct the errors. For this situation, it is to either correct the spelling of the variable to match the declared variable or to add a new line of code to declare the variable used in your code.

Always use Option Explicit in your modules. You can configure Access to do this for you. From anywhere in the Access VBA Editor, select Tools O Options. Choose the Editor tab and check the box marked Require Variable Declaration. Click OK to save your changes. Now, whenever you create a new module or build code behind any Access form or report, you'll be prompted to declare your variables. The Editor only adds Option Explicit as new modules are created, so you will need to insert that phrase at the top of each existing module.

Declaring Variables

You declare a variable by use of the Dim (short for dimension) keyword, and then the variable name, followed by the word As, and then the variable type. For example, the following statement declares the state variable in your procedure:

Dim strState As String

Once you dimension your variable, you can assign it a value anywhere in your procedure. For string variables such as strState, use the following statement to assign the value "Washington":

strState = "Washington"

Now that your variable has a value, you can use it repeatedly within your procedure. Consider the following code segment:

Private Sub OpenDatabaseConnection()

Dim objConn As ADODB.Connection Dim objRST As ADODB.Recordset Dim strSQL As String Dim strConn As String Dim strState As String

'Create the ADODB Connection and Recordset Objects Set objConn = CreateObject("ADODB.Connection") Set objRST = CreateObject("ADODB.Recordset")

'Open your ADODB Connection strConn = "Provider=Microsoft.ACE.OLEDB.l2.0;Data Source=c:\Cust.mdb;" strSQL = "Select * from tblCust WHERE CustState = '" & "Washington" & "';"

objConn.Open (strConn) objConn.Mode = adModeRead objRST.Open strSQL, objConn, adOpenForwardOnly, adLockOptimistic objRST.MoveFirst

'Print relevant customer information

While Not objRST.EOF

Debug.Print objRST.Fields("CustName") Debug.Print objRST.Fields("CustState") Debug.Print objRST.Fields("CustCountry") objRST.MoveNext Wend 'while end - closed the while loop objRST.Close objConn.Close

'Release your variables Set objRST = Nothing Set objConn = Nothing

End Sub

This code opens an ActiveX Data Objects (ADO) connection to an Access database. It then opens a recordset of all customers in Washington and prints their names, state, and country in the Immediate window. This is pretty simple code. You're referencing the state in only one place, so you really do not need to create a variable to hold the state name. If you know without a doubt that you'll never need to change your code and you won't need the same value later in your code, you could just use the actual value. However, consider the situation where users are allowed to input their own state name. If you've hard-coded the state name into the procedure, you have no way to switch states when the user needs to do so. You could adapt the previous code through the use of a variable and input box to allow users to select the state they need. Here's how that code might look:

Private Sub OpenDatabaseConnection()

Dim objConn As ADODB.Connection Dim objRST As ADODB.Recordset Dim strSQL As String Dim strConn As String Dim strState As String

'Create the ADODB Connection and Recordset Objects Set objConn = CreateObject("ADODB.Connection") Set objRST = CreateObject("ADODB.Recordset")

'Open your ADODB Connection strConn = "Provider=Microsoft.ACE.OLEDB.12.Q;Data Source=c:\Cust.mdb;"

strState = InputBox("Please enter a state", "Enter State")

strSQL = "Select * from tblCust WHERE CustState = '" & strState & "';"

objConn.Open (strConn) objConn.Mode = adModeRead objRST.Open strSQL, objConn, adOpenForwardOnly, adLockOptimistic objRST.MoveFirst

'Print relevant customer information

While Not objRST.EOF

Debug.Print objRST.Fields("CustName") Debug.Print objRST.Fields("CustState") Debug.Print objRST.Fields("CustCountry") objRST.MoveNext

Wend objRST.Close objConn.Close

'Release your variables Set objRST = Nothing Set objConn = Nothing

End Sub

Using the preceding code, users can enter any state in response to the input box and your code will run and return the appropriate records.

There is still a key element missing from this procedure—error handling. As you know, or will soon discover, in addition to trapping for events triggered by code, error handling can help control what users are able to do and how data is handled. For example, if users enter Sacramento as a state, misspell Mississippi, or simply choose a state for which no records exist in your database, the preceding code will generate an error. Chapter 9 focuses on error handling.

Was this article helpful?

0 0

Post a comment