Recordset Iteration 1

[Binnit]

Dim db As Database
Dim rst As Recordset
Dim Agency As String

Set db = CurrentDb
Set rst = db.OpenRecordset("tblBordereauxAgencies")

Do While Not rst.EOF

Agency = rst.Fields("agencycode")
PrintBordereauxStatements (Agency)
rst.MoveNext
Loop

The idea is that it opens a report in preview mode for each agency in the table (currently 6) which would allow the user to print the report or close it before moving on to the next, however, it only opens the report with the data from the last value in the table.

How can I correct this?



Happiness is...not getting what you want but wanting what you have already got

 

[Remou]

You will need to post the code for the Sub or Function PrintBordereauxStatements

 

[Binnit]

Remou - here is the code for the function:-

Public Function PrintBordereauxStatements(AgencyRef)

DoCmd.OpenReport "rptBordereauxStatements", acViewPreview, , "[Agency]= '" & AgencyRef & "'"


End Function

Apologies for the delay.


Happiness is...not getting what you want but wanting what you have already got

 

[Binnit]

Remou
When I step through this process, each agency is passed through so the iteration bit is working, it just appears to be a problem with the "Preview" part of the process.

Happiness is...not getting what you want but wanting what you have already got

 

[Binnit]

Remou

I have now taken away the "Preview" aspect and it sends all output to the printer which is OK but would have preferred to have the preview option. At least I know the code works.

Happiness is...not getting what you want but wanting what you have already got

 

[Remou]

Does your version of Access allow you to open reports modally (WindowMode)?

[tt]DoCmd.OpenReport(ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs)[/tt]

 

[Binnit]

Still using A97 for this application and does not appear to support "Window Mode" or Open Args on Reports.

What would the value be? I can try to tag it on and see what happens.
Thanks

Happiness is...not getting what you want but wanting what you have already got

 

[Remou]

Ok. Try this, it has been a while since I used '97, but it may suit:

Public Function PrintBordereauxStatements(AgencyRef)
Const conObjStateClosed = 0
Const conDesignView = 0

DoCmd.OpenReport "rptBordereauxStatements", acViewPreview, , "[Agency]= '" & AgencyRef & "'"
    
Do While True
    If SysCmd(acSysCmdGetObjectState, acReport, "rptBordereauxStatements") = conObjStateClosed Then
        Exit Do
    End If
    DoEvents
Loop
End Function

 

[Binnit]

Remou
That worked a treat - many thanks although I know need to learn & follow how this works.

Star for you.

Happiness is...not getting what you want but wanting what you have already got