Recordset error

Question

Hi Everyone:
I have the following references in my Access project:

I am trying to retrieve records for a specific ID number. The code used in the selection form is:
Option Compare Database
Option Explicit
Private lngGameID As Long, _
rsAnalyst As Recordset

Private Sub cboGameID_Click()
'Purpose: Creates a RecordSet
'Parameters: lngGameID as Long - Game ID number
'Returns: The Records for the selected game

lngGameID = CLng(Me.cboGameID.Value)  
Set rsAnalyst = RptData(lngGameID)  

End Sub

Private Sub Form_Load()
Me.cboGameID.Value = 0
End Sub

The code for the function returning the RecordSet is:
Public Function RptData(ByVal lngID As Long) As Recordset
'Purpose: Gets the report's data
'Parameters: lngID As Long - Game ID number
'Returns: A recordset

On Error GoTo RptData_Err  
Set dbs = CurrentDb  
strSQL = "SELECT tblActual_Draw.* " & _  
         "FROM tblActual_Draw " & _  
         "WHERE (((tblActual_Draw.GameID) = " & [lngID] & "))" & _  
         "ORDER BY tblActual_Draw.Drw_Date DESC;"  
  
Set rsData = dbs.OpenRecordset(strSQL)  
   
  With rsData  
    If .RecordCount <> 0 Then  
        'Populate RecordSet  
        Set RptData = rsData  
    Else  
        Set RptData = Nothing  
    End If  
End With  
  

RptData_Exit:
On Error Resume Next
Exit Function

RptData_Err:
'Log error
Call LogError(Err.Number, _
Err.Description, _
"modFunctions Function RptData ", _
Now)
Resume RptData_Exit
End Function

Instead of returning a RecordSet I am getting this error:

Why? A type mismatch. They are both RecordSets.
Thank you,
MRM256

Answer 1

I see a couple things quickly, a missing spaces before the ORDER BY, [] around lngId, missing Dim statements, I'd probably do something along the lines of

Private lngGameID As Long
Private rsAnalyst As DAO.Recordset

Private Sub cboGameID_Click()
'Purpose: Creates a RecordSet
'Parameters: lngGameID as Long - Game ID number
'Returns: The Records for the selected game
lngGameID = CLng(Me.cboGameID.Value)
Set rsAnalyst = RptData(lngGameID)
End Sub

Private Sub Form_Load()
Me.cboGameID.Value = 0
End Sub

Public Function RptData(ByVal lngID As Long) As DAO.Recordset
'Purpose: Gets the report's data
'Parameters: lngID As Long - Game ID number
'Returns: A recordset
Dim db As DAO.Database
Dim rsData As DAO.Recordset
Dim strSQL As String

On Error GoTo RptData_Err
Set dbs = CurrentDb
strSQL = "SELECT tblActual_Draw.* " & _
         "FROM tblActual_Draw " & _
         "WHERE (tblActual_Draw.GameID = " & lngID & ") " & _
         "ORDER BY tblActual_Draw.Drw_Date DESC;"

Set rsData = dbs.OpenRecordset(strSQL)
With rsData
    If .RecordCount <> 0 Then
        'Populate RecordSet
        Set RptData = rsData
    Else
        Set RptData = Nothing
    End If
End With

RptData_Exit:
On Error Resume Next
If Not rsData Is Nothing Then
rsData.Close
rsData = Nothing
End If
If Not dbs Is Nothing Then dbs = Nothing
Exit Function

RptData_Err:
'Log error
Call LogError(Err.Number, Err.Description, "modFunctions Function RptData ", Now)
Resume RptData_Exit
End Function

Also note that as of Access 2007, you don't need to include the DAO ... library as it is part of the ACE library (Microsoft Office XX.0 Access database engine Object Library).