Hi!
VB 6.0 - MS Access 2000.
I'm trying to use two recordsets simultaneously as follows:
Set rstFolio = New ADODB.Recordset
Set rstCode = New ADODB.Recordset
strSQLText = "Select [Folio] from [StudentDataTest] order by [Folio]"
Set rstFolio.DataSource = ExecuteSQL(strSQLText, strMsgText)
rstFolio.MoveFirst
Do While Not rstFolio.EOF '1. There is another Folio.
strNewFolio = rstFolio!Folio
strSQLText = "Select [Amount],
VB 6.0 - MS Access 2000.
I'm trying to use two recordsets simultaneously as follows:
Set rstFolio = New ADODB.Recordset
Set rstCode = New ADODB.Recordset
strSQLText = "Select [Folio] from [StudentDataTest] order by [Folio]"
Set rstFolio.DataSource = ExecuteSQL(strSQLText, strMsgText)
rstFolio.MoveFirst
Do While Not rstFolio.EOF '1. There is another Folio.
strNewFolio = rstFolio!Folio
strSQLText = "Select [Amount],
Code:
from [Receipts]" _
& " Where [Folio] = '" & strNewFolio & "' order by [Code]"
Set rstCode.DataSource = ExecuteSQL(strSQLText, strMsgText)
rstCode.MoveFirst
strCode = ""
strPreviousCode = ""
curAmount = 0
Do While Not rstCode.EOF '2. There is another Payment.
strCode = rstCode!Code
curAmount = rstCode!Amount
Loop '2. Do While Not rstCode.EOF
Set rstCode = Nothing
rstFolio.MoveNext
strNewFolio = ""
Loop '1. Do While Not rstReceipts.EOF
Set rstFolio = Nothing
The code in "ExecuteSQL(strSQLText, strMsgText)" - declared as follows:
Public Function ExecuteSQL(ByVal SQL _
As String, MsgString As String) _
As ADODB.Recordset
declares and opens a connection object & then - after returning a recordset - closes the connection.
I get:
"Run-Time Error 3704
Operation is not allowed when the object is closed."
as soon as I try & do a Move First on the second Recordset -"rstCode.MoveFirst".
I have been reading in the VB 6.0 Help files about opening multiple recordsets but I'm not understanding what I need to do to allow me to have two or more recordsets open & running simultaneously.
I would appreciate it if I could be pointed in the right direction.
Thanks!!
Robin