Item cannot be found in the collection corresponding to the requested name or ordinal.

Hello!  I have a search form that is based on a number of criteria and I build the sql string based on the user chosen criteria.  As a result, this string can get really involved and difficult.  I am not having a problem with that part.  The problem is that one of the criteria is an Account Balance and the original amount is on one table and the payments are on another.  I have it to the point where I can go through the main recordset and based on that criteria I can gather, one at a time, the payment amount and thus determine the balance.  The error I am receiving is when I try to update the original recordset with the new calculated balance amount.  Here is some of the code:
SELECT DISTINCT TBLCOLLECTION.CASEID as [Case ID],tblcollection.casename as [Case Name],TBLCOLLECTIONPEOPLECOMPANY.LOSSAMOUNT as [Loss Amount],'' as [Current Balance],TBLCOLLECTIONPEOPLECOMPANY.COLLECTIONPEOPLECOMPANYCOUNTERID,ITBCOLLECTIONAGENCY.AGENCYNAME as [Agency].......

Once I have that recordset, I am going through this code:
Set recSearchResults = New ADODB.Recordset
            recSearchResults.CursorLocation = adUseServer
            Set recSearchResults = rectmpSearchResults.Clone
            Do Until recSearchResults.EOF
                recBalance.Open "SELECT TBLCOLLECTIONPEOPLECOMPANY.LOSSAMOUNT, Sum(TBLCOLLECTIONPAYMENT.PAYMENTAMOUNT) AS SumPaymentAmt FROM TBLCOLLECTIONPEOPLECOMPANY LEFT JOIN TBLCOLLECTIONPAYMENT ON TBLCOLLECTIONPEOPLECOMPANY.COLLECTIONPEOPLECOMPANYCOUNTERID = TBLCOLLECTIONPAYMENT.PAYER WHERE TBLCOLLECTIONPEOPLECOMPANY.COLLECTIONPEOPLECOMPANYCOUNTERID=" & rectmpSearchResults!COLLECTIONPEOPLECOMPANYCOUNTERID & " GROUP BY TBLCOLLECTIONPEOPLECOMPANY.COLLECTIONPEOPLECOMPANYCOUNTERID, TBLCOLLECTIONPEOPLECOMPANY.LOSSAMOUNT", gcnnICMS, adOpenDynamic, adLockOptimistic, adCmdText
                Dim curBalance As Currency
                If IsNull(recBalance!SumPaymentAmt) Then
                    curBalance = CCur(recBalance!LossAmount)
                Else
                    curBalance = CCur(recBalance!LossAmount) - recBalance!SumPaymentAmt
                End If
                recBalance.Close
               
                recSearchResults.Update "[Current Balance]", curBalance
               
               
                recSearchResults.MoveNext
            Loop
           
I tried the Clone method because I did not want to change the original value of that first recordset.  Once I have all this, I am setting a dbgrid control to the recordset to display on the search form.

When I look at the fields on the cloned recordset, the name of the "Current Balance" field is shown just as I have it but I still get the error listed in the title.

Any help is appreciated!
kevink34Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
bob_onlineConnect With a Mentor Commented:
You've got some weird stuff in there.  For one thing, don't dim a variable within a loop.

However, the problem is you do not have a field called "Current Balance" in your database and therefore you can't update due your recordset connection parameters.  If you want what you're doing to work, change

recSearchResults.CursorLocation = adUseServer

to

recSearchResults.CursorLocation = adUseClient
           
I've never used clone, so check this -- does a clone have an active connection property?  If so, Set recSearchResults.ActiveConnection = Nothing

I also think that you don't need the update.

recSearchResults![Current Balance] = curBalance
               
Are you sure the square brackets aren't causing trouble?              
               
0
 
RichWCommented:
What field in the db is [Current Balance]?  You have it set as '' in the original SELECT statement.

It looks like you're updating a '' field.

Am I missing something?


0
 
RichWCommented:
Like I said, I think the '' as [Current Balance] isn't linking to a db field.

0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Anthony PerkinsCommented:
RichW

The line:
'' as [Current Balance]
is creating a blank column and is quite legal.  The fact that later on it will be updated with a numeric value curBalance, should not in itself be a problem (other than the fact that you are coercing a value from a numeric to a string).

kevink34,
I think we need more information and specifically where the error is occurring.

All the clone method buys you is that you do not have to keep track of multiple bookmarks.  I believe you will find that if the cloned recordset is modified the original recordset is also changed.

Anthony
0
 
RichWCommented:
Anthony, isn't the problem that he's doing an UPDATE on the blank column illegal?  I've never tried that before, so I don't know if it is or not.

I know you can create a blank column, but to Update it I thought was illegal.

I thought you had to have something that has an ordinal reference associated to be able to UDPATE it?

You learn something new every day.

:)
RichW
0
 
kevink34Author Commented:
THANKS BOB_ONLINE!!!!!

I have been working on this for two days.  At one point I did have adUseClient and a lot of other stuff in the code and at one point I added the dim statement while I was in run mode and never moved it.  Thanks for pointing that out!

I think the line that actually helped make it work was the Set recSearchResults.ActiveConnection = Nothing
~~~~~~~~~~~~~~~~~~~~~~
RichW, Thanks for your comments.  The reason I did the '' as [Current Balance] in my select statement was because I wanted to make sure I had a field called [Current Balance] in my final recordset.  I know I could have Appended the field to the recordset once I had it but I thought of it when I was coding that section.
~~~~~~~~~~~~~~~~~~~~~~
Thanks again for your help!
0
 
kevink34Author Commented:
One more thing....  I changed the recSearchResults.Update "[Current Balance]", curBalance
to
recSearchResults![Current Balance] = curBalance
to get it to work!
             
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.