Solved

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

Posted on 2002-07-25
7
346 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 350 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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

Technology Partners: 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!

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

738 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