?
Solved

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

Posted on 2002-07-25
7
Medium Priority
?
348 Views
Last Modified: 2010-05-02
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!
0
Comment
Question by:kevink34
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 4

Expert Comment

by:RichW
ID: 7178242
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
 
LVL 5

Accepted Solution

by:
bob_online earned 1400 total points
ID: 7178245
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
 
LVL 4

Expert Comment

by:RichW
ID: 7178318
Like I said, I think the '' as [Current Balance] isn't linking to a db field.

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7178340
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
 
LVL 4

Expert Comment

by:RichW
ID: 7178353
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
 

Author Comment

by:kevink34
ID: 7178392
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
 

Author Comment

by:kevink34
ID: 7178415
One more thing....  I changed the recSearchResults.Update "[Current Balance]", curBalance
to
recSearchResults![Current Balance] = curBalance
to get it to work!
             
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question