Microsoft Access VBA - Lesson 17: Counting and Looping

	Counting and Looping

Fundamentals of Loops

Introduction

A loop is a technique used to repeat an action. The Visual Basic language provides various techniques and keywords to perform such actions called loops.

For Each Item in a Collection

In our lesson on introducing collections, we saw that the Visual Basic language provides the For Each...Next statement used to visit each item. That operation is one of the techniques to perform a loop. The formula of the For Each ... Next loop is:

For Each element In collection
    statements
    options
Next

We saw that you must first declare a variable for the element.

Practical Learning: Introducing Loops

Start Microsoft Access
In the list of files, click Exercise3 from the previous lesson
On the Ribbon, click Create and click Form Design
In Controls section of the Ribbon, click the Button and click the form.
If a wizard starts, click Cancel
On the form, right-click the button and click Build Event

In the Choose Builder dialog box, double-click Code Builder and implement the event as follows:

Private Sub Command0_Click()
    Dim month
    Dim months As Collection

    Set months = New Collection
    
    months.Add "January"
    months.Add "February"
    months.Add "March"
    months.Add "April"
    months.Add "May"
    months.Add "June"
    months.Add "July"
    months.Add "August"
    months.Add "September"
    months.Add "October"
    months.Add "November"
    months.Add "December"

    For Each month In months
        MsgBox month
    Next
    
    Set months = Nothing
End Sub

Return to Microsoft Access and switch the form to Form View
Click the button
Click OK on each message box
Close the form
When asked whether you want to save, click No

For the Next Item in the Collection

If you want to explicitly indicate that you want to access the next item in the collection, after the Next keyword, add the element. The formula of the loop becomes:

For Each element In collection
    statements
    options
Next [ element ]

Here is an example:

Private Sub Command0_Click()
    Dim month
    Dim months As Collection

    Set months = New Collection
    
    months.Add "January"
    months.Add "February"
    months.Add "March"
    months.Add "April"
    months.Add "May"
    months.Add "June"
    months.Add "July"
    months.Add "August"
    months.Add "September"
    months.Add "October"
    months.Add "November"
    months.Add "December"

    For Each month In months
        MsgBox month
    Next month
    
    Set months = Nothing
End Sub

Fundamentals of Loop Counters

Introduction

You can visit each item of a collection based on the index of that item. To assist you with this, the Visual Basic language provides a loop created with For...To...Next. The formula to follow is:

For counter = start To end
  statement(s)
Next

You must use a variable by which the counting would proceed. First declare an Integer (or compatible, such as Byte or Long) variable. That variable will be used in the counter placeholder. In that place, that is, after the For keyword, you must initialize the variable with the start point. Here is an example:

Dim i As Integer

. . .

For i = 1 To . . .

Next

After the To keyword, specify the last value of the counting as the end point. If you know that value, that is, if it is a constant, use it. In the body of the loop, you access each item using the name of the collection and the counter value.

Practical Learning: Introducing Loop Counters

On the Ribbon, click File and click Open
In the list of files, click Business Mathematics from Lesson 16
In the Navigation Pane, right-click Depreciation Schedule and click Design View
On the form, right-click the Generate button and click Build Event
On the Choose Builder dialog box, click Code Builder and click OK

Implement the event as follows:

Private Sub cmdGenerate_Click()
    Dim cost As Double
    Dim period As Integer
    Dim strSchedule As String
    Dim depreciation As Double
    Dim salvageValue As Double
    Dim estimatedLife As Integer
    Dim depreciations As New Collection
    
    cost = CDbl(Nz(txtCost))
    salvageValue = CDbl(Nz(txtSalvageValue))
    estimatedLife = CDbl(Nz(txtEstimatedLife))

    For period = 1 To estimatedLife
        depreciation = SYD(cost, salvageValue, estimatedLife, period)
        depreciations.Add (depreciation)
    Next

    strSchedule = "Depreciation Schedule" & vbCrLf & _
                  "===================" & vbCrLf & _
                  "Year" & vbTab & "Depreciation" & vbCrLf & _
                  "===================" & vbCrLf

    For period = 1 To estimatedLife
        strSchedule = strSchedule & period & vbTab & FormatNumber(depreciations(period)) & vbCrLf & _
                      "--------------------------------------" & vbCrLf
    Next

    If cost > 0 Then
        MsgBox strSchedule, vbOKOnly Or vbInformation, "Straight-Line Method"
    End If
    
    Set depreciations = Nothing
End Sub

Return to Microsoft Access and switch the form to Form View
Click Machine Value and type 6500
Click Salvage Value and type 2000
Click Estimated Life and type 5
Click the Calculate button:
Click OK on the message box
Save and close the form
On the Ribbon, click File and click Open
In the list of files, click Exercise3 used earlier
On the Ribbon, click Create and click Form Design
In Controls section of the Ribbon, click the Button and click the form.
If a wizard starts, click Cancel
On the form, right-click the button and click Build Event
In the Choose Builder dialog box, double-click Code Builder

If the values of the loop are coming from a collection, you can use its Count member as the last value of the loop. To see an example, implement the event as follows:

Private Sub Command0_Click()
    Dim i As Integer
    Dim list As String
    Dim zones As Collection

    Set zones = New Collection

    list = "United States Times Zones" & vbCrLf & _
           "=-=-=-=-=-=-=-=-=-=-=-=-=-=-=" & vbCrLf
           
    zones.Add ("Pacific Standard Time (PST)")
    zones.Add ("Mountain Standard Time (MST)")
    zones.Add ("Central Standard Time (CST)")
    zones.Add ("Eastern Standard Time (EST)")
    zones.Add ("Atlantic Standard Time (AST)")
    zones.Add ("Alaskan Standard Time (AKST)")
    zones.Add ("Hawaii-Aleutian Standard Time (HST)")
    zones.Add ("Samoa standard time (UTC-11)")
    zones.Add ("Chamorro Standard Time (UTC+10)")

    For i = 1 To zones.Count
        list = list & zones(i) & vbCrLf
    Next

    MsgBox list
    
    Set zones = Nothing
End Sub

Return to Microsoft Access and switch the form to Form View
Click the button
Click OK
Close the form
When asked whether you want to save, click No
On the Ribbon, click File and click Open
In the list of files, click Altair Realtors1 from Lesson 7
On the Ribbon, click Create and click Class Module

Type:

Enum HouseTypes
    SingleFamily
    Townhouse
    Condominium
    Unknown
End Enum

Public PropertyNumber As Long
Public PropertyType As HouseTypes
Public City As String
Public Bedrooms As Integer
Public Bathrooms As Single
Public MarketValue As Double

In the Project window, click (Name) and type Property
Return to Microsoft Access
On the Ribbon, click Create and click Form Design
In Controls section of the Ribbon, click the Button and click the form.
If a wizard starts, click Cancel
On the form, right-click the button and click Build Event
In the Choose Builder dialog box, double-click Code Builder

If the items of a collection are object types, you can also use a loop to locate each. To see an example, implement the event as follows:

Private Sub Command0_Click()
    Dim house As Property
    Dim index As Integer
    Dim strListing As String
    Dim residence As Property
    Dim properties As New Collection
    
    strListing = "Altair Realtors - Properties Listing" & vbCrLf & _
                 "======================================" & vbCrLf & _
                 "Prop #" & vbTab & "Location" & vbTab & vbTab & _
                 "Beds" & vbTab & "Baths" & vbTab & "Market Value" & vbCrLf & _
                 "======================================" & vbCrLf
    
    Set house = New Property
    house.PropertyNumber = 247472
    house.PropertyType = HouseTypes.SingleFamily
    house.City = "Silver Spring"
    house.Bedrooms = 5
    house.Bathrooms = 3.5
    house.MarketValue = 685755
    properties.Add house
    
    Set house = New Property
    house.PropertyNumber = 502840
    house.PropertyType = HouseTypes.Condominium
    house.City = "Washington"
    house.Bedrooms = 2
    house.Bathrooms = 2#
    house.MarketValue = 318950
    properties.Add house
    
    Set house = New Property
    house.PropertyNumber = 150281
    house.PropertyType = HouseTypes.SingleFamily
    house.City = "Bowie State"
    house.Bedrooms = 5
    house.Bathrooms = 3.5
    house.MarketValue = 782575
    properties.Add house
    
    Set house = New Property
    house.PropertyNumber = 240875
    house.PropertyType = HouseTypes.Townhouse
    house.City = "Charleston"
    house.Bedrooms = 3
    house.Bathrooms = 2.5
    house.MarketValue = 348500
    properties.Add house
    
    Set house = New Property
    house.PropertyNumber = 475974
    house.PropertyType = HouseTypes.SingleFamily
    house.City = "Gaithersburg"
    house.Bedrooms = 4
    house.Bathrooms = 2.5
    house.MarketValue = 635775
    properties.Add house

    For index = 1 To properties.Count
        Set residence = properties(index)

        strListing = strListing & residence.PropertyNumber & vbTab & _
                     residence.City & vbTab & residence.Bedrooms & vbTab & _
                     residence.Bathrooms & vbTab & residence.MarketValue & vbCrLf
    Next
    
    strListing = strListing & "======================================"
    
    MsgBox strListing, vbOKOnly Or vbInformation, "Altair Realtors"
    
    Set house = Nothing
    Set properties = Nothing
End Sub

Return to Microsoft Access and switch the form to Form View
Click the button:
Click OK on the message box
Return to Microsoft Visual Basic

In the above example, we used a local variable to hold the object from the loop. This is not required. To access the objects another way, change the code as follows:

Private Sub Command0_Click()
    Dim house As Property
    Dim index As Integer
    Dim strListing As String
    Dim properties As New Collection
    
    strListing = "Altair Realtors - Properties Listing" & vbCrLf & _
                 "======================================" & vbCrLf & _
                 "Prop #" & vbTab & "Location" & vbTab & vbTab & _
                 "Beds" & vbTab & "Baths" & vbTab & "Market Value" & vbCrLf & _
                 "======================================" & vbCrLf
    
    Set house = New Property
    house.PropertyNumber = 247472
    house.PropertyType = HouseTypes.SingleFamily
    house.City = "Silver Spring"
    house.Bedrooms = 5
    house.Bathrooms = 3.5
    house.MarketValue = 685755
    properties.Add house
    
    Set house = New Property
    house.PropertyNumber = 502840
    house.PropertyType = HouseTypes.Condominium
    house.City = "Washington"
    house.Bedrooms = 2
    house.Bathrooms = 2#
    house.MarketValue = 318950
    properties.Add house
    
    Set house = New Property
    house.PropertyNumber = 150281
    house.PropertyType = HouseTypes.SingleFamily
    house.City = "Bowie State"
    house.Bedrooms = 5
    house.Bathrooms = 3.5
    house.MarketValue = 782575
    properties.Add house
    
    Set house = New Property
    house.PropertyNumber = 240875
    house.PropertyType = HouseTypes.Townhouse
    house.City = "Charleston"
    house.Bedrooms = 3
    house.Bathrooms = 2.5
    house.MarketValue = 348500
    properties.Add house
    
    Set house = New Property
    house.PropertyNumber = 475974
    house.PropertyType = HouseTypes.SingleFamily
    house.City = "Gaithersburg"
    house.Bedrooms = 4
    house.Bathrooms = 2.5
    house.MarketValue = 635775
    properties.Add house

    For index = 1 To properties.Count
        strListing = strListing & _
        	     properties(index).PropertyNumber & vbTab & _
                     properties(index).City & vbTab & _
                     properties(index).Bedrooms & vbTab & _
                     properties(index).Bathrooms & vbTab & _
                     properties(index).MarketValue & vbCrLf
    Next
    
    strListing = strListing & "======================================"
    
    MsgBox strListing, vbOKOnly Or vbInformation, "Altair Realtors"
    
    Set house = Nothing
    Set properties = Nothing
End Sub

Close the form
When asked whether you want to save, click No

Options on Loops Counters

The Explicit Next Item

As seen with the For Each loop, if you want to explicitly indicate that you are accessing the next item for each subsequent loop in a counter, type the index variable after the Next keywork. Here are examples:

Private Sub cmdGenerate_Click()
    Dim cost As Double
    Dim period As Integer
    Dim strSchedule As String
    Dim depreciation As Double
    Dim salvageValue As Double
    Dim estimatedLife As Integer
    Dim depreciations As New Collection
    
    cost = CDbl(Nz(txtCost))
    salvageValue = CDbl(Nz(txtSalvageValue))
    estimatedLife = CDbl(Nz(txtEstimatedLife))

    For period = 1 To estimatedLife
        depreciation = SYD(cost, salvageValue, estimatedLife, period)
        depreciations.Add (depreciation)
    Next

    strSchedule = "Depreciation Schedule" & vbCrLf & _
                  "===================" & vbCrLf & _
                  "Year" & vbTab & "Depreciation" & vbCrLf & _
                  "===================" & vbCrLf

    For period = 1 To estimatedLife
        strSchedule = strSchedule & period & vbTab & FormatNumber(depreciations(period)) & vbCrLf & _
                      "--------------------------------------" & vbCrLf
    Next period

    If cost > 0 Then
        MsgBox strSchedule, vbOKOnly Or vbInformation, "Straight-Line Method"
    End If
    
    Set depreciations = Nothing
End Sub

Stepping the Counting Loop

The regular formula of a loop counter increments the counting by 1 at the end of each statement. If you want to control how the incrementing processes, you can set your own, using the Step option. The formula to follow is:

For counter = start To end Step increment
  statement(s)
Next

You can set the incrementing value to your choice. If the value of increment is positive, the counter will be added to its value.

If you want to loop from a higher value to the a lower value, set a negative value to the increment factor and use the following formula:

For counter = end To start Step decrement
  statement(s)
Next

Practical Learning: Stepping the Counting Loop

On the Ribbon, click File and click Open
In the list of files, click Exercise3 used earlier in this lesson
On the Ribbon, click Create and click Form Design
In Controls section of the Ribbon, click the Button and click the form.
If a wizard starts, click Cancel
On the form, right-click the button and click Build Event

In the Choose Builder dialog box, double-click Code Builder and implement the event as follows:

Private Sub Command0_Click()
    Dim counter As Integer
    Dim states As New Collection
    
    states.Add ("Alaska"): states.Add ("Alabama"): states.Add ("Arkansas")
    states.Add ("Arizona"): states.Add ("California"): states.Add ("Colorado")
    states.Add ("Connecticut"): states.Add ("Delaware"): states.Add ("Florida")
    states.Add ("Georgia"): states.Add ("Hawaii"): states.Add ("Iowa")
    states.Add ("Idaho"): states.Add ("Illinois"): states.Add ("Indiana")
    states.Add ("Kansas"):  states.Add ("Kentucky"): states.Add ("Louisiana")
    states.Add ("Massachusetts"): states.Add ("Maryland"): states.Add ("Maine")
    states.Add ("Michigan"): states.Add ("Minnesota"): states.Add ("Missouri")
    states.Add ("Mississippi"): states.Add ("Montana")
    states.Add ("North Carolina"): states.Add ("North Dakota")
    states.Add ("Nebraska"): states.Add ("New Hampshire")
    states.Add ("New Jersey"): states.Add ("New Mexico"): states.Add ("Nevada")
    states.Add ("New York"): states.Add ("Ohio"): states.Add ("Oklahoma")
    states.Add ("Oregon"): states.Add ("Pennsylvania")
    states.Add ("Rhode Island"): states.Add ("South Carolina")
    states.Add ("South Dakota"): states.Add ("Tennessee"): states.Add ("Texas")
    states.Add ("Utah"): states.Add ("Virginia"): states.Add ("Vermont")
    states.Add ("Washington"): states.Add ("Wisconsin")
    states.Add ("West Virginia"): states.Add ("Wyoming")
    
    For counter = 1 To states.Count Step 5
        MsgBox states.item(counter)
    Next counter
  
    Set states = Nothing
End Sub

Return to Microsoft Access and switch the form to Form View
Click the button
Click OK on each message box
Return to Microsoft Visual Basic

To loop backward, change the code as follows:

Private Sub Command0_Click()
    Dim counter As Integer
    Dim states As New Collection
    
    states.Add ("Alaska"): states.Add ("Alabama"): states.Add ("Arkansas")
    states.Add ("Arizona"): states.Add ("California"): states.Add ("Colorado")
    states.Add ("Connecticut"): states.Add ("Delaware"): states.Add ("Florida")
    states.Add ("Georgia"): states.Add ("Hawaii"): states.Add ("Iowa")
    states.Add ("Idaho"): states.Add ("Illinois"): states.Add ("Indiana")
    states.Add ("Kansas"):  states.Add ("Kentucky"): states.Add ("Louisiana")
    states.Add ("Massachusetts"): states.Add ("Maryland"): states.Add ("Maine")
    states.Add ("Michigan"): states.Add ("Minnesota"): states.Add ("Missouri")
    states.Add ("Mississippi"): states.Add ("Montana")
    states.Add ("North Carolina"): states.Add ("North Dakota")
    states.Add ("Nebraska"): states.Add ("New Hampshire")
    states.Add ("New Jersey"): states.Add ("New Mexico"): states.Add ("Nevada")
    states.Add ("New York"): states.Add ("Ohio"): states.Add ("Oklahoma")
    states.Add ("Oregon"): states.Add ("Pennsylvania")
    states.Add ("Rhode Island"): states.Add ("South Carolina")
    states.Add ("South Dakota"): states.Add ("Tennessee"): states.Add ("Texas")
    states.Add ("Utah"): states.Add ("Virginia"): states.Add ("Vermont")
    states.Add ("Washington"): states.Add ("Wisconsin")
    states.Add ("West Virginia"): states.Add ("Wyoming")
    
    For counter = states.Count To 1 Step -8
        MsgBox states.item(counter)
    Next counter
  
    Set states = Nothing
End Sub

Return to Microsoft Access and click the button
Click OK on each message box
Return to Microsoft Visual Basic

Loop Counters and Procedures

Introduction

You can create a looping statement in a procedure or in an event of a control. When you do this, you may let the whole loop be processed.

Practical Learning: Stepping the Counting Loop

To process a loop in a procedure, change the code as follows:

Private Sub Show()
    Dim counter As Integer
    Dim states As New Collection
    
    states.Add ("Alaska"): states.Add ("Alabama"): states.Add ("Arkansas")
    states.Add ("Arizona"): states.Add ("California"): states.Add ("Colorado")
    states.Add ("Connecticut"): states.Add ("Delaware"): states.Add ("Florida")
    states.Add ("Georgia"): states.Add ("Hawaii"): states.Add ("Iowa")
    states.Add ("Idaho"): states.Add ("Illinois"): states.Add ("Indiana")
    states.Add ("Kansas"):  states.Add ("Kentucky"): states.Add ("Louisiana")
    states.Add ("Massachusetts"): states.Add ("Maryland"): states.Add ("Maine")
    states.Add ("Michigan"): states.Add ("Minnesota"): states.Add ("Missouri")
    states.Add ("Mississippi"): states.Add ("Montana")
    states.Add ("North Carolina"): states.Add ("North Dakota")
    states.Add ("Nebraska"): states.Add ("New Hampshire")
    states.Add ("New Jersey"): states.Add ("New Mexico"): states.Add ("Nevada")
    states.Add ("New York"): states.Add ("Ohio"): states.Add ("Oklahoma")
    states.Add ("Oregon"): states.Add ("Pennsylvania")
    states.Add ("Rhode Island"): states.Add ("South Carolina")
    states.Add ("South Dakota"): states.Add ("Tennessee"): states.Add ("Texas")
    states.Add ("Utah"): states.Add ("Virginia"): states.Add ("Vermont")
    states.Add ("Washington"): states.Add ("Wisconsin")
    states.Add ("West Virginia"): states.Add ("Wyoming")
    
    For counter = 1 To states.Count
        MsgBox states.item(counter)
    Next counter
  
    Set states = Nothing
End Sub

Private Sub Command0_Click()
    Show
End Sub

Return to Microsoft Access and click the button
Click OK on each message box
Return to Microsoft Visual Basic

To create a function that returns a collection after processing a loop, change the code as follows:

Private Function CreateStates() As Collection
    Dim states As New Collection
    
    states.Add ("Alaska"): states.Add ("Alabama"): states.Add ("Arkansas")
    states.Add ("Arizona"): states.Add ("California"): states.Add ("Colorado")
    states.Add ("Connecticut"): states.Add ("Delaware"): states.Add ("Florida")
    states.Add ("Georgia"): states.Add ("Hawaii"): states.Add ("Iowa")
    states.Add ("Idaho"): states.Add ("Illinois"): states.Add ("Indiana")
    states.Add ("Kansas"):  states.Add ("Kentucky"): states.Add ("Louisiana")
    states.Add ("Massachusetts"): states.Add ("Maryland"): states.Add ("Maine")
    states.Add ("Michigan"): states.Add ("Minnesota"): states.Add ("Missouri")
    states.Add ("Mississippi"): states.Add ("Montana")
    states.Add ("North Carolina"): states.Add ("North Dakota")
    states.Add ("Nebraska"): states.Add ("New Hampshire")
    states.Add ("New Jersey"): states.Add ("New Mexico"): states.Add ("Nevada")
    states.Add ("New York"): states.Add ("Ohio"): states.Add ("Oklahoma")
    states.Add ("Oregon"): states.Add ("Pennsylvania")
    states.Add ("Rhode Island"): states.Add ("South Carolina")
    states.Add ("South Dakota"): states.Add ("Tennessee"): states.Add ("Texas")
    states.Add ("Utah"): states.Add ("Virginia"): states.Add ("Vermont")
    states.Add ("Washington"): states.Add ("Wisconsin")
    states.Add ("West Virginia"): states.Add ("Wyoming")

    Set CreateStates = states
    Set states = Nothing
End Function

Private Sub Command0_Click()
    Dim counter As Integer
    Dim governments As Collection
    
    Set governments = CreateStates()
    
    For counter = 1 To governments.Count
        MsgBox governments.item(counter)
    Next counter
    
    Set governments = Nothing
End Sub

Return to Microsoft Access and click the button
Click OK on each message box
Return to Microsoft Visual Basic

Exiting a Procedure

In some cases, you may want to exit a conditional statement or a loop before its end. To assist with with this, the Visual Basic language provides the Exit keyword. It can be used in the body of a procedure or a loop.

Exiting a procedure consists of interrupting its flow and jumping to the End line of the procedure. To do this anywhere in the body of a procedure, type Exit Sub.

Practical Learning: Exiting a Procedure

To create a procedure that exits before its end, change the code as follows:

Private Sub ProcessTimeZones()
    Dim i As Integer
    Dim list As String
    Dim zones As Collection

    Set zones = New Collection

    list = "United States Times Zones" & vbCrLf & _
           "=-=-=-=-=-=-=-=-=-=-=-=-=-=-=" & vbCrLf
           
    zones.Add ("Pacific Standard Time (PST)")
    zones.Add ("Mountain Standard Time (MST)")
    zones.Add ("Central Standard Time (CST)")
    zones.Add ("Eastern Standard Time (EST)")
    zones.Add ("Atlantic Standard Time (AST)")
    zones.Add ("Alaskan Standard Time (AKST)")
    zones.Add ("Hawaii-Aleutian Standard Time (HST)")
    zones.Add ("Samoa standard time (UTC-11)")
    zones.Add ("Chamorro Standard Time (UTC+10)")

    MsgBox zones.item(1)
    MsgBox zones.item(2)
    MsgBox zones.item(3)
    MsgBox zones.item(4)
    MsgBox zones.item(5)
    MsgBox zones.item(6)
    MsgBox zones.item(7)

    Exit Sub
    
    MsgBox zones.item(8)
    MsgBox zones.item(9)
    
    Set zones = Nothing
End Sub

Private Sub Command0_Click()
    ProcessTimeZones
End Sub

Return to Microsoft Access and click the button
Click OK on the message boxes.
Notice that the collection contains 9 items but only 7 message boxes display
Close the form
When asked whether you want to save, click No
On the Ribbon, click File and click Open
In the list of files, click Business Starter from Lesson 13
On the Ribbon, click Create and click Class Module

Type:

Public EmployeeNumber As Long
Public FirstName As String
Public LastName As String
Public HourlySalary As Double

In the Project window, click (Name) and type Employee
In Project window, expand Microsoft Access Class Objects and double-click Form_Payroll Evaluation

Exiting a For Loop Counter

You can also exit a For loop that uses a counter. To do this, in the section where you want to stop, type Exit For. This would stop the flow of the loop and jump to the line immediately after the Next keyword. This feature of the Visual Basic language is very valuable when looking for a specific value or object in a collection.

Practical Learning: Exiting a Loop

In the Object combo box, select cmdFind and implement the event as follows:

Private Sub cmdFind_Click()
    Dim index As Integer
    Dim staff As Employee
    Dim worker As New Employee
    Dim employeeFound As Boolean
    Dim employees As New Collection
        
    employeeFound = False
      
    Set staff = New Employee
    staff.EmployeeNumber = "953-084"
    staff.FirstName = "Anthony"
    staff.LastName = "Walters"
    staff.HourlySalary = 18.52
    employees.Add staff
        
    Set staff = New Employee
    staff.EmployeeNumber = "204-815"
    staff.FirstName = "Jeannine"
    staff.LastName = "Rocks"
    staff.HourlySalary = 22.74
    employees.Add staff
        
    Set staff = New Employee
    staff.EmployeeNumber = "728-411"
    staff.FirstName = "Aaron"
    staff.LastName = "Gibson"
    staff.HourlySalary = 35.08
    employees.Add staff

    Set staff = New Employee
    staff.EmployeeNumber = "528-492"
    staff.FirstName = "Stephen"
    staff.LastName = "Brothers"
    staff.HourlySalary = 24.93
    employees.Add staff

    Set staff = New Employee
    staff.EmployeeNumber = "303-415"
    staff.FirstName = "Laura"
    staff.LastName = "Edom"
    staff.HourlySalary = 15.75
    employees.Add staff

    ' Check every record in the collection of employees
    For index = 1 To employees.Count
        ' Get a reference to the current record
        Set worker = employees(index)
            
        ' If the employee number of the current record is the same as the user typed, ...
        If worker.EmployeeNumber = txtEmployeeNumber Then
            ' ... display the first and last names
            txtEmployeeName = worker.LastName & ", " & worker.FirstName
            ' ... and display the hourly salary
            txtHourlySalary = worker.HourlySalary

            ' Since the employee has been found, make a note.
            employeeFound = True

            ' Now that the employee has been found, there is no reason to keep looking. Exit the loop
            Exit For
        End If
        
        ' As long as we haven't finished with the loop and we haven't
        ' found the employee, continue with the loop
    Next

    If employeeFound = False Then
        txtEmployeeName = ""
        txtEmployeeNumber = ""
    End If
End Sub

In the Object combo box, select cmdCalculate and implement the event as follows:

Private Sub cmdCalculate_Click()
    Dim netPay As Double
    Dim timeWorked As Double
    Dim hourlySalary As Double
    
    hourlySalary = CDbl(Nz(txtHourlySalary))
    timeWorked = CDbl(Nz(txtTimeWorked))
    netPay = hourlySalary * timeWorked
    
    txtNetPay = FormatNumber(netPay)
End Sub

Return to Microsoft Access and switch the form to Form View
Click Employee # and type 204-815
Click Find
Click Time Worked and type 42.50
Click Calculate
Save and close the form

Loops Repeaters

Introduction

The Visual Basic language presents many variations of loops. They combine the Do and the Loop keywords.

Doing Something While a Loop is Running

One of the formulas to perform a loop uses the Do... Loop While approach. The formula to follow is:

Do
    statement(s)
Loop While condition

This loop can be illustrated as follows:

Doing Something While a Loop is Running

The statement(s) would execute first. Then the condition would be checked. If the condition is true, then the statement(s) would execute again. The check-execution routine would continue as long as the condition is true. If/Once the condition becomes false, the statement will not execute anymore and the code will move beyond the loop.

As you may guess already, the condition must provide a way for it to be true or to be false. Otherwise, the looping would execute continually.

Practical Learning: Doing a Loop While Something is Happening

On the Ribbon, click File and click Open
In the list of files, click Business Mathematics
In the Navigation Pane, right-click Investment Evaluation and click Design View
On the form, right-click the Calculate button and click Build Event...
In the Choose Builder dialog box, click Code Builder and click OK

Implement the event as follows:

Private Sub cmdCalculate_Click()
    Dim index
    Dim periods
    Dim payment
    Dim interest
    Dim compounded
    Dim futureValue
    Dim interestRate
    Dim presentValue
    Dim strDepreciation
    Dim payments As New Collection
    Dim interests As New Collection
    Dim compoundFrequency As Integer

    futureValue = CDbl(txtFutureValue)
    interestRate = CDbl(txtInterestRate) / 100#

    compounded = CInt(InputBox("Enter a number for the desired compound frequency:" & vbCrLf & _
                               "1 - Daily" & vbCrLf & _
                               "2 - Weekly" & vbCrLf & _
                               "3 - Monthly" & vbCrLf & _
                               "4 - Quarterly" & vbCrLf & _
                               "5 - Semiannually" & vbCrLf & _
                               "6 - Anually", _
                               "Compound interest", "1"))
    compoundFrequency = iif(compounded = 1, 365, iif(compounded = 2, 52, iif(compounded = 3, 12, iif(compounded = 4, 4, iif(compounded = 5, 2, 1)))))
   
    periods = CDbl(txtPeriods)
    presentValue = futureValue / ((1 + interestRate / compoundFrequency) ^ (periods * compoundFrequency))

    index = 1

    Do
        payment = futureValue / ((1 + interestRate / compoundFrequency) ^ ((periods * compoundFrequency) - index))
        payments.Add (payment)
        index = index + 1
    Loop While index <= periods * compoundFrequency

    interest = payments(1) - presentValue
    interests.Add interest

    index = 2

    Do
        interest = payments(index) - payments(index - 1)
        interests.Add interest
        index = index + 1
    Loop While index <= periods * compoundFrequency


    txtCompounded = iif(compounded = 1, "Compounded Daily", _
                    iif(compounded = 2, "Compounded Weekly", _
                    iif(compounded = 3, "Compounded Monthly", _
                    iif(compounded = 4, "Compounded Quarterly", _
                    iif(compounded = 6, "Compounded Semi-Annually", _
                                        "Compounded Anually")))))
    txtPresentValue = FormatNumber(presentValue)

    strDepreciation = "Depreciation Schedule" & vbCrLf & _
                      "===================" & vbCrLf & _
                      "Period" & vbTab & "Interest" & vbTab & "Amount" & vbCrLf & _
                      "===================" & vbCrLf
        
    index = 1

    Do
        strDepreciation = strDepreciation & CStr(index) & vbTab & FormatNumber(interests(index)) & vbTab & FormatNumber(payments(index)) & vbCrLf
        
        index = index + 1
    Loop While index <= periods * compoundFrequency
    
    MsgBox strDepreciation, vbOKOnly Or vbInformation, "Depreciation Schedule"
End Sub

Return to Microsoft Access and switch the to Form View
Click Future Value and type 5000
Click Interest Rate and type 7.50
Click Periods and type 5
Click the Calculate button
In the input box, type 3
Click OK
Save and close the form

Doing a Loop Until Something Happens

An alternative to the Do... Loop While uses the following formula:

Do
    statement(s)
Loop Until condition

Once again, the statement(s) section executes first. After executing the statement(s), the condition is checked. If the condition is true, the statement(s) section executes again. This will continue until the condition becomes false. Once the condition becomes false, the loop stops and the flow continues with the section under the Loop Until line.

Practical Learning: Doing a Loop Until Something Happens

In the Navigation Pane, right-click Straight-Line Depreciation and click Design View
On the form, right-click the Calculate button and click Build Event...
In the Choose Builder dialog box, click Code Builder and click OK

Implement the event as follows:

Private Sub cmdCalculate_Click()
    Dim cost
    Dim period
    Dim bookValue
    Dim strSchedule
    Dim salvageValue
    Dim estimatedLife
    Dim depreciationRate
    Dim yearlyDepreciation

    period = 1
    cost = (txtCost)
    salvageValue = CDbl(txtSalvageValue)
    estimatedLife = CInt(txtEstimatedLife)

    depreciationRate = 100# / estimatedLife
    yearlyDepreciation = SLN(cost, salvageValue, estimatedLife)

    bookValue = cost

    strSchedule = "Depreciation Schedule" & vbCrLf & _
                  "======================================" & vbCrLf & _
                  "    Depreciation" & vbTab & "Yearly" & _
                  vbTab & vbTab & vbTab & "Accumulated" & vbCrLf & _
                  "Year" & vbTab & "Rate   Depreciation Book Value " & vbTab & "Depreciation" & vbCrLf & _
                  "======================================" & vbCrLf
                      
    Do
        strSchedule = strSchedule & period & vbTab & depreciationRate & "%" & vbTab & _
                      FormatNumber(yearlyDepreciation) & vbTab & _
                      FormatNumber(Format(bookValue - (yearlyDepreciation * period), "#")) & _
                      vbTab & vbTab & FormatNumber(yearlyDepreciation * period) & vbCrLf

        period = period + 1
    Loop Until period = estimatedLife + 1

    MsgBox strSchedule, vbOKOnly Or vbInformation, "Depreciation Schedule"
End Sub

Return to Microsoft Access and switch the to Form View
Click Asset Original Value and type 5800
Click Salvage Value and type 2000
Click Periods and type 5
Click the Calculate button
In the input box, type 3
Save and close the form

Doing a Loop While Something is Happening

The Visual Basic language provides another loop option that uses the Do While... Loop expression. Its formula is:

Do While condition
    statement(s)
Loop

This time, the condition is checked first. If the condition is true, then the statement(s) execute(s). Then the condition is checked again. If the Condition is false, or once/when the condition becomes false, the statement(s) section is(are) skipped and the flow continues with the code below the Loop keyword.

Practical Learning: Doing a Loop Until Something Happens

On the Ribbon, click File and click Open
In the list of files, click Chemistry1 from Lesson 13
On the Ribbon, click Create and click Class Module

Type:

Public AtomicNumber As Integer
Public ChemicalSymbol As String
Public ElementName As String
Public AtomicMass As String

In the Project window, click (Name) and type Element
Close Microsoft Visual Basic
On the Ribbon, click Create and click Form Design
On the Ribbon, click Button and click the form. If a wizard starts, click Cancel
On the form, right-click the button and click Build Event...
In the Choose Builder dialog box, click Code Builder and click OK

Implement the event as follows:

Private Sub Command0_Click()
    Dim strSummary
    Dim elm As Element
    Dim nbr As Integer
    Dim current As Element
    Dim periodicTable As New Collection

    Set elm = New Element
    elm.AtomicNumber = 1
    elm.ChemicalSymbol = "H"
    elm.ElementName = "Hydrogen"
    elm.AtomicMass = 1.0079
    periodicTable.Add elm

    Set elm = New Element
    elm.AtomicNumber = 2
    elm.ChemicalSymbol = "He"
    elm.ElementName = "Helium"
    elm.AtomicMass = 4.002682
    periodicTable.Add elm

    Set elm = New Element
    elm.AtomicNumber = 3
    elm.ChemicalSymbol = "Li"
    elm.ElementName = "Lithium"
    elm.AtomicMass = 6.941
    periodicTable.Add elm

    Set elm = New Element
    elm.AtomicNumber = 4
    elm.ChemicalSymbol = "Be"
    elm.ElementName = "Berylium"
    elm.AtomicMass = 9.0122
    periodicTable.Add elm

    Set elm = New Element
    elm.AtomicNumber = 5
    elm.ChemicalSymbol = "B"
    elm.ElementName = "Boron"
    elm.AtomicMass = 10.811
    periodicTable.Add elm

    Set elm = New Element
    elm.AtomicNumber = 6
    elm.ChemicalSymbol = "C"
    elm.ElementName = "Carbon"
    elm.AtomicMass = 12.011
    periodicTable.Add elm

    Set elm = New Element
    elm.AtomicNumber = 7
    elm.ChemicalSymbol = "N"
    elm.ElementName = "Nitrogen"
    elm.AtomicMass = 14.007
    periodicTable.Add elm

    Set elm = New Element
    elm.AtomicNumber = 8
    elm.ChemicalSymbol = "O"
    elm.ElementName = "Oxygen"
    elm.AtomicMass = 15.999
    periodicTable.Add elm

    strSummary = "Chemistry - Periodic Table" & vbCrLf & _
                 "=+=+=+=+=+=+=+=+=+=+=+=" & vbCrLf & _
                 "Atomic #" & vbTab & "Symbol" & vbTab & "Element" & _
                 vbTab & "Mass" & vbCrLf

    nbr = 1
    
    Do While nbr <= periodicTable.Count
        Set current = periodicTable(nbr)
        
        strSummary = strSummary & current.AtomicNumber & vbTab & _
                     current.ChemicalSymbol & vbTab & current.ElementName & _
                     vbTab & current.AtomicMass & vbCrLf
        
        nbr = nbr + 1
    Loop
    
    MsgBox strSummary, vbOKOnly Or vbInformation, _
           "Chemistry - Periodic Table"
End Sub

Return to Microsoft Access and switch the to Form View
Click the button:
Save and close the form

Do Something Until a Loop Ends

An alternative to the Do While... Loop loop uses the following formula:

Do Until condition
    statement(s)
Loop

This loop works like the Do While... Loop expression. The condition is examined first. If the condition is true, then the statement(s) section executes.

Practical Learning: Ending the Lesson

Close Microsoft Access


Previous	Copyright © 2002-2022, FunctionX, Inc.	Next