Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2003

    Question Unanswered: Report automation

    Is there a way to set up a scheduler in Access to schedule a report to run say every Friday afternoon and email to the users? I am not a programmer but seems like there should be a way to do this.


  2. #2
    Join Date
    Nov 2003
    Well darby..…I’m going to brave this post simply because no one is biting on it.

    Your suspicions are correct. There is a way. There are lots of ways. The trouble is, you will need to know something about VB or VBA programming otherwise you are going to be completely lost with what you will be looking at….the required code to do it. Unless one of these fine people that view this forum is willing to attach a DB with the sample code you need to accomplish the task, your post will turn into a small book.

    A front end unit (in any programming language) can be written to even utilize the MS Windows Scheduler or Outlook and through Windows Automation email your reports. Or it could be done directly through Access providing your DB it is up and running all the time. There are a lot of different variables to play with here.

    Just as a simple example this would be how you could Output Multiple Reports to One Word File Automatically.

    Let’s say you have a list of reports you have to send to a customer or a specific individual. This has to be done every so often and takes too long. As well as that the reports need to be in word format, which takes even more time, and there are 50 of them.

    Create a new form. On this form put a list box called LIST0. Either fill the list box via code (see List all Reports example) or from a table via a query. Create a button called SENDIT. Go into the code for the form by clicking the code button on the toolbar and paste the following code.

    Private Sub  SENDIT_Click()
       On Error GoTo ERRORHANDLER  
       Dim varItem As  Variant
       If List0.ItemsSelected.Count>0 Then 
          Dim AppWord As New Word.Application 
          Dim DocWrd As Word.Document 
          Dim i As Integer
          Dim Progress as String
          Dim EventTitle as String
          AppWord.Visible = True
          Set DocWrd= AppWord.Documents.Add 
          DocWrd.PageSetup.TopMargin = 36    
          DocWrd.PageSetup.BottomMargin = 36    
          DocWrd.PageSetup.LeftMargin = 36 
          DocWrd.PageSetup.RightMargin = 18
          i = 0 
          For Each varItem In  List0.ItemsSelected
             i = i  +  1
             Progress = "Processing... " & List0.ItemData(varItem)  
             DoCmd.OutputTo acOutputReport, List0.Column(0, varItem),  acFormatRTF, "c:\temp\" & List0.ItemData(varItem) & ".rtf", False 
             AppWord.Selection.InsertFile "c:\temp\" & List0.ItemData(varItem) & ".rtf", "", False, False, False 
             If i < List0.ItemsSelected.Count Then 
                AppWord.Selection.InsertBreak wdSectionBreakNextPage
             End If 
          Progress = "Generating Email"
          DocWrd.SaveAs "c:\temp\"  & EventTitle & ".doc", wdFormatDocument
          DocWrd.BuiltInDocumentProperties("Title").Value ="Set the title of  the combined reports - " & Date
          AppWord.Options.SendMailAttach = True
          Set DocWrd = Nothing
          Set AppWord = Nothing
       End If
       Progress = ""
       Exit Sub
        Progress = ""
        If MsgBox("Do you want to start over?", vbCritical + vbYesNo) = vbYes Then
            DocWrd.Close wdDoNotSaveChanges
            Exit Sub
        End If
    End Sub
    Save the form and run it. Select the reports you want to send in the list and click the button. All the reports will be run and joined into one nice long word document. Then bring up your mail client with the document attached.

    List all Reports example

    This piece of code shows you how to fill a combo box with a list of reports within the current database.

    Create a form and put an unbound combobox on the from and call it ReportList. Then put the following code in the form open event.

    Private Sub Form_Open(Cancel As Integer)
        Dim Dbs As Database
        Dim Rpc As Container
        Dim Rpt As Document
        Set Dbs = CurrentDb
        Set Rpc = Dbs.Containers!Reports
        For Each Rpt In Rpc.Documents
            ReportList.RowSource = ReportList.RowSource & Rpt.Name & ";"
        Set Dbs = Nothing
        Set Rpc = Nothing
        Set Rpt = Nothing
    End Sub
    After the form loads you'll have a combobox with a list of the reports available. Once a user has selected a report you could have a button which opens the report or prints it maybe. To find out the report name just use Me.[ReportList].Value

    Alternatively if you want to be a little smarter. Just put a listbox on a form and set it's rowsource to :-

    SELECT Name FROM msysobjects WHERE (((msysobjects.Type)=-32764));
    If you don’t know programming, you can now quickly see how cryptic things can be. In any case, you can rest assured, nothing is impossible. Besides…now that I’ve posted this, you can bet someone has something better to offer as a post.

    Sorry and good luck.

  3. #3
    Join Date
    Oct 2003
    Provided Answers: 1
    Also to SELECT all Reports in the current DB use this SQL

    SELECT MSysObjects.Name, MSysObjects.Flags, MSysObjects.Type
    &nbsp;&nbsp;FROM MSysObjects
    &nbsp;&nbsp;WHERE (((MSysObjects.Type)=-32764))
    &nbsp;&nbsp;ORDER BY MSysObjects.Name;

  4. #4
    Join Date
    Dec 2003

    Re: Report automation

    I achieve this by using the scheduler on out W2K server. I create a version of the database that runs a macro that uses the sendobject function and then closes the database. You just need to ensure that you have a configure email client on the server. Just set the scheduler to run the mdb file at whatever times you want.

    You can get clever and check for the existence of a chk file on the machin, or look up the username to decide whether to run the sendobject macro in the autoexec. That way you only have to maintain one version of the database.

  5. #5
    Join Date
    Dec 2003
    Thanks for all the help. Hope you all have a great holiday.

  6. #6
    Join Date
    Dec 2003
    Belfast, UK

    I was trying to do the same thing, I managed to find a database someone create with a clock that runs in real time, what I did was to add a feature to the on timer event that runs the reports or another program etc. might be worth a look. I'm sure this can be tweaked to suit any application.

    I have attached the clock file. You may as well have a look.
    Attached Files Attached Files

  7. #7
    Join Date
    Jan 2004
    Toronto, Canada
    If you want to convert the reports to PDFs without having to buy any addons, i can show you a way;
    its through VB code;

  8. #8
    Join Date
    Mar 2004
    Cambridge UK


    Originally posted by Jerrie
    If you want to convert the reports to PDFs without having to buy any addons, i can show you a way;
    its through VB code;
    I would be interested in how to do this please.


Posting Permissions

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