question

MarkMcCumber-4801 avatar image
0 Votes"
MarkMcCumber-4801 asked DanielPineault-1898 answered

Recordset error

Hi Everyone:
I have the following references in my Access project:
89644-ms-access-references.png


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:
89625-recordset-error.png

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


office-access-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

DanielPineault-1898 avatar image
0 Votes"
DanielPineault-1898 answered

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).

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.