The Where command parameter

[SallyJenkins]

I have created a report that uses a table as its record source. I need to create individual pdf files for each fund in this table. There may be 2 to many pages of activity for each fund. Prior to opening the report I query the table to get distinct funid ids. I step through the query results and open the report, pass a parameter(fundid) using the Where clause (MS Access 2010 - mdb). I am expecting to see a single report for each fundid, but am seeing the entire report each loop through the list of fundid's. Is there someting on the report specification side I need to do for it to accept the parameter? BTW, the Fundid is text.

Here's the code:

Dim Dbs As DAO.Database
Set Dbs = CurrentDb
Dim rsEndStatSumActs As DAO.Recordset
strSQL = "SELECT DISTINCT FundID FROM EndStatSumActs"
Set rsEndStatSumActs = Dbs.OpenRecordset(strSQL)
    'Debug.Print "Records " & rsToAdd.RecordCount
If rsEndStatSumActs.RecordCount = 0 Then
    ' We have a problem
Else
    Do While Not rsEndStatSumActs.EOF
        strFundID = rsEndStatSumActs![FundId]
        stCriteria = "[FundID] = '" & rsEndStatSumActs![FundId] & "'"
        DoCmd.OpenReport "EndStatSumActs", acViewPreview, , stCriteria
        rsEndStatSumActs.MoveNext
    Loop
End If

 

[boblarson]

You are using acViewPreview instead of acViewNormal which would print them. If you use acViewPreview, only one will show because it is the same report and it would only be able to be opened one time to view unless you closed it.

 

[Pat Hartman]

The following code saves the files as PDF's. To save as a PDF, you need to use the OutputTo method and it does not support a Where argument. This code is run from a button on a form. The loop of unique treaties saves each treaty to a hidden form field. The RecordSource of the report uses criteria that references the hidden form field.

Private Sub cmdStatement_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim qd As DAO.QueryDef
    Dim strWhere As String
    Dim strFileName As String
    On Error GoTo ErrProc
    Set db = CurrentDb()
    Set qd = db.QueryDefs!qClaimStatementHeaderController
        qd.Parameters!EnterBatchID = Me.txtBatchID
    Set rs = qd.OpenRecordset
    Do Until rs.EOF
        Me.txtTrtyNum = rs!TrtyNum  'Report references form for argument
        strWhere = "BatchID = " & Me.txtBatchID & " AND TrtyNum = '" & Me.txtTrtyNum & "'"
        strFileName = Forms!frmLogin!txtDefaultDirectory & "\" & "Clm" & Me.txtTrtyNum
        strFileName = strFileName & "_" & Format(Date, "yyyymmdd") & "_" & Me.txtBatchID & ".pdf"
 
        DoCmd.OutputTo acOutputReport, "rptClaimStatement", acFormatPDF, strFileName, True
        rs.MoveNext
    Loop
    rs.Close
    db.Close
ExitProc:
    Exit Sub
ErrProc:
    Select Case Err.Number
        Case 2501   'report cancelled
            Resume Next
        Case Else
            MsgBox Err.Number & "--" & Err.Description
            Resume ErrProc
    End Select
End Sub

 

[SallyJenkins]

I'm doing my happy dance!

I incorporated both suggestions into my code and it is working as designed.

Many thanks.

Do While Not rsEndStatSumActs.EOF
        strFundID = rsEndStatSumActs![FundId]
        strFilename = strPath & strFundID + "1101.pdf"
        stCriteria = "[FundID] = '" & rsEndStatSumActs![FundId] & "'"
        DoCmd.OpenReport "EndStatSumActs", acViewPreview, , stCriteria
        DoCmd.OutputTo acOutputReport, "EndStatSumActs", acFormatPDF, strFilename
        DoCmd.Close acReport, "EndStatSumActs"
        rsEndStatSumActs.MoveNext
    Loop