Why won't this work!?

[Hootyman]

Gday, I'm getting an error " operation is not allowed when object is open"
I'm not sure which object the error is referring to. Can you have more than 1
recordset open at a time?


Dim rsAllClients As New ADODB.Recordset
Dim strSql As String
'returns all account info for a specific client
Dim rsClientAccount As New ADODB.Recordset
'retrurns the latest balance for a specific client
Dim rsClientBal As New ADODB.Recordset
Dim strLatestBal As String

'record count of clients for a trading period
Dim rCount As Integer
'
Dim Cnumber As String

rCount = 0

strSql = "SELECT Header.ClientNumber FROM Header WHERE
(((Header.ClientApp)=True) AND" & _
"((Header.[Date Allocated])<= #" & period1Date & "#));"

rsAllClients.Open strSql, CurrentProject.Connection
Do While Not rsAllClients.EOF
Debug.Print rsAllClients!ClientNumber;
rsAllClients.MoveNext
rCount = rCount + 1
Debug.Print rCount
Loop




'walk through the list of clients for the trading period and add latest
balance to latestBalanceTotal



rsAllClients.MoveFirst
Do While Not rsAllClients.EOF
strLatestBal = "SELECT Accounts.balance, Accounts.transactionDate FROM
Accounts " & _
"WHERE Accounts.ClientNumber = '" & rsAllClients!ClientNumber & "' ORDER
BY Accounts.transactionDate DESC;"
Debug.Print Cnumber
'rsClientBal.ActiveConnection = Nothing
rsClientBal.Open strLatestBal, CurrentProject.Connection
rsAllClients.MoveNext
Loop
rsAllClients.Close
Set rsAllClients = Nothing

 

[Robert Morley]

Gday, I'm getting an error " operation is not allowed when object is open"
I'm not sure which object the error is referring to. Can you have more than 1
recordset open at a time?

Do While Not rsAllClients.EOF
strLatestBal = "SELECT Accounts.balance, Accounts.transactionDate FROM
Accounts " & _
"WHERE Accounts.ClientNumber = '" & rsAllClients!ClientNumber & "' ORDER
BY Accounts.transactionDate DESC;"
Debug.Print Cnumber
'rsClientBal.ActiveConnection = Nothing
rsClientBal.Open strLatestBal, CurrentProject.Connection
rsAllClients.MoveNext
Loop
rsAllClients.Close

The problem lies in this block. You're opening rsClientBal within the loop,
but only closing it outside the loop. ADO doesn't allow you to multiply
open the same recordset without closing it, so the simplest solution here is
probably just to move your Close inside the loop.

That said, opening and closing a recordset in a loop is generally going to
be slow. Depending on the size of the Accounts recordset, it might be
better to just open the entire thing and use Find or Filter, or create a
parameterized query and refresh that as needed. (Not sure if that last one
is feasible or better, though...I usually use Stored Procedures in SQL
Server for that, not ADO's capabilities.)


Rob