Storing Access Output in VBA

Question

I have the following function in Access VBA

Sub SampleReadCurve()

Dim rs 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

CurveID = 15

Dim I As Integer
Dim x As Date

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

x = userdate

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)
If rs.RecordCount <> 0 Then

    rs.MoveFirst

    rs.MoveLast

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

    BucketTermAmt = 3
    BucketTermUnit = "m"
    BucketDate = DateAdd(BucketTermUnit, BucketTermAmt, MaxOfMarkAsofDate)
    InterpRate = CurveInterpolateRecordset(rs, BucketDate)
    Debug.Print BucketDate, InterpRate

End If

Next I



End Sub

Running this sub outputs a list of numbers and associated dates in the immediate window, of the form:

10/21/2015     4.14783100557042E-03 
10/20/2015     3.97301001744437E-03 
10/17/2015     4.26331322736052E-03 
10/16/2015     4.04793424134467E-03 
...

Now, I want to use this array of numbers as an input in a function. Doing this requires me to save the output of the subroutine. What is the best way of going about this? I want to save it as a RecordSet object potentially, but I do not know how to do this.

You can use one of the "collection" objects, for 2 data per row, i'd suggest using 2 collections colDate and colValue and populate them both in parallel so colDate(1) corresponds to colValue(1) Look at collection in VBA help or use an array. Can the date criteria be used and the query altering to produce a useable recordset? — Nathan_Sav
– Nathan_Sav, Commented Jan 12, 2016 at 16:07

Gareth · Accepted Answer · 2016-01-12 16:06:37Z

1

Return a recordset as the result of a function rather than procedure for example:

Function SampleReadCurve() As Recordset

'Complete database action and load recordset into variable called rs'

SampleReadCurve = rs

End Function

Then you can return the recordset as a variable in another procedure.

answered Jan 12, 2016 at 16:06

Gareth

5,2636 gold badges47 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

beeba Over a year ago

thanks. Do I need to change the existing code if I convert it to a function? I changed it to a function, and then wrote a new subroutine to declare a variable "n" and then return the recordset ("n = SampleReadCurve()") but I wasn't able to get this working

Collectives™ on Stack Overflow

Storing Access Output in VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related