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)
curBalance = CCur(recBalance!LossAmount) - recBalance!SumPaymentAmt
recSearchResults.Update "[Current Balance]", curBalance
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!