Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Nov 2004
    Location
    Georgia, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    multiple rpt to different departments (Access 2002(10.3409.3501) SP-1)

    I would think this should be simple, but I have yet to find the answer. I have a department file (dept numbers and descriptions). I have a report that I want to run for all the departments and create those reports as PDF files "separately", without creating 50 identical queries with each one containing the department numbers. I also don't want to prompt for each department. Is there a way my report can open run the first department, close, and loop through to produce the 50+ reports to distribute separately?

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: multiple rpt to different departments (Access 2002(10.3409.3501) SP-1)

    Run the report from a form in a Do loop based upon department. Also in the OnOpen event of the report change the Caption based upon the Department that would be passed in via the OpenArgs argument on the DoCmd.OpenReport command.

  3. #3
    New Lounger
    Join Date
    Nov 2004
    Location
    Georgia, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: multiple rpt to different departments (Access 2002(10.3409.3501) SP-1)

    Well Pat.. That sounds simple, but you've lost me. I have done little with "forms".. most of my apps I create queries that make tables, update queries, cross tabs etc.. once I have a finished query with the data I want to print on a report I use that last query. I also write many function that my queries may call. Once all is in place, and ready for production, I normally set up a switchboard, the operators bring up the app, many times I've made a macro named "autoexec", so the tables are all created from the macros, and all they have to do is press the "reports" menu item on the switchboard, and it will display the reports they need to run, in which case they choose rpt 001, I use the openreport property of the switchboard to just run the report. So.. from what you told me, I'm not really sure I understand what your are trying to tell me.. <img src=/S/angel.gif border=0 alt=angel width=15 height=21>

  4. #4
    New Lounger
    Join Date
    Nov 2004
    Location
    Georgia, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: multiple rpt to different departments (Access 2002(10.3409.3501) SP-1)

    I'll give it a shot Pat, and let you know.. thanks..

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: multiple rpt to different departments (Access 2002(10.3409.3501) SP-1)

    <P ID="edit" class=small>(Edited by patt on 18-Nov-04 09:19. Cleanup code a little)</P>Firstly create a form with a button on it to run the report. I presume you have defined the report already. Behind this button you should have code that looks like:
    Private Sub btnReportPreview()
    Dim sDocName as String, sWh as String, sOpArgs
    Dim dbs as DAO.Database, rs as DAO.RecordSet
    Set dbs = CurrentDB
    Set rs = dbs.OpenRecordset("your department tablename")
    Do While Not rs.Eof
    sWh = "DepartmentNo=" & rs!DepartmentNo
    sOpArgs = rs!DepartmentNo
    sDocName = "your report name"
    DoCmd.OpenReport sDocName, acViewNormal, , sWh, , sOpArgs
    rs.MoveNext
    Loop
    Set rs = Nothing
    Set dbs = Nothing
    End Sub


    In the OnOpen event of the Report put the following (this names the output file):

    Private Sub Report_Open(Cancel As Integer)
    Me.Caption = "Department" & Me.OpenArgs & " Report"
    End Sub

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: multiple rpt to different departments (Access 2002(10.3409.3501) SP-1)

    I forgot to mention that you will need a PDF writer, Adobe Acrobat, PDF995, PDFFactory are but a few.

  7. #7
    New Lounger
    Join Date
    Nov 2004
    Location
    Georgia, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: multiple rpt to different departments (Access 2002(10.3409.3501) SP-1)

    Hey Pat!!

    Whew.. as it turns out, you helped a lot, and pointed me in the right direction, which I greatly appreciate! This being said, with my expression builder, I couldn't get a lot of your exact code to work.. (I think it may have something to do with ADO, DAO, ABC (grin)) ect.. actually I really don't understand all the differences, but after tons of reading today I pretty much got it to work with the following code:

    Private Sub Command0_Click()
    On Error GoTo Err_Command0_Click

    Dim sDocName As String, sWhere As String, sOpArgs As String
    Dim sWrksch As String
    Dim sWrksch_b As String

    Dim dbObj As ADODB.Connection
    Dim rs As ADODB.Recordset

    Set dbObj = CurrentProject.Connection
    Set rs = New ADODB.Recordset

    rs.Open "Select * from T_MT_SCHOOL_DESC", dbObj, , adLockReadOnly

    Do While Not rs.EOF

    sOpArgs = rs.Fields("sch_nbr")
    sWrksch_b = """" & sOpArgs & """"

    sWhere = "[Sch_nbr]=" & sWrksch_b

    sDocName = "RPT1_FAM_WTH_DWELL_0_1_2"

    DoCmd.OpenReport sDocName, acViewNormal, , sWhere, , sOpArgs

    rs.MoveNext

    Loop

    Set rs = Nothing
    Set dbObj = Nothing


    Exit_Command0_Click:
    Exit Sub

    Err_Command0_Click:
    MsgBox Err.Description
    Resume Exit_Command0_Click

    End Sub

    Prints each report out separately pretty as can be, BUT... then I changed the caption property of the report just like you suggested, and switched my
    default printer to Adobe PDF, but from what I can tell right now, Adobe gets the caption before the OnOpen Event takes place, so it doesn't sent Adobe the separate file names.. I know they work (the filenames) because I msgboxed the text, but like I said adobe already has the original caption before the OnOpen fires.. I'm still searching, if I find a work around I'll share it..

    Again .. Thanks for your friendly help!!

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: multiple rpt to different departments (Access 2002(10.3409.3501) SP-1)

    In the OnClose event of the report try the following:


    Private Sub Report_Close()
    Dim sPF
    sPF = DLookup("Path4PDFfiles", "tbl Control")
    Name sPF & "Department" & Me.OpenArgs & " Report" As sPF & Department" & Me.OpenArgs & " Report"
    End Sub

    I keep a path of where the PDF files are created in table [tbl Control] in a field called Path4PDFfiles. So change the names to suit yourself.

Posting Permissions

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