Problem with recordset data additions

[RMOSBY]

I've got a DB with information on problem resolution as it relates to computer workstations. I've got two tables - one with details on the problems for a given day and one summarizing all of the problems by type. I'm trying to code a loop to run through the data and add summary entries as needed. Here's the code:

<code>
Private Sub cmdExecute_Click()

Dim prstCasesTemp As ADODB.Recordset
Dim prstCasesSummary As ADODB.Recordset
Dim conn As ADODB.Connection

'create needed variables
Dim techno As Double
Dim emergencyno As Single
Dim hardwareno As Single
Dim softwareno As Single
Dim installno As Single
Dim otherno As Single

'create the recordsets
Set conn = CurrentProject.Connection
Set prstCasesTemp = New ADODB.Recordset
Set prstCasesSummary = New ADODB.Recordset

prstCasesTemp.Open &quot;Cases Temp&quot;, conn, adOpenKeyset, adLockOptimistic
prstCasesSummary.Open &quot;Cases Summary&quot;, conn, adOpenKeyset, adLockOptimistic

'begin the summary loop
prstCasesTemp.MoveFirst
Do While Not prstCasesTemp.EOF
techno = prstCasesTemp![swresolvedby]

'loop while the same tech is involved
Do While prstCasesTemp![swresolvedby] = techno
If prstCasesTemp![SWPRIORITY] = &quot;1 - Emergency &quot; Then
emergencyno = emergencyno + 1
Else
Select Case prstCasesTemp![swproblemarea]
Case &quot;Hardware Fix &quot;
hardwareno = hardwareno + 1
Case &quot;Hardware/Drivers &quot;
hardwareno = hardwareno + 1
Case &quot;Software &quot;
softwareno = softwareno + 1
Case &quot;Install &quot;
installno = installno + 1
Case Else
otherno = otherno + 1
End Select
End If
prstCasesTemp.MoveNext
Loop

'add an entry to the summary table
prstCasesSummary.AddNew

'add summarized data to the summary table
prstCasesSummary![DateClosed] = prstCasesTemp![swdateresolved]
prstCasesSummary![Technician] = techno
prstCasesSummary![Emergency] = emergencyno
prstCasesSummary![Hardware] = hardwareno
prstCasesSummary![Software] = softwareno
prstCasesSummary![Install] = installno
prstCasesSummary![Other] = otherno
prstCasesSummary.Update
emergencyno = 0
hardwareno = 0
softwareno = 0
installno = 0
otherno = 0
prstCasesTemp.MoveNext
Loop

prstCasesTemp.Close
prstCasesSummary.Close

End Sub
</code>

HERE'S THE PROBLEM:
When I try to add the summarized data to the summary table, I get an error that makes no sense to me (I'm guessing it's one of those 'forest for the trees' kind of thing). The error is as follows:

Run-time error '3265':
Item cannot be found in teh collection corresponding to the requested name or ordinal.

This error occurs on the lines for adding the summary data (from prstCasesTemp) to the summary table (prstCasesSummary).

Any suggestions? I've run loops similar to this before, but this one's giving me a headache.

 

[cmmrfrds]

I think the default on the Open is for the literal expression to be a select statement. You might need to tell the Open that Cases Summary is a table - use additional parameter.

prstCasesSummary.Open &quot;Cases Summary&quot;, conn, adOpenKeyset, adLockOptimistic, adCmdTable

 

[RMOSBY]

Thanks for the suggestion to specify that the target of the 'Open' command is a table. I tried the change, but I'm still getting the same error message.

 

[cmmrfrds]

The error is when access does not find a field in the fields collection for the recordset.

Comment out all the fields but 1 since 1 field may be spelled wrong and keep working forward.

'add summarized data to the summary table
prstCasesSummary![DateClosed] = prstCasesTemp![swdateresolved]
' prstCasesSummary![Technician] = techno
' prstCasesSummary![Emergency] = emergencyno
' prstCasesSummary![Hardware] = hardwareno
' prstCasesSummary![Software] = softwareno
' prstCasesSummary![Install] = installno
' prstCasesSummary![Other] = otherno
prstCasesSummary.Update

If that is not the problem try opening a static client side cursor - probably should be anyway since it adding a new record.

prstCasesSummary.CursorLocation = adUseClient

prstCasesSummary.Open &quot;Cases Summary&quot;, conn, adOpenStatic, adLockOptimistic

 

[RMOSBY]

Thanks for the updated suggestions....

I just tried the suggestion to open a static cursor and open the recordset 'Static'. Same problem.

I've also tried working down my list one-at-a-time to see if I've simply got a mis-spelling somewhere. I started at the top of the list (and commented out the others). As I stepped through each one, I kept getting the same error message.

This one's got me pulling my hair out !! I've used this method for accessing/adding-to recordsets before. As you can see, I've got two recordsets in operation. I don't get an access error on the first - only on the one to which I'm trying to add records. I'm not even sure that the 'AddNew' is functioning (although I don't get an error, I'm not seeing new records added. If Access thinks that I'm trying to added the new data to the EOF() - maybe that's where the error is coming in at).

I'll keep trying and keep you posted if you prefer.

 

[cmmrfrds]

One other thought, maybe only 1 recordset can be active on a connection at a time. You could try adding a new connection and open the 2nd recordset on that connection. I've always closed the 1st recordset before doing something on the 2nd recordset or had 2 connections open.

Dim conn2 as New ADODB.Connection

 

[RMOSBY]

Just to make sure, I tried creating a second connection and opening the second recordset under that connection - as you can imagine, same problem.

I'm still at a loss to explain why it won't work. I've got another DB running seven recordsets under a single connection with multiple 'AddNew's and 'Update's to each one and I don't see this error coming up. I may just have to scrap and try re-coding the entire block. Something goofy's going on here.