Open Report Filter Problem

[Keith]

I am creating a function to produce and email a report as a PDF file. The filter in the DoCmd.OpenReport does not work. The function works in that the files are saved correctly but each one contains all the records in qrySubs1 not those just relating to a branch. I would like some help please. The reports are produced correctly if I manually filter the query.

The function I have is;

Public Function SubsDue()
    Dim rs As Recordset
    Dim db As DAO.Database
    Dim Id As Integer
    Dim strBranch, strfileName, strYear As String

    On Error GoTo SubsDue_Error
    strYear = Year(Now()) + 1

    Set db = CurrentDb
    Set rs = CurrentDb.OpenRecordset("qryBranchActive")
    rs.MoveFirst
    Do While Not rs.EOF
        Id = rs!BranchID
        Debug.Print Id
        strBranch = rs!Branch_Name
        Debug.Print strBranch
        
        strfileName = "C:\submariners\Subs_Due\" & strYear & "\" & strBranch & ".pdf"

        DoCmd.OpenReport "rptSubsDue", acViewPreview, "qrySubs1!BranchID =" & Id

        DoCmd.OutputTo acOutputReport, "rptSubsDue", acFormatPDF, strfileName
        'Code to Email report goes here

        rs.MoveNext
    Loop

    On Error GoTo 0
    Exit Function

SubsDue_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure SubsDue of Module mdlSubsDue"
End Function

The SQL for qry1 is;

SELECT tblBranch.BranchID, tblBranch.Branch_Name, tblMembers.MemberID, [LastName] & " " & [Initials] & "  (" & [FirstName] & ")" AS [Member Name], IIf(IsNull([tblMembers].[NatSubsBranch]),"",IIf([tblMembers].[NatSubsBranch]=1,"SO to National",IIf([tblMembers].[NatSubsBranch]=[tblMemberBranch].[BranchID],"",DLookUp("Branch_Name","tblBranch","BranchID =" & [tblMembers].[NatSubsBranch])))) AS [Nat Subs Pd at Branch], tblMembers.Overseas, Format(IIf([Nat Subs Pd at Branch]<>" ",0,IIf([Overseas]=True,2,7.5)),"Currency") AS [Subs Due]
FROM tblBranch INNER JOIN (tblMembers INNER JOIN tblMemberBranch ON tblMembers.MemberID = tblMemberBranch.MemberID) ON tblBranch.BranchID = tblMemberBranch.BranchID
WHERE (((tblMembers.Status)=1 Or (tblMembers.Status)=2 Or (tblMembers.Status)=4))
ORDER BY tblMemberBranch.BranchID;

 

[pbaldy]

Try

DoCmd.OpenReport "rptSubsDue", acViewPreview, , "qrySubs1!BranchID =" & Id

 

[rzw0wr]

Just a tip, Close your Recordset and the db you opened when finished.


Dale

 

[Keith]

Thanks for the tip Dale.

Thanks Paul,
I have inserted the missing comma. The only difference now is instead of every record from qrySubs1 in each file only records from Branch 1 are in each file.

 

[pbaldy]

Wasn't that the goal?

the files are saved correctly but each one contains all the records in qrySubs1 not those just relating to a branch.

 

[pbaldy]

Oh wait, you're saying they all have the same single branch? Make sure you close the report after outputting it.

 

[Keith]

Solved. I changed Open Report to;

DoCmd.OpenReport "rptSubsDue", acViewReport, , "qrySubs1!Branch_Name = " & Chr$(34) & strBranch & Chr$(34), acHidden

It works now

 

[pbaldy]

It works without closing it? In any case, glad you got it sorted out.

 

[Keith]

For some reason it didn't like being filtered on BranchId I changed it to filter by Branch Name and it worked a treat. Busy now coding the send email bit.