Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2017
    Posts
    2

    Unanswered: Automating Report Creation and Saving

    Hello all,

    I have 2 reports that I would like to some how have automatically generated and save as a PDF.

    1st report is filtered by manager and I would like to have access automatically create a PDF report for each manager in the filter list and save by their name in a manager folder.

    2nd report is filtered by manager and then a second filter by the groups they manage. Would like to automatically have access generate the report and save by manager in a group folder.

    Is this possible? I do not know how to write VBA.

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    630
    Provided Answers: 35
    have a listbox with all the managers in it, lstMgrs.
    have a combo box with the report to print, cboRpt.
    combo box of groups. Groups change when manager changes.
    choose the report
    click button to cycle thru all managers to print

    Code:
    sub btnPrint_click()
    Dim i As Integer, dim g as integer
    dim vMgr, vDir, vFile, vGrp
    
    For i = 0 To lstMgrs.ListCount - 1
       vMgr = lstMgrs.ItemData(i)         'get the item in list
       lstMgrs = vMgr                    'set listbox to next manager 
       cboGroups.requery
    
         vDir = "c:\folder\" & vMgr      'make manager folder
         MakeDir vDir
    
         for g = 0 to cboGrps.listcount -1
             vGrp = cboGrps.itemdata(g)
             cboGrps = vGrp
    
             vDir = vDir & "\" &  vGrp      'make group folder
             MakeDir vDir
    
             vFile = vDir & "\" & cboRpt & Format(date,"yymmdd") & ".pdf"
    
             docmd.OutputTo  acOutputReport ,cboRpt ,acFormatPdf,vFile
         next
    Next
    end sub
    
    
    '----------------
    Public Sub MakeDir(ByVal pvDir)
    '----------------
    Dim fso
    On Error Resume Next
    Set fso = CreateObject("Scripting.FileSystemObject")
    If Not fso.FolderExists(pvDir) Then fso.CreateFolder pvDir     'MkDir pvDir
    
    Set fso = Nothing
    End Sub

  3. #3
    Join Date
    Aug 2017
    Posts
    2
    Thank you for the taking the time to read and respond

    my database noobness is going to show through here. where do I place the list box and combo box, on the report itself?

    Also, I think I was too concise in my request and explanation.

    I have 2 separate reports.

    Report 1 lists all the users and their accesses filtered by manager.
    Save by manager name in to Manager folder

    Report 2 lists all the users and the group(s) they are members of and the resources those groups have access to filtered by manager.
    Save by manager name in to Groups folder

Posting Permissions

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