Runtime Errors are not matching

Question

I have the following code to retrieve a specific record based on Game ID and date.
Public Function Most_Recent_By_GameID_And_Date(ByVal lngGameID As Long, _
ByVal dtmDate As Date) _
As ADODB.Recordset
'Purpose: Finds the days the games are drawn
'Parameters: lngGameID As Long - Game ID number
'Returns: A recordset

    On Error GoTo Most_Recent_By_GameID_And_Date_Err  
    Set dbs = CurrentDb  
      
    strSQL = "SELECT TOP 1 * FROM tblActual_Draw " & _  
             "WHERE (((tblActual_Draw.GameID) = " & [lngGameID] & ") " & _  
             "AND ((tblActual_Draw.Drw_Date) = #" & [dtmDate] & "#)) " & _  
             "ORDER BY tblActual_Draw.Drw_Date DESC;"  
              
    Set rsData = dbs.OpenRecordset(strSQL, adOpenDynamic)  
  
    With rsData  
        If .RecordCount > 0 Then  
            'Populate RecordSet  
            Set Most_Recent_By_GameID_And_Date = rsData  
        Else  
            Set Most_Recent_By_GameID_And_Date = Nothing  
        End If  
    End With  
      
Most_Recent_By_GameID_And_Date_Exit:  
    On Error Resume Next  
    Exit Function  
      
Most_Recent_By_GameID_And_Date_Err:  
    'Log error  
    Call LogError(Err.Number, _  
                  Err.Description, _  
                  "modFunctions Function Most_Recent_By_GameID_And_Date", _  
                  Now)  
    Resume Most_Recent_By_GameID_And_Date_Exit  
End Function  
  

I seem to be getting two different types of errors.

After I finish the routine I get this error.

I don't understand why.

Can someone help me. I haver used such functions in the past and didn't have any problems.

Thanks,

Answer 1

I found the answer.

I reset the references to:

I modified the VBA code:
Private rsData As Recordset

Public Function Most_Recent_By_GameID_And_Date(ByVal lngGameID As Long, _  
                                               ByVal dtmDate As Date) _  
                                               As Recordset  
    'Purpose:       Finds the days the games are drawn  
    'Parameters:    lngGameID As Long - Game ID number  
    'Returns:       A recordset  
    'Dim rs As Recordset  
    On Error GoTo Most_Recent_By_GameID_And_Date_Err  
    Set dbs = CurrentDb  
      
    strSQL = "SELECT TOP 1 * FROM tblActual_Draw " & _  
             "WHERE (((tblActual_Draw.GameID) = " & [lngGameID] & ") " & _  
             "AND ((tblActual_Draw.Drw_Date) = #" & [dtmDate] & "#)) " & _  
             "ORDER BY tblActual_Draw.Drw_Date DESC;"  
              
    Set rsData = dbs.OpenRecordset(strSQL)  
  
    With rsData  
        If .RecordCount <> 0 Then  
            'Populate RecordSet  
            Debug.Print ("Record Count = " & CStr(rsData.RecordCount))  
            Set Most_Recent_By_GameID_And_Date = rsData  
              
        Else  
            Set Most_Recent_By_GameID_And_Date = Nothing  
        End If  
    End With  
      
Most_Recent_By_GameID_And_Date_Exit:  
    On Error Resume Next  
    Exit Function  
      
Most_Recent_By_GameID_And_Date_Err:  
    'Log error  
    Call LogError(Err.Number, _  
                  Err.Description, _  
                  "modFunctions Function Most_Recent_By_GameID_And_Date", _  
                  Now)  
    Resume Most_Recent_By_GameID_And_Date_Exit  
End Function  

It seems I need to define a RecordSet of object as RecordSet not ADODB.RecordSet.

Thanks

Answer 2

Oh, I think I see it now. I think you're mixing your recordset types: one is DAO and the other is ADO. Either the function you're trying to call requires an ADO recordset, and you're passing it a DAO recordset, or your recordset variable is a DAO recordset, and the recordset returned by your function is ADO.

Without seeing the entire code, I can't tell you which one it is. Also, do you have Option Explicit in your module?

Answer 3

Hi. Thanks for the additional info. Just a wild guess, but could you try removing the square brackets around your arguments to see if that helps?

In other words, where you have:

"WHERE (((tblActual_Draw.GameID) = " & [lngGameID] & ") " & _

simply use:

"WHERE (((tblActual_Draw.GameID) = " & lngGameID & ") " & _

Also, do the same with [dtmDate]. Let us know how it goes...

Answer 4

There is no easy way to correct responses.

Error entry:

Before and after execution of function.

Answer 5

Hi. You'll have to step through the code and tell us which lines are highlighted when you get those errors.