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.
Thanks in advance.
How can I move through (print) all the records in my table to my report.
Code:
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.