How to use vb6 recordset loop in crystal reports?

[sgarv]

Here is one possible way to do it. Please note that this code is untested, as I do not have the tables and data that you have. I am sure that you can adapt and debug it to your needs.

From the stdentry and feesdetail tables you get:

stdentry.brno,
stdentry.stdname,
stdentry.amtobepaid,
sum(feesdetail.paidamt) as totpaid

You calculate a sequential number (snoval) and a final balance balval. Then you insert these values into the balrep table:

--snoval - sequential number, base 1.
--brnoval - comes directly from brno
--stdnameval - comes directly from strname
--amtbpaidval - comes directly from amtobepaid
--totpaidval - comes directly from totpaid
--balval - calculated: amtbpaidval – totpaidval

Declarations:

Dim ibalsql as string
Dim snoval as integer  ‘If more than 32K items use Long instead.

Change your SQL to this:
Important!! The names of the fields that are found after the “AS” keyword must match exactly the names of the columns in your balrep table. If not then the report will not work. You have been warned!

balsql = "select 0 as snoval, "

balsql = balsql & "[stdentry.brno] as brnoval,"
balsql = balsql & "[stdentry.stdname] as stdnameval,"
balsql = balsql & "[stdentry.amtobepaid] as amtbpaidval,"

balsql = balsql & "sum([feesdetail.paidamt]) as totpaidval," 
balsql = balsql & "[stdentry.amtobepaid]-sum([feesdetail.paidamt]) as balval " 

balsql = balsql & "from stdentry,feesdetail "
balsql = balsql & "where "
balsql = balsql & "([feesdetail.delstat]='N') and [stdentry.brno]=[feesdetail.brno] and [stdentry.refundstat]='N' "

balsql = balsql & "group by [stdentry.brno],[stdentry.stdname],[stdentry.amtobepaid]"

Open the record set:

cn.Open "DSN=admn1314"

'You don't need this anymore. No more Temp table!
'cn.Execute "delete * from balrep"

rsbalrep.CursorLocation = adUseClient
rsbalrep.Open balsql, cn, ADODB.adOpenForwardOnly, ADODB.adLockBatchOptimistic

Set rsbalrep.ActiveConnection = Nothing

Configure the sequential number:

Do While Not rsbalrep.EOF
    isnoval = isnoval + 1

    'This *should* work. As I said before, untested code.
    rsbalrep!snoval = isnoval
    
    rsbalrep.MoveNext
Loop

Finally, send the record set to the report.

Report1.DiscardSavedData
  
'***The number listed here depends on the answer to the question
'***I asked you.
Report1.Database.Tables(1).SetDataSource rsbalrep, 3

With crview
    .ReportSource = Report1
    .ViewReport
    While crview.IsBusy
        DoEvents
    Wend
    .Zoom "100"
    .Visible = True
End With

    
Set Report1 = Nothing

Beware on closing the record set and database. While a good idea, if you close the record set before the data is sent to the report, the report may not display any data or generate some error. Experiment with different locations. For sure, you can close the record set and database right after the “While crview.IsBusy” loop.

After you modify your code you need to go into the report and format the amount data so that they display in the presentation that you want (###,###,###). Just right click the report item, select “format field” option.

Also, do not remove the balrep temp table from the Access DB. Although the report will no longer use it to generate the report, it will need it when you go into the report to edit it. There is a way to remove this dependency, but I have not explored CR enough to give you a clear indication on how to do it.
I hope this helps. Regards.