I've created the following VB6 code with the intention that it should loop through a recordset and pass the accno to Crystal 10 and create a statement in PDF format for each accno. However, it creates the first statement and hits an end of file when it reaches the ADOrs.movenext, which I suspect is due to the RecordSelection re-creating my original select. How do I get past this ?


'Option Explicit
Public ADOcn As New ADODB.Connection
Public ADOrs As New ADODB.Recordset

Dim rptApp As New CRAXDRT.Application
Dim rptMain As New CRAXDRT.Report

Private Sub Command2_Click()
Dim str As String

Screen.MousePointer = vbHourglass
If Not ADOrs.BOF Then
End If
Set rptMain = rptApp.OpenReport("c:\Statements\EnglishStmt.rpt")
rptMain.Database.SetDataSource ADOrs, 3, 1

Do While Not ADOrs.EOF
str = ""
str = RTrim(ADOrs.Fields(0))
rptMain.RecordSelectionFormula = "{CIF.Accno}=" & Chr(34) & str & Chr(34)
rptMain.ExportOptions.DiskFileName = "c:\TestRPT\" & str & ".pdf"
rptMain.ExportOptions.DestinationType = crEDTDiskFile
rptMain.ExportOptions.FormatType = crEFTPortableDocFormat
rptMain.Export False
Screen.MousePointer = vbDefault

End Sub

Private Sub Form_Load()

'This is the connection for Access
ADOcn.ConnectionString = "Driver=Microsoft Access Driver (*.mdb);DBQ=c:\Statements\Statements.mdb;"
'This is the connection for SQL Svr
'ADOcn.ConnectionString = "Provider=SQLOLEDB.1;Server=hqcsql02\hqcsql02;Data base=Statements;Trusted_Connection=Yes"

ADOrs.Open "select * from CIF order by Accno", ADOcn, adOpenDynamic, adLockOptimistic

End Sub