Loops in VBA


Option Compare Database

Dim iCaseCount As Integer, curCost As Currency

curCost = 47

'For iCaseCount = 5 to 10

'For iCaseCount = 5 to 10 Step 2

For iCaseCount = 10 to 5 Step -2
    MsgBox(iCaseCount & " Cases Costs PKR" & curCost * iCaseCount )
Next 'iCaseCount

Loop Over Collections/Arrays

Database Object organise everything in collections. Forms collection have every opened form as an object then we can iterate over this collection using this loop


Option Compare Database

Sub ForDemo()

Dim varShipTypes As Variant, varType As Variant, myF As Form
varShipTypes = Array("overnight", "TwoDay", "ThreeDay", "Ground")

For Each varType In varShipTypes
    MsgBox(varType)
Next 'varType

For Each myF In Forms
    mF.Name
Next 'myF

End Sub
Option Compare Database

Sub DoDemo()

Dim lngCount As Long, myR as Recordset

'--------------------------------------------------------------------
'Orders and Categories are Names of Tables. OpenRecordSet open Tables.
'---------------------------------------------------------------------
'Set myR = CurrentDb.OpenRecordset("Orders")
'Do While lngCount < 25 And Not myR.EOF
    'lngCount = lngCount + myR![Qunatity]
    'MsgBox lngCount
    'myR.MoveNext
'Loop

Set myR = CurrentDb.OpenRecordset("Categories")
Do Until myR.EOF
    MsgBox myR.[CategoryName]
    myR.MoveNext
End Sub
Previous
Next