Solved

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

Posted on 2002-07-25
7
336 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
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now