Printing every record in a recordset

[Ausburgh]

I have only 5 records in my table but when I run the following code below (without) the loop, it prints only the first record in the table and with the loop it prints only the last record in my table to the report.

How can I move through (print) all the records in my table to my report.

Private Sub ActiveReport_ReportStart()

    Set Conn = New ADODB.Connection
        
    'define the recordset access statement
    Dim strSQL As String
    strSQL = "SELECT * FROM COMPARE_REC"
    Set rs = New ADODB.Recordset
    
    Conn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=dbSHOW;Data Source=ABC-00001"
    Conn.Open
    
    With rs
        .ActiveConnection = Conn
        .CursorLocation = adUseServer
        .CursorType = adOpenDynamic
        .LockType = adLockOptimistic
        .Source = strSQL
        .Open
    End With
    
    With rs
        .MoveFirst
        Do While Not .EOF
        Field1.DataValue = !Dept & ""
        field2.DataValue = !FY04XP & ""
        Field3.DataValue = !FY05XP & ""
        Field4.DataValue = !SumOfAGenOff & ""
        Field5.DataValue = !SumOfFY04XP & ""
        Field6.DataValue = !SumOfFY05XP & ""
        Field7.DataValue = !SumOfFY06fAGenOff & ""
        .MoveNext
        Loop
    End With    
End Sub

Thanks in advance.

 

[Golom]

You are replacing the value of Fieldx.DataValue on each pass through the loop and they (of course) have the values from the last record when the loop finishes.

I'm not sure what the "Fieldx" object is (does it print something?) but, if not, then you need to do the printing within the loop.

 

[Ausburgh]

In my COMPARE_REC table on the SQL Server side there are 5 records.

The reports should resemble (these figures obviously don't add-up) :


Dept FY04XP FY05XP FY06XP

ABC 123.45 234.56 345.67
BCD 456.78 567.89 678.90
CDE 250.00 450.68 548.98
DEF 987.17 723.65 1354.15
EFG 2300.25 6250.00 7000.00
Total 4365.25 8689.15 10894.36


So,
Dept = Field1
FY04XP = Field2
FY05XP = Field3
FY05XP = Field4
Total for FY04XP = Field5
Total for FY05XP = Field6
Total for FY06XP = Field7


Thanks again

 

[Golom]

Yes ... I understand that.

The point though is that I don't see anywhere in your code that any actual "printing" is going on. Your loop just loads data into variables and replaces them on each iteration through the loop.

Where do you actually "print" (or display a report or whatever) the results?

 

[Ausburgh]

I'm going to dazzle you with my technical prowess here ...

It's an ActiveReport ... it just prints/outputs the data ... (yeah, profound right? haha).

My knowledge is limited to the fact that you just kind of point/link the field(s) in the report design to the appropriate field in the database (in SQL server) and it just prints it out when you run the report. Again I'm a novice.

Now when I was connected to an access database w/DAO I didn't have to worry about this issue.

Thanks

 

[BobRodes]

There's some sort of code line you use to actually print the report. What is it, please?

Bob

 

[macleod1021]

I demo'd Active Reports a long time ago, and if memory serves all you have to do is link the recordset (without looping) to your report, then call the .print method of the report object. The .DataSource property is what keeps popping into my mind as what you link the recordset to, but I'm not 100% sure. If that's the case then the following code (or something similar) would work:

Dim objReport as ActiveReport
Set objReport = new ActiveReport

objReport.DataSource = rs
objReport.Print

Set objReport = nothing

 

[PeterWallace]

try something like this


Title1 = "INTEREST ON RENT ARREARS TO Commercial Lease "
Title2 = Llord & " to " & tntname '& " " & Taddress
Today = TodaysDateIs(Now)
Printtitle = tntname

sql = "SELECT * From TenantToPay"
sql = sql & " ORDER BY Refer"

' connect and make cursor
ExecuteAltSQL sql, mstr


mTotal1 = 0
mTotal2 = 0
mTotal3 = 0
mTotal4 = 0


If Not Altrst.BOF Then Altrst.MoveFirst
Do While Not Altrst.EOF
mTotal1 = mTotal1 + FieldValue(Altrst!AmountDue)
mTotal2 = mTotal2 + FieldValue(Altrst!Amountpaid)
mTotal3 = mTotal3 + FieldValue(Altrst!Payable)
mTotal4 = mTotal4 + FieldValue(Altrst!Interest)
Altrst.MoveNext
Loop
Total1 = ShowAsMoney(CDbl(mTotal1))
Total2 = ShowAsMoney(CDbl(mTotal2))
Total3 = ShowAsMoney(CDbl(mTotal3))
Total4 = ShowAsMoney(CDbl(mTotal4))

If Not Altrst.BOF Then Altrst.MoveFirst

With rptinterest
.DataMember = vbNullString
Set .DataSource = Altrst
.Caption = "Invoice for : " & Trim(tntname)
With .Sections("Section1").Controls
.Item("Rent").DataField = Altrst.Fields(13).Name
.Item("DueDate").DataField = Altrst.Fields(1).Name
.Item("Tdate").DataField = Altrst.Fields(2).Name
.Item("AmountDue").DataField = Altrst.Fields(3).Name
.Item("AmountPaid").DataField = Altrst.Fields(4).Name
.Item("PaidTo").DataField = Altrst.Fields(7).Name
.Item("AmountInArrears").DataField = Altrst.Fields(15).Name
.Item("PerDay").DataField = Altrst.Fields(9).Name
.Item("Daysin").DataField = Altrst.Fields(10).Name
.Item("Interest").DataField = Altrst.Fields(11).Name
.Item("ToPay").DataField = Altrst.Fields(14).Name

End With
With .Sections("Section2").Controls
.Item("TName").Caption = Title1
.Item("Title1a").Caption = Taddress
.Item("TStreet").Caption = Title2
.Item("Dateis").Caption = Today

End With
With .Sections("Section5").Controls
.Item("TotalDue").Caption = Total1
.Item("TotalPaid").Caption = Total2
.Item("TotalInterest").Caption = Total4
.Item("TotalPayable").Caption = Total3

End With

rptinterest.WindowState = 2

.Show vbModal
End With




Closealtrst

 

[BobRodes]

That makes sense, mac.

Bob

 

[runals]

Seems to me that the original code flaw is that the record set (rs) is never committed, that is there is no update. Also I think you need and addnew command at the start of each loop iteration. The last response seems lots cooler though.


Andy