By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,012 Members | 1,063 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,012 IT Pros & Developers. It's quick & easy.

Unable to get filter to work on DoCmd.OpenReport

100+
P: 116
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
Expand|Select|Wrap|Line Numbers
  1. Private Sub lblProduceLettersButton_Click()
  2. '
  3. '   Produce the LOIs for the coming Walks, if Walk details and STC and Registrar are all defined
  4. '
  5. Dim EmailSalutation As String, EmailBody As String, EmailSignOff As String, EmailText As String
  6. Dim rsLOI2 As Recordset, strFilter As String, strLOIFile As String
  7.  
  8. Select Case SendOption
  9.     Case 1                    ' Send option = Print & post
  10. ... (irrelevant)
  11.  
  12.     Case 2                      ' Send option = email = we'll have to do the letters individually
  13.         Set rsLOI2 = CurrentDb.OpenRecordset("qry_LOI2", dbOpenForwardOnly)
  14.         '   Loop thru qry_LOI2 records and do letters one by one
  15.         If (rsLOI2.BOF And rsLOI2.EOF) Then
  16.             MsgBox "There are no letters to print. " & vbCrLf & _
  17.                    "Check that next Walks, STC and Registrar are all defined."
  18.         Else
  19.             strLOIFile = Environ("TEMP") & "\LOIFile.PDF"
  20.             Do Until (rsLOI2.EOF = True)
  21.                 strFilter = "PersonID=" & rsLOI2!PersonID
  22.                 DoCmd.OpenReport "31: Letters of Invitation", acViewPreview, , strFilter, , R31OpenArgs
  23. Debug.Print rsLOI2!PersonID, rsLOI2!First_name             '''''''''''''''''''''''''''''''''''''
  24.                 DoCmd.OutputTo acOutputReport, , acFormatPDF, strLOIFile
  25. ...
  26. ...   (irrelevant - sends the LOI file as an attachment by email)
  27. ...
  28.                 Kill strLOIFile                ' Delete the file
  29.                 rsLOI2.MoveNext
  30.             Loop
  31.             Me!lblEmailsDone.Visible = True
  32.         End If
  33.  
  34. End Select
  35.  
  36. End Sub
  37.  
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.
1 Week Ago #1

✓ answered by 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.

Share this Question
Share on Google+
8 Replies


NeoPa
Expert Mod 15k+
P: 31,615
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?
1 Week Ago #2

100+
P: 116
Not sure that I fully understood that ... do you mean it would be more helpful to say
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport ReportName:="31: Letters of Invitation", View:=acViewPreview,,WhereCondition:=strFilter,,OpenArgs:=R31OpenArgs
  2. 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:
Expand|Select|Wrap|Line Numbers
  1. PersonID=28    28           Althea
  2. PersonID=29    29           Brenda
  3. PersonID=30    30           Corrie
  4. PersonID=31    31           Di
  5. PersonID=32    32           Evelyn
  6. PersonID=33    33           Francine
  7. PersonID=34    34           Grace
  8.  
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!
1 Week Ago #3

NeoPa
Expert Mod 15k+
P: 31,615
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.
1 Week Ago #4

100+
P: 116
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. :)
1 Week Ago #5

NeoPa
Expert Mod 15k+
P: 31,615
Yes please :-)
1 Week Ago #6

NeoPa
Expert Mod 15k+
P: 31,615
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.
1 Week Ago #7

100+
P: 116
NeoPa, you're a legend!
Post #4 (close report) did the trick.
Thank you.
1 Week Ago #8

NeoPa
Expert Mod 15k+
P: 31,615
Very pleased to hear that helped Petrol. Particularly as I had to learn something new myself in order to post it ;-)
1 Week Ago #9

Post your reply

Sign in to post your reply or Sign up for a free account.