Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    164
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lost in Macro, Module land (2000)

    This should not be very difficult, but for some strange reason, I can't figure it out( I have an Access 2000 DB with several reports. Well, I'd like to somehow output those reports into excel and place them in a specific folder on the server. I was able to accomplish this without error with the first report. I did the following with the boards help "thanks again". I did a run-code on the macro action, and referred to a mosule and the following code...

    Function OutputToBudgetSummary()
    DoCmd.OutputTo acOutputReport, "Budget Summary Report", acFormatXLS, "D:ReportsBudget_Summary" & Format(Date, "yyyymmdd") & ".xls"
    End Function

    It worked perfectly. I'm currently using a batch file that reads...
    "crogram filesmicrosoft officeofficemsaccess.exe" D:NickBudget.mdb /X Budget Summary
    in which I have set to a schedule via the Windows NT task scheduler. Now I'd like to do the same thing with the other reports. Is there something I can create to combine all three reports, etc. For example, I'd like to have three different reports show up in the directory when the command is given to process. I'm clueless and any help WOULD be much appreciated. Thanks beforehand!!!

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lost in Macro, Module land (2000)

    The simplest way is to add two lines to your function:

    Function OutputToBudgetSummary()
    DoCmd.OutputTo acOutputReport, "Budget Summary Report", acFormatXLS, "D:ReportsBudget_Summary" & Format(Date, "yyyymmdd") & ".xls"
    DoCmd.outputTo .... for the second report
    DoCmd.outputTo .... for the third report
    End Function

    If you don't want to change your function, write two new functions each one for the second and third report and add the RunCode for those two reports in the Macro Budget Summary
    Francois

  3. #3
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    164
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lost in Macro, Module land (2000)

    Thanks for the reply. I have tried that, but I get a dard error 3343, Unrecognized database format D:NickBudget.mdb? Any ideas?

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lost in Macro, Module land (2000)

    Francois

  5. #5
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    164
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lost in Macro, Module land (2000)

    Here is what I now have...

    One Module named "Module1" as follows....

    Function OutputToReport()
    DoCmd.OutputTo acOutputReport, "Budget Summary Report", acFormatXLS, "D:ReportsBudget_Summary" & Format(Date, "yyyymmdd") & ".xls"
    DoCmd.OutputTo acOutputReport, "PO Tracker", acFormatXLS, "D:ReportsPO_Tracker" & Format(Date, "yyyymmdd") & ".xls"
    DoCmd.OutputTo acOutputReport, "BCER-PO Summary", acFormatXLS, "D:ReportsBCER-PO_Summary" & Format(Date, "yyyymmdd") & ".xls"
    End Function

    I then have four macros with the following names, "BCER-PO Summary, Budget Summary,PO Tracker and Report". The first three macros listed have only a RunCode action referring to the Function Name - OutputToReport()

    The Report macro is running three RunMacro actions referring to each macro and then a Quit command to close Access.

    Then of course, I'm running the following batch file...
    "crogram filesmicrosoft officeofficemsaccess.exe" D:NickBudget.mdb /X Report

    IT's running the first report fine, but giving me the 3343 run time error then.

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lost in Macro, Module land (2000)

    The function is running the three reports. So you don't need 4 macros. Just one running the function OutputToReport().
    Do you have some code in the on open event of the reports?
    If you press the F5 key when you are in the design view of the module, are the reports exprted?
    If you run the macro, are the reports exported?
    Francois

  7. #7
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    164
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lost in Macro, Module land (2000)

    I ran both tests that you mentioned, and the only report that was executed was the first report listed in the Function, the Budget Summary Report.

  8. #8
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    164
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lost in Macro, Module land (2000)

    Updated code...

    One Macro named "Report" with a RunCode action and the following Function Name - OutputToReport()
    One Module named "Module1" vomprised of the following code...

    Function OutputToReport()
    DoCmd.OutputTo acOutputReport, "Budget Summary Report", acFormatXLS, "D:ReportsBudget_Summary" & Format(Date, "yyyymmdd") & ".xls"
    DoCmd.OutputTo acOutputReport, "PO Tracker", acFormatXLS, "D:ReportsPO_Tracker" & Format(Date, "yyyymmdd") & ".xls"
    DoCmd.OutputTo acOutputReport, "BCER-PO Summary", acFormatXLS, "D:ReportsBCER-PO_Summary" & Format(Date, "yyyymmdd") & ".xls"
    End Function

    And again, the only report that actually works is the Budget Summery Report, and then I get the error.

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lost in Macro, Module land (2000)

    I'm out of ideas.
    If you want, you can attach the database so I can have a look at it.
    If it's to big you can send it to my e-mail address, it's in my profile.
    Francois

  10. #10
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lost in Macro, Module land (2000)

    Just a thought, Have you tried altering the order in which the reports are called to see if it is the report or the second call which is causing the problem?

    Peter

  11. #11
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    164
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lost in Macro, Module land (2000)

    Ihave and it's definitely the second call.

  12. #12
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lost in Macro, Module land (2000)

    Another 2c thought:
    Have you an Application.quit in the on close event of the first report ?
    Francois

  13. #13
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    164
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lost in Macro, Module land (2000)

    I've got it working fine on my development machine now, but when I try it on the server, I get the unrecognizable database format error. Thanks anyway everyone.

Posting Permissions

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