Problem With 'Run-Time Error 3074'.

[Robeen]

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],

 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

 

[ddc821]

Try loading rstFolio into an Array, then setting rstFolio = nothing. Then you can use rstCode without problems.

 

[Robeen]

Thanks ddc821!

Are you recommending that I do not have two active recordsets going at the same time and instead get all my unique IDs into an array variable before opening the second recordset?

If so, I had considered that - and I might end up doing that eventually - the thing is I like the idea of using two recordsets simultaneously. Also - as I mentioned - what I'm attempting worked before it started giving me the error [???] - so I know it can work.

In fact, I will need to use a third recordset to insert records into a third table which stores data on Late & Short Payments. So - I would really like to be able to use multiple recordsets - 'one within another within another'.

So - I still need help!

Robeen