Solved

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

Posted on 2002-07-25
7
347 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

688 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