Loop through Table With Access VBA

Question

Currently in Access VBA I have a filter for a table "VolatilityOutput" in Access, as follows:

Dim CurveID As Long
CurveID = 15
Dim strSQL As String
strSQL = "SELECT * FROM VolatilityOutput WHERE CurveID=" & CurveID & " AND MaxOfMarkAsofDate=#" & MaxOfMarkAsofDate & "# ORDER BY MaxOfMarkasOfDate, MaturityDate"

CurveID is a field in the table VolatilityOutput. Right now CurveID = 15 is hardcoded. Each Curve ID corresponds to a title in another field, CurveShortName. The data is stored in this table, "tblCurve":

Once the CurveID is set, the code performs some calculations on that specific data.

Now instead of selecting one CurveID at a time (such as CurveID=15) I want to change this code so that it will perform the calculation on each of the CurveIDs stored in the table. So it will do the calculation for CurveID = 13, then CurveID = 14, CurveID = 15 etc for every CurveID in the table. How can I change the above code so that it will loop through the table instead of inputting one curve at a time?

EDIT Update: The full code is posted here

Public Sub CalculateVol()


Dim vol As Double
Dim rs As Recordset
Dim rs2 As Recordset
Dim iRow As Long, iField As Long
Dim strSQL As String
Dim CurveID As Long
Dim MarkRunID As Long
Dim MaxOfMarkAsofDate As Date
Dim userdate As String

DoCmd.RunSQL "DELETE * FROM HolderTable"
'Clears out the old array from the holder table.

CurveID = 15

Dim I As Integer
Dim x As Date

userdate = InputBox("Please Enter the Date (mm/dd/yyyy)")

x = userdate

Dim BucketTermAmt As Long
BucketTermAmt = InputBox("Please Enter the Term Amount")

For I = 0 To 150

MaxOfMarkAsofDate = x - I


strSQL = "SELECT * FROM VolatilityOutput WHERE CurveID=" & CurveID & " AND MaxOfMarkAsofDate=#" & MaxOfMarkAsofDate & "# ORDER BY MaxOfMarkasOfDate, MaturityDate"

Set rs = CurrentDb.OpenRecordset(strSQL, Type:=dbOpenDynaset, Options:=dbSeeChanges)
Set rs2 = CurrentDb.OpenRecordset("HolderTable")



If rs.RecordCount <> 0 Then

    rs.MoveFirst

    rs.MoveLast


    Dim BucketTermUnit As String
    Dim BucketDate As Date
    Dim MarkAsOfDate As Date
    Dim InterpRate As Double


    Dim b As String

    b = BucketTermAmt

    BucketTermUnit = "m"
    BucketDate = DateAdd(BucketTermUnit, b, MaxOfMarkAsofDate)
    InterpRate = CurveInterpolateRecordset(rs, BucketDate)

    rs2.AddNew
    rs2("BucketDate") = BucketDate
    rs2("InterpRate") = InterpRate
    rs2.Update


End If

Next I


vol = EWMA(0.94)

Debug.Print vol


End Sub

Answer 1

You can loop through recordsets with a while or Do loop. Something like:

If rs.RecordCount <> 0 Then
    'set the cursor to the first row
    rs.MoveFirst

    Dim BucketTermUnit As String
    Dim BucketDate As Date
    Dim MarkAsOfDate As Date
    Dim InterpRate As Double


    Dim b As String

    'loop the rows
    Do Until rs.EOF 'endoffile



        b = BucketTermAmt

        BucketTermUnit = "m"
        BucketDate = DateAdd(BucketTermUnit, b, MaxOfMarkAsofDate)
        InterpRate = CurveInterpolateRecordset(rs, BucketDate)

        rs2.AddNew
        rs2("BucketDate") = BucketDate
        rs2("InterpRate") = InterpRate
        rs2.Update

        'to the next row for the next loop
        rs.movenext
    Loop


End If

Now you just load up that rs with all of your records, instead of just one, and let the loop do the work.

Because your CurveInterpolateRecordset function requires a recordset, and does something with it (I'm not going to begin to guess), it might be a better idea to add a new recordset of distinct curveID's then loop nearly the entire original routine inside that recordset. Something like:

Public Sub CalculateVol()


Dim vol As Double
Dim rs As Recordset
Dim rs2 As Recordset
Dim rsCurve as Recordset
Dim iRow As Long, iField As Long
Dim strSQL As String
Dim CurveID As Long
Dim MarkRunID As Long
Dim MaxOfMarkAsofDate As Date
Dim userdate As String

DoCmd.RunSQL "DELETE * FROM HolderTable"
'Clears out the old array from the holder table.

CurveID = 15

Dim I As Integer
Dim x As Date

userdate = InputBox("Please Enter the Date (mm/dd/yyyy)")

x = userdate

Dim BucketTermAmt As Long
BucketTermAmt = InputBox("Please Enter the Term Amount")

'get distinct curveIDs for the loop
strSQL = "SELECT CurveID FROM VolatilityOutput GROUP BY CurveID;"

'open up distinct curveID recordset
Set rsCurve = CurrentDB.OpenRecordset(strSQL, Type:=dbOpenDynaset, Options:=dbSeeChanges)

'loop through each distinct curveID
Do Until rsCurve.EOF

    'Loop for your dates
    For I = 0 To 150
        MaxOfMarkAsofDate = x - I

        'Changed this to the new curveID from the rsCurve recordset.
        strSQL = "SELECT * FROM VolatilityOutput WHERE CurveID=" & rsCurve.Fields("CurveID") & " AND MaxOfMarkAsofDate=#" & MaxOfMarkAsofDate & "# ORDER BY MaxOfMarkasOfDate, MaturityDate"

        Set rs = CurrentDb.OpenRecordset(strSQL, Type:=dbOpenDynaset, Options:=dbSeeChanges)
        Set rs2 = CurrentDb.OpenRecordset("HolderTable")


        If rs.RecordCount <> 0 Then

            rs.MoveFirst

            rs.MoveLast


            Dim BucketTermUnit As String
            Dim BucketDate As Date
            Dim MarkAsOfDate As Date
            Dim InterpRate As Double


            Dim b As String

            b = BucketTermAmt

            BucketTermUnit = "m"
            BucketDate = DateAdd(BucketTermUnit, b, MaxOfMarkAsofDate)
            InterpRate = CurveInterpolateRecordset(rs, BucketDate)

            rs2.AddNew
            rs2("BucketDate") = BucketDate
            rs2("InterpRate") = InterpRate
            rs2.Update


        End If
    Next I

    'move to next curveID
    rsCurve.MoveNext
Loop

vol = EWMA(0.94)

Debug.Print vol


End Sub