vb 6.0 - MSAccess 2000.
I'm using two recordsets as follows:
rst1 - to Get all the [unique] IDs from a Master Table - using an SQL statement as the DataSource.
Then I'm Looping through the IDs one at a time & for each ID
I'm opening a 2nd Recordset with a different SQL statement as its DataSource - to find all payments & payment-types made by the current ID.
I get the Error when I do a MoveFirst on the Second rs.
Both recordsets are populated using a function ExecuteSQL which is in a bas Module.
Here is the code:
Private Sub cmdDefaulters_Click()
Set rstFolio = New ADODB.Recordset
Set rstCode = New ADODB.Recordset
Set rstDefaulters = 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 in the Table.
strFolio = rstFolio!Folio
strSQLText = "Select [Amount],
I'm using two recordsets as follows:
rst1 - to Get all the [unique] IDs from a Master Table - using an SQL statement as the DataSource.
Then I'm Looping through the IDs one at a time & for each ID
I'm opening a 2nd Recordset with a different SQL statement as its DataSource - to find all payments & payment-types made by the current ID.
I get the Error when I do a MoveFirst on the Second rs.
Both recordsets are populated using a function ExecuteSQL which is in a bas Module.
Here is the code:
Private Sub cmdDefaulters_Click()
Set rstFolio = New ADODB.Recordset
Set rstCode = New ADODB.Recordset
Set rstDefaulters = 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 in the Table.
strFolio = rstFolio!Folio
strSQLText = "Select [Amount],
Code:
from [Receipts]" _
& " Where [Folio] = '" & strFolio & "' " _
& " order by [Folio], [Code]"
Set rstCode.DataSource = ExecuteSQL(strSQLText, strMsgText)
rstCode.MoveFirst [b]'Error happens here.[/b]
[b]' [Run_Time Error 3074. - Operation is not allowed when object is closed].[/b]
Do While Not rstCode.EOF
strCode = rstCode!Code
curAmount = rstCode!Amount
' . . . processing . . .
rstCode.MoveNext
Loop
rstFolio.MoveNext
Loop
Set rstFolio = Nothing
Set rstCode = Nothing
Set rstDefaulters = Nothing
[b]End Sub[/b]
__________________________________________________
Here is the code for ExecuteSQL()
[b]Public Function ExecuteSQL(ByVal SQL _
As String, MsgString As String) _
As ADODB.Recordset[/b]
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sTokens() As String
On Error GoTo ExecuteSQL_Error
sTokens = Split(SQL)
Set cnn = New ADODB.Connection
cnn.Open ConnectString
If InStr("INSERT,DELETE,UPDATE", _
UCase$(sTokens(0))) Then
cnn.Execute SQL
MsgString = sTokens(0) & _
" query successful"
Else
Set rst = New ADODB.Recordset
'rst.CursorLocation = adUseClient
rst.Open Trim$(SQL), cnn, _
adOpenKeyset, _
adLockOptimistic
rst.MoveLast 'get RecordCount
Set ExecuteSQL = rst
MsgString = rst.RecordCount & _
" records found from SQL"
End If
ExecuteSQL_Exit:
Set rst = Nothing
Set cnn = Nothing
'Set rst.ActiveConnection = Nothing
'If cnn.State = adStateOpen Then cnn.Close
'Set cnn = Nothing
Exit Function
ExecuteSQL_Error:
MsgString = "ExecuteSQL Error: " & _
Err.Description
Resume ExecuteSQL_Exit
[b]End Function[/b]
_________________________________________________________
[b]Public Function ConnectString() _
As String[/b]
ConnectString = "Provider=" & _
"Microsoft.Jet.OLEDB." & _
"4.0;Data Source=" & _
App.Path & "\bpsacdat-3-14-03.mdb"
[b]End Function[/b]
_____________________________________________
I tried CCLINT'S suggestions [appearing commented out in ExecuteSQL() above] but they didn't solve the problem.
Also - the above code [b]worked[/b] several times inbetween the times it crashed with that 3074 error . . .
I'd appreciate any help.
Robeen