ADODB Recordset

[crosley4]

This function worked in Access 2003, but does not in 2007, can anyone
please tell me why not. The ADODB .Recordset declaration and the .
Active Connection parameter don't seem to work for starters.

What I am doing is scroll through a recordset, "CurrentGradStudents
table" and storing the values for display on a report into a variable
"strHold" then formating the value in a field called "cltDest".Public Function LookUpAbsentias()
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
Dim strHold As Variant
Dim ctlDest As Controls
Dim cltDest As Variant

With rst

Set .ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset
.Open "LookUpAbsentias", Options:=adCmdTableDirect
If .RecordCount > 0 Then
.MoveFirst
Do Until .EOF
strHold = strHold & "" & .Fields("Name") & " ("
& .Fields("Term") & ") " & ", "
.MoveNext
Loop
End If

cltDest = "Absentia:" & vbCrLf & strHold & vbCrLf & vbCrLf & _
"* F=Fall Only; S=Spring Only; Y=Fall and Spring "
'MsgBox cltDest
Reports![FieldList2Report]![Absentias].Caption = cltDest
.Close


End With

Set rst = Nothing

End Function

 

[Tom van Stiphout]

On Wed, 29 Oct 2008 06:50:27 -0700 (PDT), (e-mail address removed) wrote:

Make sure you set a reference to the ADO library. Code window > Tools

References

-Tom.
Microsoft Access MVP

This function worked in Access 2003, but does not in 2007, can anyone
please tell me why not. The ADODB .Recordset declaration and the .
Active Connection parameter don't seem to work for starters.

What I am doing is scroll through a recordset, "CurrentGradStudents
table" and storing the values for display on a report into a variable
"strHold" then formating the value in a field called "cltDest".Public Function LookUpAbsentias()
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
Dim strHold As Variant
Dim ctlDest As Controls
Dim cltDest As Variant

With rst

Set .ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset
.Open "LookUpAbsentias", Options:=adCmdTableDirect
If .RecordCount > 0 Then
.MoveFirst
Do Until .EOF
strHold = strHold & "" & .Fields("Name") & " ("
& .Fields("Term") & ") " & ", "
.MoveNext
Loop
End If

cltDest = "Absentia:" & vbCrLf & strHold & vbCrLf & vbCrLf & _
"* F=Fall Only; S=Spring Only; Y=Fall and Spring "
'MsgBox cltDest
Reports![FieldList2Report]![Absentias].Caption = cltDest
.Close


End With

Set rst = Nothing

End Function

 

[crosley4]

On Wed, 29 Oct 2008 06:50:27 -0700 (PDT), (e-mail address removed) wrote:

Make sure you set a reference to the ADO library. Code window > Tools

References

-Tom.
Microsoft Access MVP

This function worked in Access 2003, but does not in 2007, can anyone
please tell me why not. The ADODB .Recordset declaration and the .
Active Connection parameter don't seem to work for starters.

What I am doing is scroll through a recordset, "CurrentGradStudents
table"  and storing the values for display on a report into a variable
"strHold" then formating the value in a field called "cltDest".

Public Function LookUpAbsentias()
Dim rst  As ADODB.Recordset
Set rst = New ADODB.Recordset
Dim strHold As Variant
Dim ctlDest As Controls
Dim cltDest As Variant

With rst

   Set .ActiveConnection = CurrentProject.Connection
   .CursorType = adOpenKeyset
   .Open "LookUpAbsentias", Options:=adCmdTableDirect
   If .RecordCount > 0 Then
     .MoveFirst
     Do Until .EOF
       strHold = strHold & "" & .Fields("Name") & " ("
& .Fields("Term") & ") " & ", "
       .MoveNext
            Loop
        End If

 cltDest = "Absentia:" & vbCrLf & strHold & vbCrLf & vbCrLf & _
 "* F=Fall Only;  S=Spring Only; Y=Fall and Spring  "
       'MsgBox cltDest
Reports![FieldList2Report]![Absentias].Caption = cltDest
            .Close

   End With

   Set rst = Nothing

End Function- Hide quoted text -

- Show quoted text -

Thanks again.. I referenced the Miscrosoft ActiveX data object library
2.1 and the code now works....