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

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

On Wed, 29 Oct 2008 06:50:27 -0700 (PDT), (E-Mail 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
>
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

On Oct 29, 10:07*am, Tom van Stiphout <tom7744.no.s...@cox.net> wrote:
> On Wed, 29 Oct 2008 06:50:27 -0700 (PDT), crosl...@gmail.com 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....