Access VBA project.. Creating a loop to go via a recordset

Question

I have an Access VBA project that I am working on and it involves Looping. I have three fields Fund #, Effective date, and Total Amount. I am trying to create a VBA code that will loop via the account ID and sum up the Total Amounts based off the Fund Activity in the last 5 days (effective Date). Basically, my in input from my table looks like this.

Fund:   Effective Date, Total Amount 
586     01/02/2015       -454
586     01/03/2015      -454
586     01/04/2015     -454
586     01/05/2015     -454
586     01/06/2015    -854
586     01/07/2015     -954
586     01/08/2015     -254
586     01/09/2015    -154
586     01/10/2015    -654
586     01/13/2015    -354
486 01/02/2015    -954
486 01/03/2015    -954
486 01/05/2015    -954
486 01/07/2015    -954
486 01/09/2015    -954
486 01/010/2015    -954

The VBA will identify the fund number. Will look at the date and sum up funds total outflow in the last five days using the effective date. Then the loop will go back to the second date and add the next five days total outflow for that fund, once it's done looping st 5 days it will move to the next date etc. Once it completes looping via that fund #, it will move to the next fund # and do the same thing as mentioned fund above

The goal is to insert the final output into another table and will look something like this

Id Date , ID Amount1, ID Amount1, ID Amount1, ID Amount1, Largest Redemption week date 
Fund #      Id Date ,   01/02- 1/07 01/03- 1/08 01/04- 1/09 01/05- 1/10, 
Etc Largest Redemption
Date    Largest Redemtption Week Amount 
44      -20788  -5788   -10500  5885    12/20/2014  -45855
88      -10788  -2788   -8500   3885    06/30/2014  -50000
60      -13788  -2788   -2500   1885    06/30/2014  -25000

        So the final output will be inserted into another table and will look something like this

This is what I have so far.. I need to create a Access vba code so will give me the out put above. Thanks for you help

Option Compare Database

Function OpenRecordset()

Dim dbsArchive As Database Dim rs As DAO.Recordset Dim rs2 As DAO.Recordset Dim i As Double Dim x As Double Dim StrSQL As String Dim strFund As String Dim strCriteria As String Dim AddFundCriteria As Double Dim FirstMark As Variant Dim IDFund As Double Dim IDDate As Double

Set dbsArchive = CurrentDb Set rs = dbsArchive.OpenRecordset("2014")

For i = 0 To rs.RecordCount - 1 strFund = rs.Fields("Link_Fund")

   StrSQL = "SELECT [USysD07366-2014].LINK_FUND, [USysD07366-2014].SUPER_SHEET_DATE," & _
             "Sum([USysD07366-2014]![REDEMPT]+[USysD07366-2014]![EXCHANGE OUT]) AS RedemptionTotal " & _
            "FROM [USysD07366-2014] " & _
            "GROUP BY [USysD07366-2014].LINK_FUND, [USysD07366-2014].SUPER_SHEET_DATE " & _
            "HAVING ((([USysD07366-2014].LINK_FUND) = " & strFund & ")) " & _
            "ORDER BY [USysD07366-2014].SUPER_SHEET_DATE; "

            Set rs2 = dbsArchive.OpenRecordset(StrSQL, dbOpenSnapshot)

For x = 0 To rs2.RecordCount - 1

' strCriteria = "Link Fund = " & rstCategories![Link Fund]

            Debug.Print rs2.Fields("Link_Fund")
            Debug.Print rs2.Fields("SUPER_SHEET_DATE")
            Debug.Print rs2.Fields("RedemptionTotal")

            rs2.MoveNext

               Next x

               rs.MoveNext

               Next i

            rs.Close
            rs2.Close
            Set rs = Nothing
            Set rs2 = Nothing
            dbsArchive.Close

ErrorHandler: MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description

        End Function

dfader2 dfader2 · Accepted Answer · 2015-04-17T17:50:04

I don't have enough reputation to add a comment so I have to ask here:

There's a number of things you might want to clear up first. When you say "the last five days", do you mean within the last 5 days, or for the last five records? If a date is skipped how do you want it to be handled?

486 01/02/2015 -954
486 01/03/2015 -954
486 01/05/2015 -954
486 01/07/2015 -954
486 01/09/2015 -954

If we're analyzing for 01/09/2015, should it sum all of these records, or only the dates > 01/04/2015? What about for dates where there are less than 5 days of data (i.e. 01/02/2015)? Should it still sum up whatever data is available, or not perform any analysis for that date?

I'm at work now and it's been a while since I've used VBA so I can't write out the actual code syntax, but my logic would be something like this:

var j = 2   # used for tracking row in solution sheet
var k = 0   # used for tracking column in solution sheet
var prevFund = Cells(2,1).Value
For each Row:
  var fundID = Cells(ActiveCell.Row, 1).Value
  k = k + 1  
  if (fundID <> prevFund)
    j = j + 1
    k = 0
  end if 
  var currentDate = Cells(ActiveCell.Row, 2).Value  
  var minDate = {currentDate - 5}  # not sure of the correct function for this  
  var sum = Cells(ActiveCell.Row, 3).Value
  for (int i =0; i < 5; i++)   
    var checkDate = Cells(ActiveCell.Row + i, 2).Value  
    if (checkDate > minDate)  
      sum = sum + Cells(ActiveCell.Row + i, 3).Value
    else
      break
    end if
  end for
  Sheets("Sheet2").Cells(j,1 + k).Value = sum
end for

This will just take care of summing up the weeks and as I said the syntax probably isn't all correct. You'll still need to handle the headers and getting the max weeks. Is the # of weeks consistent for all of the data, or can they be different depending on the fund? I'll try to update with more later if you can't figure out but please post if you do it yourself.

Access VBA project.. Creating a loop to go via a recordset

1 Answers