Home

The Types of Values

 

Variables

 

Introduction

Although you can create a complete database without writing code, in some cases, some tasks cannot be performed automatically. In this cases, you must temporarily use values that you can change at will and dismiss when not needed anymore. A variable is a value that you "put" into the computer memory when necessary and the value can be lost when the application closes. To proceed, you must communicate to the computer that you will need a portion of its memory to hold a certain value. When you communicate this, the computer reserves the necessary portion for you and makes it available when you need it.

Communicating your intention is also referred to as declaring a variable. Because there can be various values used while the application is running, the computer would need two pieces of information to hold a value: a name that can be used to identify the portion of memory and the amount of memory that will be necessary to store the value.

The Name of a Variable

Every variable you intend to use in your application must have a name. This name allows you to identify the area of memory that would have been reserved for a variable. There are rules you must observe when naming your variables. The rules are those of Microsoft Visual Basic (and not Microsoft Access):

  • The name must begin with a letter (such as a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t, u, v, w, x, y, z, A, B, C, D, E, F, G, H, I, J, K, L, M, N,  O, P, Q, R, S, T, U, V, W, X, Y or Z) or an underscore _
  • The name cannot contain a period (.) or a special character (such as ! @ # $ % ^ & * ( ) _ + - = [ ] { } ; ' : " , . / < > ? \ | ` or ~)
  • The name must not contain an empty space
  • The name must not exceed 255 characters. You should limit the name of a variable to 30 characters
  • The name must be unique in the same scope

Besides, or on top of, these rules, you can add your own conventions that would make your code easier to understand.

Practical LearningPractical Learning: Introducing Variables

  1. Start Microsoft Access and, from the resources that accompany this ebook, open the Exercise1 database
  2. In the Database window, click the Forms button
  3. To create a form, double-click Create Form in Design View
  4. On the Standard toolbar, click the Code button Code
  5. In the Object combo box, select Detail
  6. In the Procedure combo box, select Click
  7. Press Tab and type the following:
     
    Private Sub Detail_Click()
        SomeColor = vbRed
        
        Detail.BackColor = SomeColor
    End Sub
  8. Return to Microsoft Access and display the form in Form View
  9. Click the form and notice that it appears red
  10. After using the form, switch it back to Design View and return to Microsoft Visual Basic

Variable Declaration

When writing your code, you can use any variable just by specifying its name. When you provide this name, the computer directly creates an area in memory for it. Microsoft Visual Basic allows you to directly use any name for a variable as you see fit. If you use various variables like that, this could result in some confusion in your code. As mentioned earlier, you can first declare a variable before using it.

To declare a variable, you use the Dim keyword followed by the name of the variable. Here is an example:

Private Sub Form_Load()
    Dim BackgroundColor
End Sub

Declaring a variable simply communicates to Visual Basic the name of that variable. You can still use a mix of declared and not-declared variables. If you declare one variable and then start using another variable with a similar but somewhat different name, Microsoft Visual Basic would still consider that you are using two variables. This can create a great deal of confusion because you may be trying to use the same variable referred to twice. The solution to this possible confusion is to tell Microsoft Visual Basic that a variable cannot be used if it has not been primarily declared. To communicate this, on top of each file you use in the Code Editor, type

Option Explicit

This can also be done automatically for each file by checking the Require Variable Declaration in the Options dialog box.

Practical Learning: Using a Variable

  1. On the main menu of Microsoft Visual Basic, click Tools -> Options...
  2. Click the Editor property page. In the Code Settings section, put a check mark in the Require Variable Declaration check box
     
  3. Click OK and return to Microsoft Access
  4. Close Microsoft Visual Basic
  5. From Microsoft Access, open the Department of Records and Statistics database you created in Lesson 1
  6. When asked whether you want to save the form, click No
  7. To create a form, in Database window, click Forms and double-click Create Form In Design View
  8. On the Standard toolbar, click the Code button Code and notice that the top section of the file now displays Option Explicit

Value Conversion

Every time the user enters a value in an application. That value is primarily considered as text. This means that, if you want to use such a value in an expression or a calculation that expects a specific value other than text, you must convert it appropriately. Fortunately, Microsoft Visual Basic provides an effective mechanism to convert a text value to one of the other values we will see next.

To convert text to another value, there is a function adapted for the purpose and that depends on the type of value you want to convert it to. We will mention each when necessary.

 

Data Types

 

Introduction

A data type tells the computer the kind of value you are going to use. There are different kinds of values for various purposes. Before assigning a data type to a variable, you should know how much space a data type will occupy in memory. Different variables or different data types use different amounts of space in memory. The amount of space used by a data type is measured in bytes.

To specify the data type that will be used for a variable, after typing Dim followed by the name of the variable, type the As keyword, followed by one of the data types we will review next. The formula used is:

Dim VariableName As DataType

This technique allows you to declare one variable on its line. In many assignments, you will need to declare more than one variable. To do this, you have two alternatives. You can declare each variable on its own line. This would be done as follows:

Dim Variable1 As DataType1
Dim Variable2 As DataType2
Dim Variable3 As DataType3

You can also declare more than one variable on the same line. To do this, use only one Dim keyword but separate each combination of a name and data type with a comma. This would be done as follows:

Dim Variable1 As DataType1, Variable2 As DataType2
Dim Variable3 As DataType3

Microsoft Visual Basic also provides special characters for some data types so that, instead of specifying a data type, you can use that character. We will indicate what character for what type.

Boolean

A variable is considered Boolean if it can hold only one of two values, either true or false, 0 or no 0, Yes or No. To declare such a variable, use the Boolean keyword. Here is an example:

Private Sub Form_Load()
    Dim IsMarried As Boolean
End Sub

After declaring the variable and when using it, you can specify its value as True or as False. To convert a value or an expression to Boolean, you can call the CBool() function.

String

A string is a character or a combination of characters that constitutes text of any kind and almost any length. To declare a string variable, fuse the String data type. Here is an example:

Private Sub Form_Load()
    Dim CountryName As String
End Sub

You can omit the As String expression. Instead, to indicate that you are declaring a String variable, you can end its name with the $ symbol. Here is an example:

Private Sub Form_Load()
    Dim CountryName$
End Sub

If you have a value that is not primarily text and you want to convert it to a string, use CStr() with the following syntax:

CStr(Value To Convert to String)

In the parentheses of the CStr(), enter the value that you want to convert to string.

 

Byte

If you are planning to use a numeric value in your program, you have a choice from different kinds of numbers that Microsoft Access and Microsoft Visual Basic can recognize.  You can use the Byte data type for a variable that would hold a natural number that ranges from 0 to 255. You can declare it as follows:

Private Sub Form_Load()
    Dim StudentAge As Byte
End Sub

If the user enters a certain value in a control and you want to convert it to a small number, you can use CByte(). The formula to use would be:

Number = CByte(Value to Convert to Byte)

When using CByte(), passing that value between the parentheses.

Integer

An integer is a natural number. To declare a variable that would hold a number that ranges from -32768 to 32767, use the Integer data type. The integer type should always be used when counting things such as books in a library or students in a school; in this case you would not use decimal values. Here is an example of declaring an integer variable:

Private Sub Form_Load()
    Dim Tracks As Integer
End Sub

When declaring an integer variable, you can omit the As Integer expression and terminate the name of the variable with %. Here is an example:

Private Sub Form_Load()
    Dim Tracks%
End Sub

If you have a value that needs to be converted into a natural number, you can call CInt() using the following formula:

Number = CInt(Value to Convert)

Between the parentheses of CInt(), enter the value, text, or expression that needs to be converted.

Long

A long integer is a number that can be used for a variable involving greater numbers than integers. To declare a variable that would hold such a large number, use the Long data type. Here is an example:

Private Sub Form_Load()
    Dim Population As Long
End Sub

Alternatively, you can omit the As Long expression and end the variable name with the @ symbol to indicate that you are declaring a Long integer variable. Here is an example:

Private Sub Form_Load()
    Dim Population@
End Sub

To convert a value to a long integer, call CLng() using the following formula:

Number = CLng(Value to Convert)

To convert a value to long, enter it in the parentheses of CLng().

Single

In computer programming, a decimal number is one that represents a fraction. Examples are 1.85 or 426.88. If you plan to use a variable that would that type of number but precision is not your main concern, declare it using the Single data type. Here is an example:

Private Sub Form_Load()
    Dim Distance As Single
End Sub

If you want, you can omit the As Single expression in the declaration. Instead, you can type ! at the end the name of the variable to still indicate that you are declaring a Single variable. Here is an example:

Private Sub Form_Load()
    Dim Distance!
End Sub

If you have a value that needs to be converted, use CSng() with the following formula:

Number = CSng(Value to Convert)

Double

If you want to use a decimal number that requires a good deal of precision, declare a variable using the Double data type.

In most circumstances, it is preferable to use Double instead of Single when declaring a variable that would hold a decimal number. Although the Double takes more memory spaces (computer memory is not expensive anymore(!)), it provides more precision.

Here is an example of declaring a Double variable:

Private Sub Form_Load()
    Dim Distance As Double
End Sub

Instead of the AS Double expression, you can omit it and end the name of the variable with the # character to indicate that you are declaring a Double variable. Here is an example:

Private Sub Form_Load()
    Dim Distance#
End Sub

To convert a value to double-precision, use CDbl() with the following formula:

Number = CDbl(Value to Convert)

In the parentheses of CDbl(), enter the value that needs to be converted.

Currency

The Currency data type is used to deal with monetary values. Here is an example of declaring it:

 
Private Sub Form_Load()
    Dim StartingSalary As Currency
End Sub

If you want to convert a string to a monetary value, use CCur() with the following formula:

Number = CCur(Value to Convert)

To perform this conversion, enter the value in the parentheses of CCur().

Date

In Visual Basic, a Date data type is used to specify a date or time value. Therefore, to declare either a date or a time variables, use the Date data type. Here are two examples:

Private Sub Form_Load()
    Dim DateOfBirth As Date
    Dim KickOffTime As Date
End Sub

If you have a string or an expression that is supposed to hold a date or a time value, to convert it, use CDate() based on the following formula:

Result = CDate(Value to Convert)

In the parentheses of CDate(), enter the value that needs to be converted.

Variant

A Variant can be used to declare any kind of variable. You can use a variant when you can't make up your mind regarding a variable but, as a beginning programmer, you should avoid it.

Here is a table of various data types and the amount of memory space each one uses:

Data type Description Range
Byte 1-byte binary data 0 to 255
Integer 2-byte integer – 32,768 to 32,767
Long 4-byte integer – 2,147,483,648 to 2,147,483,647
Single 4-byte floating-point number – 3.402823E38 to – 1.401298E – 45 (negative values)
1.401298E – 45 to 3.402823E38 (positive values)
Double 8-byte floating-point number – 1.79769313486231E308 to
– 4.94065645841247E – 324 (negative values)
4.94065645841247E – 324 to 1.79769313486231E308 (positive values)
Currency 8-byte number with fixed decimal point – 922,337,203,685,477.5808 to 922,337,203,685,477.5807
String String of characters Zero to approximately two billion characters
Date 8-byte date/time value January 1, 100 to December 31, 9999

When naming your variables, besides the rules reviewed previously, you can start a variable's name with a one to three letters prefix that could identify the data type used. Here are a few suggestions:

Data Type Prefix Example
Boolean bln blnFound
Byte byt bytTracks
Date/Time dtm dteStartOfShift
Double dbl dblDistance
Error err errCantOpen
Integer int intNbrOfStudents
Long lng lngPopulation
Object obj objConnection
Single sng sngAge
String str strCountryName
Currency cur curHourlySalary
Variant var varFullName

Variables of Built-In Objects

 

Introduction

In the above sections, we saw how to declare a variable from a built-in data type. Besides these types, Microsoft Access and Microsoft Visual Basic ship with objects as we introduced them in Lesson 3. Sometimes you will need to refer to such objects in your code. In most cases, you will need to first declare a variable of the desired type before using it.

To declare a variable of an object, you should first make sure you know the type of object you want.

A Variable of Type Object

Every object you will use in your application is primarily of type Object. In many cases, you will be able to directly use the object in your application. In some other cases, you will first need to declare the variable and initialize it before using it. Also, in many cases, you can declare a variable and specify its particular type. In some cases, you may not know or may not need to specify the particular type of the object you want to use. In this case, when declaring the variable, you can specify its type as Object. When using the Object type to declare a one, the variable should be one of the existing VBA types of object and not one of the basic data types we saw earlier. This would be done as follows:

Dim objVariable As Object

After this declaration, you should then initialize the variable and specify the actual type it would be. To initialize a variable declared as a VBA object, use the Set operator that we will see later.

The Application Object

In Lesson 3, we saw that a Microsoft Access database was an object of type Application. In your code, to declare a variable of this type, you can type:

Dim app As Application

If you want to refer to such an object outside of Microsoft Access, you must qualify it with the Access object. For example, from an application such as Microsoft Word, to declare a variable that refers to a Microsoft Access database, the above declaration would be made as:

Dim app As Access.Application

Even in Microsoft Access, you can use Access.Application.

Constants

 

Introduction

A constant is a value that doesn't change (this definition is redundant because the word value already suggests something that doesn't change). There are two types of constants you will use in your programs: those supplied to you and those you define yourself.

Constant Colors

To assist you with identifying colors, Microsoft Visual Basic uses various constants

Color Name Constant Value Color
Black vbBlack &h00  
Red vbRed &hFF  
Green vbGreen &hFF00  
Yellow vbYellow &hFFFF  
Blue vbBlue &hFF0000  
Magenta vbMagenta &hFF00FF  
Cyan vbCyan &hFFFF00  
White vbWhite &hFFFFFF  
 

The Carriage Return-Line Feed Constant

Visual Basic provides the vbCrLf constant used to interrupt a line of text and move to the next line.

Built-in Constants: PI

PI is a mathematical constant whose value is approximately equal to 3.1415926535897932. It is highly used in operations that involve circles or geometric variants of a circle: cylinder, sphere, cone, etc.

Built-in Logical Constants: NULL

A variable is said to be null when its value is invalid or doesn't bear any significant or recognizable value.

Built-in Logical Constants: TRUE and FALSE

An expression is said to be false if the result of its comparison is 0. Otherwise, the expression is said to bear a true result.

Operations on Variables

 

Introduction

An operation is at least one value combined with a symbol to produce a new value. A more complex operation can involve more than one value and possibly more than one symbol. A value involved in an operation is called an operand. A symbol involved in an operation is called an operator.

The Assignment Operator =

The assignment operation is used to make a copy of a value, an expression, or the content of a control and give the copy to another field or expression. The assignment operation is performed with the = sign.

For example, suppose you have a field that displays a first name and that field is called FirstName. If you want that first name to display in another field, with this new field named, in the new field you could type:

=FirstName

On the other hand, you can use the assignment operator to give a value to a declared variable. Here is an example:

Private Sub Form_Load()
    Dim NumberOfTracks As Integer
    NumberOfTracks = 16
End Sub

When the assignment operator is provided to a variable as a starting value for the variable, this is referred to as initializing the variable.

Practical Learning: Using the Assignment Operator

  1. From the resources that accompany this ebook, open the Exercise2 database
  2. Open the Assignment form
  3. After viewing the form, switch it to Design View
  4. On the form, click the First Name text box to select it
  5. On the Properties window, click the Event tab
  6. Double-click On Lost Focus
  7. Right-click On Lost Focus and click Build...
  8. Implement the event as follows:
     
    Private Sub txtFirstName_LostFocus()
        ' Assign the content of the First Name text box
        ' to the Full Name text box
        txtFullName = txtFirstName
    End Sub
  9. Return to the form and switch it to Form View
  10. Click the First Name text box. Type Catherine and press Tab
     
  11. Notice that the Full Name text box got filled with the value of the First Name when the First Name text box lost focus.
    After using the form, switch it to Design View

Set

We saw earlier that you could declare a variable based on a built-in object of VBA. To specify the particular object you are referring to, you can (must) use the Set operator to assign an existing object to your variable. This would be done as follows:

dim ctlFirstName as Control
Set ctlFirstName = TextBox

The Double Quotes: ""

Double-quotes are used to display a string. First...

A string is an empty space, a character, or a group of characters that you type or provide to a control and you want this character or this group of characters to be considered "as is". In other words, the expression or the control that receives the string should keep it or them the way you supplied it or them.

A string can be an empty space or one character, such as $ or w; a group of characters, like home or Manchester United or Verbally speaking, I mean… Ah forget it. Most of the time, you will want the program to keep this character or group of characters exactly the way you or the user supplied them. In order to let the program know that this is a string, you must enclose it in double quotes. From our examples, our strings would be "$", "w", "home", "Manchester United", and "Verbally speaking, I mean… Ah forget it".

To assign a string to an expression or a field, use the assignment operator as follows:

= "Manchester United"

In the same way, to initialize a variable with a string , use the assignment operator. Here is an example:

Private Sub Form_Load()
    Dim Address As String
    Address = "12404 Lockwood Drive Apt D4"
End Sub

Practical Learning: Using the Double-Quote Operator

  1. On the Assignment form, right-click the Full Name text box and click Build Event...
     
  2. On the Choose Builder dialog box, click Code Builder and click OK
  3. Once in the Code Editor, in the Object combo box, select cmdReset and implement its Click event as follows:
     
    Private Sub cmdReset_Click()
        ' Make all text boxes empty
        txtFirstName = ""
        txtMI = ""
        txtLastName = ""
        txtFullName = ""
        txtUsername = ""
    End Sub
  4. Click the empty line between Private Sub txtFullName_BeforeUpdate and End Sub. Notice that the Object combo box displays txtFullName.
  5. In the Procedure combo box, select DblClick and implement the event as follows:
     
    Private Sub txtFullName_DblClick(Cancel As Integer)
        Dim strFullName As String
        strFullName = "Mary D. Lunden"
        txtFullName = strFullName
    End Sub
  6. Return to the form and double-click the Full Name text box
  7. After viewing the form, switch it to Design View and return to Microsoft Visual Basic

The String Concatenator: &

The & operator is used to append two strings, the contents of two controls, or expressions; this is considered as concatenating them. For example, it could allow you to concatenate a first name and a last name, producing a full name. The general formula of the concatenation operator is expressed as:

Value1 & Value2

To display a concatenated expression, use the assignment operator. To assign a concatenated expression to a variable, use the assignment operator the same way. Here is an example:

Private Sub Form_Load()
    Dim FirstName, LastName As String
    Dim FullName As String
    
    FirstName = "Francis "
    LastName = "Pottelson"
    FullName = FirstName & LastName
    Text0 = FullName
End Sub

To concatenate more than two expressions, you can use as many & operators between any combination of two expressions as necessary. After concatenating the expressions or values, you can assign the result to another value or expression using the assignment operator. The syntax used is:

=Value1 & " " & Value2

Examples

=FirstName & " " & LastName
This would display, for example, Boniface Dunkirk
  =[LastName] & ", " & [FirstName]
This would produce, for example, Chang, Helene
  =[Address] & " " & [City] & " " & [State] & " " & [ZIPCode] & " " & [Country]
This would display a complete address in a field
 

Practical Learning: Using the Concatenator

  1. In the Code Editor, delete the code in the txtFirstName_LostFocus event
  2. In the Object combo box, select cmdCreateAccount and implement its Click event as follows:
     
    Private Sub cmdCreateAccount_Click()
        Dim strFirstName As String
        Dim strMiddleInitial As String
        Dim strLastName As String
        Dim strFullName As String
        Dim strUsername As String
        
        strFirstName = txtFirstName
        strMiddleInitial = txtMI
        strLastName = txtLastName
        ' Create a username made of the last name followed by the middle initial
        strUsername = txtLastName & strMiddleInitial
        ' Create a full name as the first name followed by the last name
        strFullName = txtFirstName & " " & txtLastName
        
        txtFullName = strFullName
        txtUsername = strUsername
    End Sub
  3. Return to Microsoft Access, switch the form to Form View, and click First Name
  4. Type Hermine and press Tab
  5. Type D and press Tab
  6. In the Last Name text box, type Summers and press Tab
  7. Notice that the first button is selected. Press Enter
  8. Close the dialog box. When asked whether you want to save the form, click Yes

The Negation Operator -

In mathematics, an integer such as 120 or a double floating number such as 98.005 is qualified as positive; that is, it is considered greater than 0. If a number is less than 0, to express it, you can add the - sign on the left side of the number. Examples are -5502 or -240.65. The - sign signifies that the number is negative.

A variable or an expression can also be represented as negative by prefixing it with a - sign. Examples are -Distance or -NbrOfPlayers. To initialize a variable with a negative value , use the assignment operator. Here is an example:

Private Sub Form_Load()
    Dim NumberOfTracks As Byte
    Dim Temperature As Integer
    
    NumberOfTracks = 16
    Temperature = -94
End Sub

The Addition: +

The addition is used to add one value or expression to another. It is performed using the + symbol and its formula is:

Value1 + Value2

The addition allows you to add two numbers such as 12 + 548 or 5004.25 + 7.63

After performing the addition, you get a result. You can provide such a result to another variable or control. This is done using the assignment operator. The formula used would be:

= Value1 + Value2

Practical Learning: Using the Addition

  1. Open the frmAlgebraicOperators form.
  2. After viewing it, switch it to Design View
  3. The accompanying resources include pictures of geometric figures. To enhance the form, you can add them. To do that, on the Toolbox, click Image and click the left area of the labels. On the Insert Picture dialog box, locate the picture and add it.
  4. On the form, click the Quadrilateral tab. Right-click the top Calculate button and click Build Event...
  5. On the Choose Builder dialog box, double-click Code Builder.
  6. In the Object combo box, select cmdRCalculate
  7. Implement both Click events as follows:
     
    Private Sub cmdRCalculate_Click()
        Dim dblLength, dblHeight As Double
        Dim dblPerimeter As Double
        
        dblLength = txtRLength
        dblHeight = txtRHeight
        ' Calculate the perimeter of the rectangle
        ' by adding the length to the height, 2 times each
        dblPerimeter = dblLength + dblHeight + dblLength + dblHeight
        txtRPerimeter = dblPerimeter
    End Sub
    
    Private Sub cmdSqCalculate_Click()
        Dim dblSide As Double
        Dim dblPerimeter As Double
        
        dblSide = txtSqSide
        ' Calculate the perimeter of a square by adding the side 4 times
        dblPerimeter = dblSide + dblSide + dblSide + dblSide
        txtSqPerimeter = dblPerimeter
    End Sub
  8. Return to the form and switch it to Form View
  9. In the Quadrilateral tab, click Side and type 35.55
  10. Click the top Calculate button
  11. Click Length and type 42.72
  12. Click Height and type 36.44
  13. Click the other Calculate button
  14. After using the form, return to the Code Editor

The Subtraction: -

The subtraction is performed by retrieving one value from another value. This is done using the - symbol. The syntax used is:

Value1 - Value2

The value of Value1 is subtracted from the value of Value2. After the operation is performed, a new value results. This result can be used in any way you want. For example, you can display it in a control using the assignment operator as follows:

= Value1 - Value2

The Multiplication: *

The multiplication allows adding one value to itself a certain number of times, set by the second value. The multiplication is performed with the * sign which is typed with Shift + 8. Here is an example:

Value1 * Value2

During the operation, Value1 is repeatedly added to itself, Value2 times. The result can be assigned to another value or displayed in a control as follows:

= Value1 * Value2

Practical Learning: Using the Multiplication

  1. To apply the multiplication operation, change the Click events as follows:
     
    Private Sub cmdRCalculate_Click()
        Dim dblLength, dblHeight As Double
        Dim dblPerimeter As Double
        Dim dblArea As Double
        
        dblLength = txtRLength
        dblHeight = txtRHeight
        ' Calculate the perimeter of the rectangle
        ' by adding the length to the height, 2 times each
        dblPerimeter = dblLength + dblHeight + dblLength + dblHeight
        dblArea = dblLength * dblHeight
        
        txtRPerimeter = dblPerimeter
        txtRArea = dblArea
    End Sub
    
    Private Sub cmdSqCalculate_Click()
        Dim dblSide As Double
        Dim dblPerimeter As Double
        Dim dblArea As Double
        
        dblSide = txtSqSide
        ' Calculate the perimeter of a square by adding the side 4 times
        dblPerimeter = 4 * dblSide
        dblArea = dblSide * dblSide
        
        txtSqPerimeter = dblPerimeter
        txtSqArea = dblArea
    End Sub
  2. Get back to the form and click both Calculate buttons:
     
  3. After using the form, switch it to Design View and get back to the Code Editor
  4. In the Object combo box, select cmdCCalculate and implement its Click event as follows:
     
    Private Sub cmdCCalculate_Click()
        Dim dblRadius As Double
        Dim dblCircumference, dblArea As Double
        
        dblRadius = txtCircleRadius
        ' Circumference of a circle = 2 * Radius * PI
        dblCircumference = 2 * dblRadius * 3.14159
        ' Area of a circle = Radius * Radius * PI
        dblArea = 3.14159 * dblRadius * dblRadius
        
        txtCircleCircumference = dblCircumference
        txtCircleArea = dblArea
    End Sub
  5. Return to the form and switch it to Form View
  6. Click the Circular tab and change the top Radius (the radius of the circle) to 64.88 and click the top Calculate button:
     
  7. After using the form, switch it to Design View

The Integer Division: \

Dividing an item means cutting it in pieces or fractions of a set value. For example, when you cut an apple in the middle, you are dividing it in 2 pieces. If you cut each one of the resulting pieces, you will get 4 pieces or fractions. This is considered that you have divided the apple in 4 divisions. Therefore, the division is used to get the fraction of one number in terms of another.

Microsoft Visual Basic provides two types of results for the division operation. If you want the result of the operation to be a natural number, called an integer, use the backlash operator "\" as the divisor. Here is an example:

Value1 \ Value2

This operation can be performed on two types of valid numbers, with or without decimal parts. After the operation, the result would be a natural number. The result of the operation can be assigned to another value. It can also be displayed in a control using the assignment operator:

= Value1 \ Value2

The Division: /

The second type of division results in a decimal number. It is performed with the forward slash "/". Its syntax is:

Value1 / Value2

After the operation is performed, the result is a decimal number. The result of either operation can be assigned to another value. It can also be displayed in a control using the assignment operator:

= Value1 / Value2

The Exponentiation: ^

Exponentiation is the ability to raise a number to the power of another number. This operation is performed using the ^ operator (Shift + 6). It uses the following mathematical formula:

yx

In Microsoft Visual Basic (and Microsoft Access), this formula is written as:

y^x

and means the same thing. Either or both y and x can be values or expressions, but they must carry valid values that can be evaluated.

When the operation is performed, the value of y is raised to the power of x. You can display the result of such an operation in a field using the assignment operator as follows:

=y^x

You can also assign the operation to an expression as follows:

Total = y^x

Practical Learning: Using the Exponentiation Operator

  1. Return to the Code Editor and change the code of the cmdSqCalculate_Click as follows:
     
    Private Sub cmdSqCalculate_Click()
        Dim dblSide As Double
        Dim dblPerimeter As Double
        Dim dblArea As Double
        
        dblSide = txtSqSide
        ' Calculate the perimeter of a square by adding the side 4 times
        dblPerimeter = 4 * dblSide
        dblArea = dblSide ^ 2
        
        txtSqPerimeter = dblPerimeter
        txtSqArea = dblArea
    End Sub
  2. Get to the form and switch it to Form View.
  3. In the Quadrilateral property sheet, enter 12.46 in the Side text box and click the top Calculate
  4. After using the form, switch it to Design View

The Remainder Operator: Mod

The division operation gives a result of a number with or without decimal values, which is fine in some circumstances. Sometimes you will want to get the value remaining after a division renders a natural result. Imagine you have 26 kids at a football (soccer) stadium and  they are about to start. You know that you need 11 kids for each team to start. If the game starts with the right amount of players, how many will seat and wait?

The remainder operation is performed with keyword Mod. Its syntax is:

Value1 Mod Value2

The result of the operation can be used as you see fit or you can display it in a control using the assignment operator as follows:

= Value1 Mod Value2

The Parentheses Operators: ()

Parentheses are used in two main circumstances: in an event (or procedures, as we will learn) or in an operation. The parentheses in an operation help to create sections in an operation. This regularly occurs when more than one operators are used in an operation.

Consider the following operation: 8 + 3 * 5

The result of this operation depends on whether you want to add 8 to 3 then multiply the result by 5 or you want to multiply 3 by 5 and then add the result to 8. Parentheses allow you to specify which operation should be performed first in a multi-operator operation. In our example, if you want to add 8 to 3 first and use the result to multiply it by 5, you would write (8 + 3) * 5. This would produce 55. On the other hand, if you want to multiply 3 by 5 first then add the result to 8, you would write 8 + (3 * 5). This would produce 23.

As you can see, results are different when parentheses are used on an operation that involves various operators. This concept is based on a theory called operator precedence. This theory manages which operation would execute before which one; but parentheses allow you to completely control the sequence of these operations.

Practical Learning: Using the Parentheses in an Operation

  1. Get to the Code Editor and change the following event:
     
    Private Sub cmdRCalculate_Click()
        Dim dblLength As Double
        Dim dblHeight As Double
        Dim dblPerimeter As Double
        Dim dblArea As Double
        
        dblLength = txtRLength
        dblHeight = txtRHeight
        ' Calculate the perimeter of the rectangle
        ' by adding the length to the height, 2 times each
        dblPerimeter = 2 * (dblLength + dblHeight)
        dblArea = dblLength * dblHeight
        
        txtRPerimeter = dblPerimeter
        txtRArea = dblArea
    End Sub
  2. In the Object combo box, select cmdECalculate and implement its event as follows:
     
    Private Sub cmdECalculate_Click()
        Dim dblRadius1 As Double
        Dim dblRadius2 As Double
        Dim dblCircumference As Double
        Dim dblArea As Double
        
        dblRadius1 = txtEllipseRadius1
        dblRadius2 = txtEllipseRadius2
        dblCircumference = (dblRadius1 + dblRadius2) * 3.14159
        dblArea = dblRadius1 * dblRadius2 * 3.14159
        
        txtEllipseCircumference = dblCircumference
        txtEllipseArea = dblArea
    End Sub
  3. Return to the form. Test the rectangle and the ellipse.

The Square Brackets Operator: []

In Lesson 2, we saw that it was suitable to use one-word names for objects in Microsoft Access. In reality, Microsoft Access, as mentioned already, is particularly flexible with names. We saw that we could use square brackets to enclose a name made of. As seen in Lesson 2, this principle is the same here.

Practical Learning: Using the Square Brackets Operator

  1. Get to the Code Editor and change the following events:
     
    Private Sub cmdRCalculate_Click()
        Dim dblLength As Double
        Dim dblHeight As Double
        Dim dblPerimeter As Double
        Dim dblArea As Double
        
        dblLength = [txtRLength]
        dblHeight = [txtRHeight]
        ' Calculate the perimeter of the rectangle
        ' by adding the length to the height, 2 times each
        dblPerimeter = 2 * (dblLength + dblHeight)
        dblArea = dblLength * dblHeight
        
        [txtRPerimeter] = dblPerimeter
        [txtRArea] = dblArea
    End Sub
    
    Private Sub cmdECalculate_Click()
        Dim dblRadius1 As Double
        Dim dblRadius2 As Double
        Dim dblCircumference As Double
        Dim dblArea As Double
        
        dblRadius1 = [txtEllipseRadius1]
        dblRadius2 = [txtEllipseRadius2]
        dblCircumference = (dblRadius1 + dblRadius2) * 3.14159
        dblArea = dblRadius1 * dblRadius2 * 3.14159
        
        [txtEllipseCircumference] = dblCircumference
        [txtEllipseArea] = dblArea
    End Sub
  2. In the Object combo box, select cmdECalculate and implement its event
  3. Return to the form. Test the rectangle and the ellipse

The Collection Operator: !

Once again, in Lesson 3, we mentioned that the exclamation point operator "!" was used to access a member of a collection. 

Practical Learning: Using the Exclamation Operator

  1. Get to the Code Editor and change the code of the cmdSqCalculate_Click event as follows:
     
    Private Sub cmdSqCalculate_Click()
        Dim dblSide As Double
        Dim dblPerimeter As Double
        Dim dblArea As Double
        
        dblSide = Forms!frmAlgebraicOperators!txtSqSide
        ' Calculate the perimeter of a square by adding the side 4 times
        dblPerimeter = 4 * dblSide
        dblArea = dblSide ^ 2
        
        Forms!frmAlgebraicOperators!txtSqPerimeter = dblPerimeter
        Forms!frmAlgebraicOperators!txtSqArea = dblArea
    End Sub
  2. Get back to the form and enter a value in the Side text box of the square in the Quadrilateral tab

The Line Continuation Operator: _

As introduced in Lesson 3, the line continuation character is used to span a section of code to more than one line.

Practical Learning: Using the Underscore Operator

  1. In the Code Editor, click the arrow of the Object combo box and select cmdCubeCalculate_Click
  2. Implement the Click event as follows:
     
    Private Sub cmdCubeCalculate_Click()
        Forms!frmAlgebraicOperators!txtCubeArea = 6 * _
    				Forms!frmAlgebraicOperators!txtCubeSide _
                                  * Forms!frmAlgebraicOperators!txtCubeSide
        Forms!frmAlgebraicOperators!txtCubeVolume = _
    				Forms!frmAlgebraicOperators!txtCubeSide * _
                                    Forms!frmAlgebraicOperators!txtCubeSide * _
                                    Forms!frmAlgebraicOperators!txtCubeSide
    End Sub
  3. In the Object combo box, select cmdBoxCalculate and implement its Click event as follows:
     
    Private Sub cmdBoxCalculate_Click()
        ' Volume = Length * Width * Height
        Forms!frmAlgebraicOperators!txtBoxVolume = _
                Forms!frmAlgebraicOperators!txtBoxLength * _
                Forms!frmAlgebraicOperators!txtBoxWidth * _
                Forms!frmAlgebraicOperators!txtBoxHeight
                
        Dim dblLength, dblHeight, dblWidth As Double
        dblLength = Forms!frmAlgebraicOperators!txtBoxLength
        dblHeight = Forms!frmAlgebraicOperators!txtBoxWidth
        dblWidth = Forms!frmAlgebraicOperators!txtBoxHeight
        ' Area = 2 * ((L * H) + (H * W) + (L * W))
        Forms!frmAlgebraicOperators!txtBoxArea = 2 * ( _
                                                        (dblLength * dblHeight) + _
                                                        (dblHeight * dblWidth) + _
                                                        (dblLength * dblWidth) _
                                                     )
    End Sub
  4. Return to the form and switch it to Form View.
  5. Click the 3-Dimensions tab and test box shapes:
     
  6. Save the form and close it

Database Maintenance

 

Database Creation

So far, we have seen various ways of creating a database, including creating a blank database or using the wizard. Besides these techniques, you can also programmatically create a database. To do this, first declare a variable of type Application and initialize the variable with the version of the Microsoft Access that will be used. To actually create the database, call the NewCurrentDatabase method of the Application class. This method takes as argument the path and the name of the new database. The name should include the .mdb extension but if you omit it, the extension would be added when the database is created. Here is an example that creates a new database named Championship in a folder named Programs on the C: drive:

Private Sub cmdCreateDatabase_Click()
    Dim strNewDB As String
    Dim appAccess As Access.Application

    strNewDB = "C:\Programs\Championship.mdb"
    Set appAccess = CreateObject("Access.Application.9")
    
    appAccess.NewCurrentDatabase strNewDB
End Sub

 

 
 

Previous Copyright © Yevol, 2007 Next