Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    New Lounger
    Join Date
    Dec 2005
    Location
    Kitchener, Ontario, Canada
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a query that finds a specific set of records and I want to print a separate report (but same report) on each of the records found by looping through the query results until a report has been produced for each record. Does someone have some code that will do this easily?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could use something like this. It's intended to be the On Click event procedure of a command button cmdReports on a form. The code requires a reference to the Microsoft DAO 3.6 Object Library in Tools | References...

    Code:
    Private Sub cmdReports_Click()
      Dim dbs As DAO.Database
      Dim rst As DAO.Recordset
    
      Set dbs = CurrentDb
      ' Substitute the appropriate name
      Set rst = dbs.OpenRecordset("qrySomething", dbOpenForwardOnly)
    
      ' Loop through the records
      Do While Not rst.EOF
    	' Substitute the appropriate names
    	DoCmd.OpenReport ReportName:="rptMyReport", WhereCondition:="ID = " & rst!ID
    	' On to the next
    	rst.MoveNext
      Loop
    
      rst.Close
      Set rst = Nothing
      Set dbs = Nothing
    End Sub
    In the above, ID is the field that uniquely identifies the records. I have assumed that it is a number field.

  3. #3
    New Lounger
    Join Date
    Dec 2005
    Location
    Kitchener, Ontario, Canada
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Hans,

    I am using the code on the OnClick event of a command button. I'm getting Error 3061 (expects 2 parameters). Here is the line of code that it's failing on:

    Set rst = dbs.OpenRecordset("qryLCCurrentOpenClaims", dbOpenForwardOnly)

    Wasn't sure if the error was from the statement above or from the recordset query so here's the SQL statement to obtain the records we need to run reports for:

    SELECT OMIA_LCDET.OMIACLAIMNUMBER, OMIA_LCDET.OCC, Sum(OMIA_LCDET.PAID_REC) AS SumOfPAID_REC, Sum(OMIA_LCDET.RESV_AMT) AS SumOfRESV_AMT
    FROM OMIA_LCDET
    WHERE (((OMIA_LCDET.COMPANYNUMBER)=[Forms]![frmMenu]![CompanyNumber]) AND ((OMIA_LCDET.COMPDATE)<=[Forms]![frmMenu]![OccurEnd]))
    GROUP BY OMIA_LCDET.OMIACLAIMNUMBER, OMIA_LCDET.OCC
    HAVING (((Sum(OMIA_LCDET.RESV_AMT))<>0))
    ORDER BY OMIA_LCDET.OMIACLAIMNUMBER;

    When we did it for one company we just input each claim number in a field and ran the reports individually. It's now been decided that we need to run it on thousands of claims for about 60 different company, and that we should be printing the reports to PDF (trying to save some trees). Here's what we're trying to do.

    On the frmMenu a CompanyNumber is enter and cmdButton is clicked to run the series a reports for a company. We want to produce two reports for each record (claim) and put them in a specified folder based on the CompanyNumber. The CompanyNumber is used to put the reports in the correct folder.

    When we were running the report one claim at a time the report query got the CompanyNumber and ClaimNumber from the fields on frmMenu. When outputting to PDF, how would the report pickup the ClaimNumber from each record in the recordset?

    Here's what we have so far:

    Private Sub cmdLiabIndivClaim_Click()
    Dim stDocName1 As String, stOutputFileName1 As String, stFileName1 As String
    Dim stDocName2 As String, stOutputFileName2 As String, stFileName2 As String
    Dim stPath As String, stCoNo As String
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    On Error GoTo HandleErr
    stPath = "L:\CLAIMS\Stats Department Reports\Claims Reserving Project 2009-04\"

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("qryLCCurrentOpenClaims", dbOpenForwardOnly)

    ' Loop through the records
    Do While Not rst.EOF
    stCoNo = Me.CompanyNumber
    If stCoNo < 10 Then
    stCoNo = "0" & stCoNo
    End If

    stPath = stPath & "Liability\" & stCoNo & "\"
    stDocName1 = "rptLCIndivClaim-ByTrxDate"
    stFileName1 = "LC " & Me.ClaimNumber & " by TrxDate.pdf"
    'DoCmd.OpenReport stDocName1, acViewPreview (not using anymore because we want to print to PDF now)
    stOutputFileName1 = stPath & stFileName1
    DoCmd.OutputTo acOutputReport, stDocName1, acFormatPDF, stOutputFileName1

    stDocName2 = "rptLCIndivClaim-ByPymtResType"
    stFileName2 = "LC " & Me.ClaimNumber & " by PymtResType.pdf"
    'DoCmd.OpenReport stDocName2, acViewPreview (not using anymore because we want to print to PDF now)
    DoCmd.OutputTo acOutputReport, stDocName2, acFormatPDF, stOutputFileName2

    ' On to the next
    rst.MoveNext
    Loop

    rst.Close
    Set rst = Nothing
    Set dbs = Nothing

    ExitHere:
    Exit Sub

    HandleErr:
    Select Case Err.Number
    Case Else
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "cmdLiabIndivClaim_Click.Form_frmMenu"
    End Select
    End Sub

    Clear as mud?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Your query has conditions on both CompanyName and on ClaimNumber. But I thought that you wanted to loop through the companies?

  5. #5
    New Lounger
    Join Date
    Dec 2005
    Location
    Kitchener, Ontario, Canada
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='773137' date='30-Apr-2009 20:18']Your query has conditions on both CompanyName and on ClaimNumber. But I thought that you wanted to loop through the companies?[/quote]

    The SQL statement was the one I originally used when we were just doing one claim number at a time. I realize I have to change the query to be able to loop through each company and the claim numbers within company but am not sure how to do that using the output to pdf method.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    So you want to produce a separate PDF file for each claim number within each company?

    Or a PDF file for each company, listing all claim numbers for that company?

  7. #7
    New Lounger
    Join Date
    Dec 2005
    Location
    Kitchener, Ontario, Canada
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='773251' date='01-May-2009 12:18']So you want to produce a separate PDF file for each claim number within each company?

    Or a PDF file for each company, listing all claim numbers for that company?[/quote]


    A separate PDF file for each claim number within each company?

  8. #8
    New Lounger
    Join Date
    Dec 2005
    Location
    Kitchener, Ontario, Canada
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That wasn't a question, I forgot to take off the ? mark.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Sorry to keep on asking questions. Do you still need the condition

    ((OMIA_LCDET.COMPDATE)<=[Forms]![frmMenu]![OccurEnd]))

  10. #10
    New Lounger
    Join Date
    Dec 2005
    Location
    Kitchener, Ontario, Canada
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Don't be sorry... I'm just happy that you are there to help out.
    Yes I still need the date condition.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try the following:

    - Remove the criteria from the query (they will be specified in the code).
    - Change the code to the following:

    Code:
    Private Sub cmdLiabIndivClaim_Click()
      Dim stDocName1 As String, stOutputFileName1 As String, stFileName1 As String
      Dim stDocName2 As String, stOutputFileName2 As String, stFileName2 As String
      Dim stPath As String, stCoNo As String
      Dim dbs As DAO.Database
      Dim rst As DAO.Recordset
      Dim strSQL As String
      Dim strWhere As String '
    
      On Error GoTo HandleErr
    
      stPath = "L:\CLAIMS\Stats Department Reports\Claims Reserving Project 2009-04\"
    
      Set dbs = CurrentDb
      strSQL = "SELECT COMPANYNUMBER, OMIACLAIMNUMBER FROM OMIA_LCDET " & _
    	"WHERE COMPDATE<=# " & Format(Me!OccurEnd, "mm/dd/yyyy") & _
    	"# GROUP BY COMPANYNUMBER, OMIACLAIMNUMBER HAVING Sum(RESV_AMT)<>0"
      Set rst = dbs.OpenRecordset(strSQL, dbOpenForwardOnly)
    
      ' Loop through the records
      Do While Not rst.EOF
    	stCoNo = rst!COMPANYNUMBER
    	If stCoNo < 10 Then
    	  stCoNo = "0" & stCoNo
    	End If
    
    	strWhere = "COMPANYNAME=" & rst!COMPANYNUMBER & _
    	  " AND OMIACLAIMNUMBER=" & rst!OMIACLAIMNUMBER
    	stPath = stPath & "Liability\" & stCoNo & "\"
    
    	stDocName1 = "rptLCIndivClaim-ByTrxDate"
    	stFileName1 = "LC " & rst!OMIACLAIMNUMBER & " by TrxDate.pdf"
    	DoCmd.OpenReport stDocName1, acViewPreview, , strWhere
    	stOutputFileName1 = stPath & stFileName1
    	DoCmd.OutputTo acOutputReport, , acFormatPDF, stOutputFileName1
    
    	stDocName2 = "rptLCIndivClaim-ByPymtResType"
    	stFileName2 = "LC " & rst!OMIACLAIMNUMBER & " by PymtResType.pdf"
    	DoCmd.OpenReport stDocName2, acViewPreview, , strWhere
    	stOutputFileName2 = stPath & stFileName2
    	DoCmd.OutputTo acOutputReport, , acFormatPDF, stOutputFileName2
    
    	' On to the next
    	rst.MoveNext
      Loop
    
    ExitHere:
      On Error Resume Next
      rst.Close
      Set rst = Nothing
      Set dbs = Nothing
      Exit Sub
    
    HandleErr:
      Select Case Err.Number
    	Case Else
    	  MsgBox "Error " & Err.Number & ": " & Err.Description, _
    		vbCritical, "cmdLiabIndivClaim_Click.Form_frmMenu"
      End Select
    End Sub
    I have assumed that COMPANYNUMBER and OMIACLAIMNUMBER are number fields.

  12. #12
    New Lounger
    Join Date
    Dec 2005
    Location
    Kitchener, Ontario, Canada
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Hans, just getting back to working on this again. I think I'm almost there.

    We've changed it up a little bit. We're going to input the COMPANYNUMBER in a form called frmMenu and then run the code. I've changed the strSQL statement as follows:

    strSQL = "SELECT COMPANYNUMBER, OMIACLAIMNUMBER FROM OMIA_LCDET " & _
    "WHERE COMPANYNUMBER=[Forms]![frmMenu]![CompanyNumber]" & _
    "GROUP BY COMPANYNUMBER, OMIACLAIMNUMBER HAVING Sum(RESV_AMT)<>0"

    However, I get the following error:

    Error 3601: Too few parameters. Expected 1.

    I assume it has something to do with getting the company number from the form, but not sure why.

    Thanks,
    Sue

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    DAO (which we use to open the recordset) operates at a lower level than the Access interface, it doesn't "know" about forms and controls on forms. So you have to place the reference to the control outside the quotes:

    strSQL = "SELECT COMPANYNUMBER, OMIACLAIMNUMBER FROM OMIA_LCDET " & _
    "WHERE COMPANYNUMBER=" & Forms!frmMenu!CompanyNumber & _
    " GROUP BY COMPANYNUMBER, OMIACLAIMNUMBER HAVING Sum(RESV_AMT)<>0"

  14. #14
    New Lounger
    Join Date
    Dec 2005
    Location
    Kitchener, Ontario, Canada
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I still need to add the OCC date criteria to the WHERE clause. This is what I added but I get a compile error on the # sign after the OCC<=

    strSQL = "SELECT COMPANYNUMBER, OMIACLAIMNUMBER FROM OMIA_LCDET " & _
    "WHERE COMPANYNUMBER=" & Forms!frmMenu!CompanyNumber AND OCC<=# " & Format(Me!OccurEnd, "mm/dd/yyyy") & _
    "# GROUP BY COMPANYNUMBER, OMIACLAIMNUMBER HAVING Sum(RESV_AMT)<>0"

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try

    strSQL = "SELECT COMPANYNUMBER, OMIACLAIMNUMBER FROM OMIA_LCDET " & _
    "WHERE COMPANYNUMBER=" & Forms!frmMenu!CompanyNumber & " AND OCC<=# " & Format(Me!OccurEnd, "mm/dd/yyyy") & _
    "# GROUP BY COMPANYNUMBER, OMIACLAIMNUMBER HAVING Sum(RESV_AMT)<>0"

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •