Compile Error: Method or Data Member Not Found

[ssh]

Hi,
I am borrowing some code for a function I found on this site to calculate medians by group. The original posting appears in this link: http://www.mrexcel.com/forum/archive/index.php/t-229421.html

When I tried to use this function, though, I got the error message: "Compile Error: Method or Data Member not Found" on the line of code that says Set RstSorted = RstOrig.OpenRecordset(). Can someone tell me what I am doing wrong? Below is the code I am borrowing. Thank you for your help!

ssh

Public Function MedianOfRst(RstName As String, fldName As String, Optional strWhere As String) As Double
'** Call function in query by Median: MedianOfRst("Table Name","Field Name","[Group]= " & [Group])

'This function will calculate the median of a recordset. The field must be a number value.
Dim MedianTemp As Double
Dim RstOrig As Recordset

'** NEW CODE ADDED BY GIACOMO **
Dim strSQL As String

strSQL = "SELECT [" & fldName & "] FROM [" & RstName & "]"

If strWhere <> vbNullString Then
strSQL = strSQL & " WHERE " & strWhere
End If

Set RstOrig = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
'** END OF NEW CODE **


' ** OLD CODE REPLACED IN THE BLOCK ABOVE **
'Set RstOrig = CurrentDb.OpenRecordset(RstName, dbOpenDynaset)

' ** ORIGINAL CODE **
RstOrig.Sort = fldName
Dim RstSorted As Recordset


'THIS IS WHERE THE COMPILE ERROR: METHOD OR DATA MEMBER NOT FOUND OCCURS.
Set RstSorted = RstOrig.OpenRecordset()



If RstSorted.RecordCount Mod 2 = 0 Then
RstSorted.AbsolutePosition = (RstSorted.RecordCount / 2) - 1
MedianTemp = RstSorted.Fields(fldName).Value
RstSorted.MoveNext
MedianTemp = MedianTemp + RstSorted.Fields(fldName).Value
MedianTemp = MedianTemp / 2
Else
RstSorted.AbsolutePosition = (RstSorted.RecordCount - 1) / 2
MedianTemp = RstSorted.Fields(fldName).Value
End If
MedianOfRst = MedianTemp
End Function



-kjo1080

 

[SydneyGeek]

I'm not exactly sure but, instead of having
Dim RstOrig As Recordset
change that to
Dim RstOrig As DAO.Recordset
...etc.

The reason is that Access uses both DAO and ADO object models. If you don't define which you are using (a DAO Recordset is not compatible with an ADO Recordset), you may get Access deciding on the fly... and getting it wrong.

So, define all your recordsets in this code as DAO.Recordset objects. Also, check to see that you have set a reference to the Microsoft DAO 3.6 Object Library.

Denis

 

[ssh]

Denis, thanks much for your help. That was what I needed.

 

[tatendatiffany]

I know this post has been fixed but i am exepriencing the same error with one of my code please help : on the line in bold

Function getrecordset(str) As String
 'create recordset object
     Dim rs As ADODB.Recordset
     Set rs = New ADODB.Recordset
     With rs
    .ActiveConnection = cn
    'using the str variable open the recordset
    .Source = str
    .Open
     End With
    'if recordset NOT NULL
     If Not (rs.BOF And rs.EOF) Then
    getrecordset = rs(0)
        getrecordset = Recordset
     'else
        'getrecordset = "No Result"
    getrecordset = vbNullString
    End If
                                'close and destroy recordset object
     Set rs = Nothing
    End Function
Sub getresults()
  Dim r As Integer
    Dim c As Integer
    Dim str As String
    Dim SQL As String
    Dim WhichWorksheet As String
    Dim NamedRange As String
    Dim rs As Recordset
    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection
    
   Call CheckConnection
   
   Set MFrst = Nothing
If MFcnn.State = 1 Then
    commandSQL = ThisWorkbook.Worksheets.[B]Range[/B]("Environ1" & "Environ3" & "Environ5" & "Environ7").CopyFromRecordset
    commandSQL = SQL & " FOR FETCH ONLY WITH UR;"
    Debug.Print commandSQL
    On Error GoTo fErr
    MFrst.Open commandSQL, MFcnn, adOpenStatic, adLockReadOnly
        CurrentStatus = "Updating"
    For c = 31 To 35
        For r = 2 To 39
            If Cells(r, c).Value <> "" Then
                str = Cells(r, c).Value
                Cells(r, c - 5).Value = getrecordset(str)
            End If
        Next r
    Next c
End Sub

 

[SydneyGeek]

I can't see the bold line to tell where the error occurs, but...
Have you set a reference to ADO? It's the Microsoft ActiveX Data Objects 2.x Library (2.8 for Access 2003), or version 6.x for 2007 and 2010.

Denis

 

[tatendatiffany]

sorry if i didnt clearly highlight in bold but i have fixed it now i am encounting another problem of setting up an empty recordset before the loop which will return the value of the recordset from the loop:
any ideas of how i could do this?