CODEWITHSUNDEEP
sqlvbaexcelms-access
beeba
beeba

Reputation: 432

Storing Access Output in VBA

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.

Upvotes: 0

Views: 59

Answers (1)

Gareth
Gareth

Reputation: 5243

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.

Upvotes: 1

Related Questions