Unable to get filter to work on DoCmd.OpenReport

[Petrol]

I need to create a series of separate reports with content derived from a query, qry_LOI2. So I loop through a recordset based on the query, with a filter selecting one record at a time. At least, that's the idea. In practice it produces the same report each time, always based on the first record in the recordset. My code is

Private Sub lblProduceLettersButton_Click()
'
'   Produce the LOIs for the coming Walks, if Walk details and STC and Registrar are all defined
'
Dim EmailSalutation As String, EmailBody As String, EmailSignOff As String, EmailText As String
Dim rsLOI2 As Recordset, strFilter As String, strLOIFile As String
 
Select Case SendOption
    Case 1                    ' Send option = Print & post
... (irrelevant)
 
    Case 2                      ' Send option = email = we'll have to do the letters individually
        Set rsLOI2 = CurrentDb.OpenRecordset("qry_LOI2", dbOpenForwardOnly)
        '   Loop thru qry_LOI2 records and do letters one by one
        If (rsLOI2.BOF And rsLOI2.EOF) Then
            MsgBox "There are no letters to print. " & vbCrLf & _
                   "Check that next Walks, STC and Registrar are all defined."
        Else
            strLOIFile = Environ("TEMP") & "\LOIFile.PDF"
            Do Until (rsLOI2.EOF = True)
                strFilter = "PersonID=" & rsLOI2!PersonID
                DoCmd.OpenReport "31: Letters of Invitation", acViewPreview, , strFilter, , R31OpenArgs
Debug.Print rsLOI2!PersonID, rsLOI2!First_name             '''''''''''''''''''''''''''''''''''''
                DoCmd.OutputTo acOutputReport, , acFormatPDF, strLOIFile
...
...   (irrelevant - sends the LOI file as an attachment by email)
...
                Kill strLOIFile                ' Delete the file
                rsLOI2.MoveNext
            Loop
            Me!lblEmailsDone.Visible = True
        End If
 
End Select
 
End Sub
 

The qry_LOI2 definitely has seven records with unique numeric identifiers PersonID. The Debug.Print statement lists all seven of them.

Any help much appreciated.

[NeoPa]

Hi Petrol.

It's generally helpful to use named parameters in code when asking for help with that code - especially for calls of complicated procedures with multiple parameters, and more especially still when some are optional. That's just a tip for the future; not a chastisement ;-)

In this case the parameters are expressed in the right order at least. So, nothing obviously wrong. It would be helpful to see the Debug info, but also to ensure you include strFilter in your Debug command.

Another question for you is "Have you traced the code and checked whether or not the Report opens to the correct page within Access at least?". You don't say explicitly but I suspect you are seeing the first record every time when you look at the results of the DoCmd.OutpuTo() call?

[Petrol]

Not sure that I fully understood that ... do you mean it would be more helpful to say

DoCmd.OpenReport ReportName:="31: Letters of Invitation", View:=acViewPreview,,WhereCondition:=strFilter,,OpenArgs:=R31OpenArgs
DoCmd.OutputTo ObjectType:=acOutputReport,, ObjectFormat:=acOutputPDF, OutputFile:=strLOIFile]

?
I must admit I haven't really used that form, but hopefully this will make it clearer.

Thanks for the tip about adding strFilter to the Debug.Print. I had checked it before in the immediate window, but I've now added it to the Debug.Print statement. The result is below:

PersonID=28    28           Althea
PersonID=29    29           Brenda
PersonID=30    30           Corrie
PersonID=31    31           Di
PersonID=32    32           Evelyn
PersonID=33    33           Francine
PersonID=34    34           Grace
 

Finally, yes it's possible that the OutputTo is just outputting the first report over and over, but I did put a breakpoint on the OutputTo statement (line 24) and at each iteration of the loop it was the first report that was shown on screen. Baffling!

[NeoPa]

Baffling indeed. But - you've answered all my questions which is good. I'm finding you increasingly easy to work with.

In the rest of the news it's still unclear why it isn't working :-( The WhereCondition value appears perfectly set and the call is set up correctly (It was before but it was just harder work to check with positional parameters).

Ah. Hang on. We don't have all your code within the loop. I'm guessing the Report is never closed from one iteration to the next. If you open a Report that is already open the effect is simply to switch focus to the existing report. If you want to run it with different parameters then you will certainly need to close it before opening it again.

I don't see where the documentation makes that clear but I found it to be the case in my testing.

[Petrol]

Aha! That may well be it.
I won't have a chance to test it today, but it sounds like you may have hit upon the solution. I'll keep you posted. :)

[NeoPa]

Yes please :-)

[NeoPa]

It looks like instead of adding the extra value of strFilter to the Debug.Print I should have asked for the value of Reports("31: Letters of Invitation").Filter. Or maybe even both actually. That would have shown the problem easily enough. I'll know for next time.

[Petrol]

NeoPa, you're a legend!
Post #4 (close report) did the trick.
Thank you.

[NeoPa]

Very pleased to hear that helped Petrol. Particularly as I had to learn something new myself in order to post it ;-)