Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Aug 2013
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Newbie (that's me) needs help with code...

    Hello All,

    I am just getting into VBA code and admittedly may be in over my head a touch. I feel like I'm close (but likely I'm wrong). Be gentle.

    I am trying to automate the running of some reports. Basically I want to pass some values to a report, then rip the file to SharePoint. Then do it again and again for various departments. The below code just runs reports for the 3 departments. Eventually there will be 73.

    I pieced this code together using some old code I hade plus some additional code I found online about arrays. They are new to me. I am sure there are better ways of doing this (like having the departments and folder names in a table and reading the values from there) but I am in a time crunch, and frnakly I just dont know how to do it.

    Here's my code so far:

    Code:
    Function mcrPushReportToSPbyDept()
    
    Dim tblDateStamp
    Dim rs As DAO.Recordset
    Dim lngPosition
    
    Dim strDept(0 To 2) As Integer
    Dim strFolder(0 To 2) As String
    
    '3 sample departments
    strDept(0) = "250"
    strFolder(0) = "Audiology - 250"
    
    strDept(1) = "28"
    strFolder(1) = "Behavioral Health - 28"
    
    strDept(2) = "29"
    strFolder(2) = "Cancer Center - 29"
    
    Set rs = CurrentDb.OpenRecordset("tblDateStamp")
    
    'loop through the array values
        For lngPosition = LBound(strDept) To UBound(strDept)
    
        MsgBox strDept(lngPosition) 'this tells me what value will be passed to the below report - it says 250. Correct!
        MsgBox strFolder(lngPosition) ' this tells me what value will be passed to the below report - it says Audiology - 250. Correct!
    
        'Detail Report
        ' The below line runs a report based on a query qry_Summary_filtered by department_SP where department is the value of strDept(lngPosition) which is 250  
        DoCmd.OpenReport "Summary by Department", acViewPreview, "", "[qry_Summary_filtered by department_SP]![Department #]=strDept(lngPosition)", acNormal  
    
         'The below line then takes the report and puts it on SharePoint    
         DoCmd.OutputTo acOutputReport, "Summary by Department", acFormatPDF, "\\inside.mc.rochester.edu\sites\ESD\QA\Shared Documents\" & strFolder(lngPosition) & Format(rs.Fields(0), "yyyy-mm") & " Details.pdf", False
        DoCmd.Close
        
        'Do it again
        Next lngPosition
            
    mcrPushReportToSPbyDept_Exit:
        Exit Function
    
    mcrPushReportToSPbyDept_Err:
        MsgBox Error$
        Resume mcrPushReportToSPbyDept_Exit
    
    End Function
    The line in red is where I am having a problem. I get the following error:

    "Undefined function 'strDept' in expression".

    Ok What am I doing wrong? (laymans terms please

    Also how does the rest of the code look? Is it OK? Or a mess? Thanks all for your help and I look forward to your replies.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Naynlou,

    Welcome to the lounge as a new poster!

    I believe your problem is the spaces in your folder names. You can correct this by adding quotes as follows:
    Code:
        DoCmd.OpenReport "Summary by Department", acViewPreview, "", "[qry_Summary_filtered by department_SP]![Department #]=" & chr(34) & strDept(lngPosition) & chr(34), acNormal
    Please note this is untested "AIR" code! You may also need to address this situation in the DoCmd.OutputTo command line as well.


    HTH
    Last edited by RetiredGeek; 2013-08-09 at 11:34.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    naynlou (2013-08-09)

  4. #3
    New Lounger
    Join Date
    Aug 2013
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Yes, now it's passing the value properly! Thank you so much! I wasn't aware of the spaces being an issue.

    And you're right. Now its bombing on the OutputTo command.

    I tried modifing to this but it still says Runtime error 2501. The OutputTo command was cancelled.

    Code:
    DoCmd.OutputTo acOutputReport, "Summary by Department_auto", acFormatPDF, "\\inside.mc.rochester.edu\sites\ESD\QA\Shared Documents\" & Chr(34) & strFolder(lngPosition) & Chr(34) & _
        Format(rs.Fields(0), "yyyy-mm") & " Details.pdf", False
    Is there a syntax error in there I am missing?

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Naynlou,

    The whole filespec needs to wind up with quotes around it after it is assembled. The following should do the trick:
    Code:
    DoCmd.OutputTo acOutputReport, "Summary by Department_auto", acFormatPDF, Chr(34) & _
          "\\inside.mc.rochester.edu\sites\ESD\QA\Shared Documents\" &  strFolder(lngPosition)  & _
           Format(rs.Fields(0), "yyyy-mm") & " Details.pdf" & Chr(34), False
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #5
    New Lounger
    Join Date
    Aug 2013
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Ah ok I see what you did there. I just copy pasted your modified code and it still get's the same OutputTo error. Hmmm. Does that mean something else might be up in the expression itself?

    The strFolder(lngPosition) when moused over does contain the folder value it should, and it's a real folder on our SharePoint site. I don't see any typos in the path or object names. And the formatting of the appended yyyy-mm date value has worked fine for me in the past.

    What else could it be?

    I truly appreciate your help.
    Last edited by naynlou; 2013-08-09 at 13:53.

  7. #6
    New Lounger
    Join Date
    Aug 2013
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Interesting! I removed the leading space from ..." Details.pdf" & Chr(34), False and made it "Details.pdf" & Chr(34), False

    Now it works! Is there a way to accomodate the leading space? If not I may just use a dash.

  8. #7
    New Lounger
    Join Date
    Aug 2013
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Got it. I just updated the code to add a space through VB.

    ... & Chr(32) & "Details.pdf" & Chr(34), False

    Works beautifully now. Thanks so much for you help!

  9. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Naynlou,

    Glad you got it sorted! Sorry, I was offline for a while Friday is "Backup Image Day".
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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