I have a couple of fields in a report that are populated via VBA
Code:
Public Function StarsBenchmark(ByRef msr As String, msryear As Long) As Double
Dim strsql As String, rs As New ADODB.Recordset
strsql = "Select Beg, End from IStar_Matrix where submeasureCode = """ & _
msr & """ and star = 5 and Measureyear = " & msryear
rs.Open strsql, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
If Not rs.EOF Then
If Left(msr, 3) = "PCR" Then
StarsBenchmark = rs!high
Else
StarsBenchmark = rs!Low
End If
Else
StarsBenchmark = 0
End If
rs.Close
Set rs = Nothing
End Function
However Everytime I run the report I get a Run-Time error in the rs.open line in the Function above: Method 'Open' of object '_Recordset' failed.
I've checked the SQL and it runs perfectly in the Query Analyzer. What am I missing?