Report Filter Problem 1

[kmclane]

I have a report I am trying to filter via code. I seem to be part way there, but the filter is not affecting the report. Here is my code so far:

Public Function PrintAgntSettleRep()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim StrFilter As String
Set db = CurrentDb
Set rs = db.OpenRecordset("AgentSettlementList")
If Not rs.BOF And Not rs.EOF Then
rs.MoveFirst
End If
Do While Not rs.EOF
StrFilter = "sortcode = """ & rs(0).Value & """"
DoCmd.OpenReport "SettlementReportPage1", acViewPreview, StrFilter
DoCmd.PrintOut acPrintAll, , , acMedium, 1
rs.MoveNext
Loop
rs.Close
Set db = Nothing
End Function

I have verfied that StrFilter is looping through the rs and being changed each time, but my report always opens with all records. I should only get two pages, instead I get 78. I have tested the filter manually and it does filter that way. I've run out of ideas as to what is wrong, I'm not getting any error messages and I do have the filter on property set to yes in my report.
Thanks, Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[Frink]

Hallo,

Try using the Where clause, instead of the filter:
DoCmd.OpenReport "SettlementReportPage1", acViewPreview, , StrFilter
instead of
DoCmd.OpenReport "SettlementReportPage1", acViewPreview, StrFilter

- Frink

 

[kmclane]

Well, that helped a bit, now I get the first two pages. Now, I have a different problem. If I leave my Printout line in I get the same two pages printed over and over, If I comment it out, I get one preview, and then when I close that nothing further happens. I am trying to automate creating individual pdf files, I've tried to go down this road before, but so far have been unsuccessful. I am trying to do this one step at a time. If I can get it to give me each section at a time I can go on to auto printing each one with a unique filename based on my Name1 field on the report. I think there is something missing that is keeping it from looping.
Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[kmclane]

Well, on further checking, it is looping, the value of StrFilter is changed through all 78 values, but it's not performing the other actions in the loop for each one. I guess I need a way to tell it to close the preview after it sends the print job?
Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[kmclane]

Ok, I fixed that. Now I have this code:

Public Function PrintAgntSettleRep()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim StrFilter As String
Dim StrName As String
Set db = CurrentDb
Set rs = db.OpenRecordset("AgentSettlementList")
If Not rs.BOF And Not rs.EOF Then
rs.MoveFirst
End If
Do While Not rs.EOF
StrFilter = "sortcode = """ & rs(0).Value & """"
StrName = Left(rs(1).Value, 20)
DoCmd.OpenReport "SettlementReportPage1", acViewPreview, , StrFilter
Debug.Print StrFilter
Debug.Print StrName
DoCmd.PrintOut acPrintAll, , , acMedium, 1
DoCmd.Close acReport, "SettlementReportPage1", acSaveNo
rs.MoveNext
Loop
rs.Close
Set db = Nothing
End Function

Which is working fine. But now I have to manually give each file a name. I have StrName pulling the data that I want to use for the file name, but I need to find a way to feed it to the program so it doesn't as for it. Then I will be truly automated! One other small issue, my Name1 field has some instances of a "/" which doesn't work in filenames, which is why I added the "Left()" as a temporary fix. Is there a simple way to say convert the "/" to a "-" on the fly?
Thanks, Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[Frink]

Hallo,

Try this:

Public Function strReplaceInString(ByRef pstrInput As String, ByRef pstrSearch As String, ByRef pstrReplace As String) As String
On Error GoTo lblErr
  Dim strResult As String
  Dim intI As Integer
  Dim intStart As Integer
  strResult = ""
  intStart = 1
  intI = InStr(intStart, pstrInput, pstrSearch)
  Do While intI > 0
    strResult = strResult & Mid$(pstrInput, intStart, intI - intStart) & pstrReplace
    intStart = intI + Len(pstrSearch)
    intI = InStr(intStart, pstrInput, pstrSearch)
  Loop
  strResult = strResult & Mid$(pstrInput, intStart)
lblExit:
  strReplaceInString = strResult
  Exit Function
lblErr:
  MsgBox Err.Description, vbExclamation, "mdlLibrary.strReplaceInString"
  strResult = ""
  Resume lblExit
End Function

Use
strName=strReplaceInString (rs(1).Value,"/","-")

- Frink

 

[kmclane]

Thanks, that works perfectly. Have a star on me. Now if I can just get the file name to the print job without having to type it in. I tried the faq solution here, but it doesn't work in A97 which I am stuck in. Hey, it's still better than extracting them from one big pdf. Plus after I do one month I can just click on a file and replace it next month.
Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg