How to use vb6 recordset loop in crystal reports?

[sathyguy]

Friends,

VB6
Crystal Reports 9
MS-Access

I am always creating a temporary tables for reports. the report is ok...its working perfect.
I am doing all the calculations in the front end (vb6) and saving those recordset output in a table and i am just displaying it in the crystal reports 9.
Please see the below code and help me on using the loop for the crystal reports without using the temp table.
by the way...is my method correct?

Private Sub cmdbalrep_Click()
Set cn = New ADODB.Connection
Dim rsbalrep As New ADODB.Recordset

cn.Open "DSN=admn1314"
cn.Execute "delete * from balrep"

balsql = "select [stdentry.brno],[stdentry.stdname],[stdentry.amtobepaid]," _
& "sum([feesdetail.paidamt]) as totpaid from stdentry,feesdetail where " _
& "([feesdetail.delstat]='N') and [stdentry.brno]=[feesdetail.brno] and [stdentry.refundstat]='N' " _
& "group by [stdentry.brno],[stdentry.stdname],[stdentry.amtobepaid]"

rsbalrep.Open balsql, cn, adOpenDynamic

Do While Not rsbalrep.EOF
    snoval = snoval + 1
    brnoval = rsbalrep("brno")
    stdnameval = rsbalrep("stdname")
    
    amtbpaidval = Format(rsbalrep("amtobepaid"), "###,###,###")
    totpaidval = Format(rsbalrep("totpaid"), "###,###,###")
    
    balval = amtbpaidval - totpaidval
    
    If balval = 0 Then
    balval = 0
    Else
    balval = Format(balval, "###,###,###")
    End If
    
    cn.Execute "insert into balrep values ('" & snoval & "','" & brnoval & "','" & stdnameval & "'," _
    & "'" & amtbpaidval & "','" & totpaidval & "','" & balval & "')"
    
    rsbalrep.MoveNext
Loop

rsbalrep.Close
Set rsbalrep = Nothing

cn.Close
Set cn = Nothing

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

    
Set Report1 = Nothing
Set cn = New ADODB.Connection
cn.Open "dsn=admn1314"
      
cn.Execute "delete * from refrep"

cn.Close
Set cn = Nothing
End Sub

thanks

[sgarv]

The short answer to your question whether you can do this without the intermediary temp table is Yes, you can :-)

I have some remarks about your code. I hope you are not bothered by them.

The procedure that you present uses various variables, including snoval, brnoval, stdnameval, amtbpaidval, totpaidval and balval. Where are these declared? What is their type? For example, balval. At one point you use it as a numerical type:

balval = amtbpaidval - totpaidval

Then a few lines down you use it as a string:

balval = Format(balval, "###,###,###")

This is not good because it can lend itself to confusion, both for the developer and the VB6 environment.

If these variables are used only inside this procedure then they should be declared within it. If they are used in various procedures then you need to design the app in a way that they won't.

For example, going back to balval. You could define it as Currency then in the insert do something like this:

cn.Execute "insert into balrep values ('" & snoval & "','" & brnoval & "','" & stdnameval & "'," _
& "'" & amtbpaidval & "','" & totpaidval & "','" & Format(balval, "###,###,###") & "')"

Now, to answer your question. You can open the original record set, disconnect it, set values in the loop (snoval) then finally pass the record set to the report. Also, leave the amounts as numerical (do not format using the ###,###,### format) in the VB6 code and then do the formatting in the report.

I have a question: Is the BalRep table the only table that is used in the report?

Regards, SGarv

[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.