Results 1 to 8 of 8
  1. #1
    d3ell is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2014
    Location
    doncaster UK
    Posts
    19

    Saving a pdf report from database to the current database directory folder

    Hi Hopefully can get some assistance on this if possible!

    We recently migtrated all our data on to dropbox from a local server. All is great except a database that i created several years ago uses vba code provided and sourced on here and via google. I have no knowledge of coding so i'm at a bit of a loss of how to change this now so that it will work again.

    In short when we press a button on a form within our database, it saves a .pdf copy of the report into a file in our file system, external to the database. which we then use to send onto our customer.

    I have attached the current code below..

    Private Sub Command166_Click()
    Dim FileName As String
    Dim FilePath As String
    FileName = "Plot " & Me.Customer_Plot_No & " " & Me.Development & " Work Sheet"
    FilePath = "C:\Users\angie\Dropbox (Bell)\Bell Team Folder\BELL ELECTRICAL\CUSTOMER CARE\Database\Worksheets" & FileName & ".pdf"


    DoCmd.OutputTo acOutputReport, "Employee Worksheet", acFormatPDF, FilePath
    MsgBox "Report Saved Successfully", vbInformation, "save confirmed"
    End Sub

    This still works on one computer where the dropbox directory is... ( C:\Users\angie\Dropbox...)

    However when we login from a 2nd PC where the dropbox dir is different and we click the same button, we get the following error Run-time error'2501': The outputTo action was cancelled - When you go to Debug the following line is highlighted

    DoCmd.OutputTo acOutputReport, "Employee Worksheet", acFormatPDF, FilePath

    I assume this is because that file directory doesn't exist on the 2nd PC.

    Is there a way to change the filepath to whatever the filepath of the database is? - I have had a bit of a search and I have tried the following with no joy..

    FilePath = CurDir & "FileName & ".pdf"

    Any thoughts??

    Thanks

  2. #2
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,715
    You can get the current database file path from

    Code:
    sFilePath = CurrentProject.Path & "\" & sFileName & ".pdf"
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,955
    so you are saying your issue is this

    FilePath = "C:\Users\angie\Dropbox (Bell)\…..
    suggest try

    FilePath = "C:\Users\" & environ("username") & "\Dropbox (Bell)\…..

    edit: for some reason, the backslash in the quotes, although showing correctly in the edit window, displays as a pipe char when posted

  4. #4
    d3ell is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2014
    Location
    doncaster UK
    Posts
    19
    Hi Thanks for your prompt response... I have changed the block of code to the following and still not saving correctly.. do I need to change any other bits?

    Private Sub Command125_Click()
    Dim FileName As String
    Dim FilePath As String
    FileName = "Plot " & Me.Customer_Plot_No & " " & Me.Development & " Remedial works report"
    sFilePath = CurrentProject.Path & "" & sFileName & ".pdf"
    DoCmd.OutputTo acOutputReport, "Report1", acFormatPDF, FilePath
    MsgBox "Report Saved Successfully", vbInformation, "save confirmed"
    End Sub

  5. #5
    d3ell is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2014
    Location
    doncaster UK
    Posts
    19
    Quote Originally Posted by Ajax View Post
    so you are saying your issue is this



    suggest try

    FilePath = "C:\Users\" & environ("username") & "\Dropbox (Bell)\..

    edit: for some reason, the backslash in the quotes, although showing correctly in the edit window, displays as a pipe char when posted


    This looks like it would work, however i think the dropbox is saved in slightly different locations on different PC's. If there is no other workaround i may move all the dropbox locations on the 6 PC's that access this. We have a lot of data though so hopefully this would be a last resort!

    Thank you very much for your repsonse though!

  6. #6
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,955
    all depends on whether other users need to see the report, if they don't it doesn't matter, locating on each users C drive should not be a problem.

    And you are missing a \ from Minty's suggestion

  7. #7
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,715
    Just as an added bit of useful code for filepaths, I use this (blatantly stolen from somewhere on the interwebs)
    Just store it in a Module, if you don't have any modules call it modFunctions and store other useful snippets in there.

    Code:
    Public Function TrailingSlash(varIn As Variant) As String
        If Len(varIn) > 0& Then
            If Right(varIn, 1&) = "\" Then
                TrailingSlash = varIn
            Else
                TrailingSlash = varIn & "\"
            End If
        End If
    End Function
    Simply use it around any filepath and you know that it has the trailing slash added as required.

    Your code in completeness should be something like;
    Code:
    Private Sub Command125_Click()
        
        Dim sFileName As String
        Dim sFilePath As String
        
        sFileName = "Plot_" & Me.Customer_Plot_No & "_" & Me.Development & "_Remedial_works_report"
        sFilePath = CurrentProject.Path & "\" & sFileName & ".pdf"
    
        Debug.Print sFilePath
    
    
        DoCmd.OutputTo acOutputReport, "Report1", acFormatPDF, FilePath
        MsgBox "Report Saved Successfully", vbInformation, "save confirmed"
    
    
    End Sub
    I've removed all the spaces from your filename to ensure it works, and added a debug to print the filepath to the immediate window for debugging purposes.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  8. #8
    d3ell is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2014
    Location
    doncaster UK
    Posts
    19

    Red face

    Quote Originally Posted by Minty View Post
    Just as an added bit of useful code for filepaths, I use this (blatantly stolen from somewhere on the interwebs)
    Just store it in a Module, if you don't have any modules call it modFunctions and store other useful snippets in there.

    Code:
    Public Function TrailingSlash(varIn As Variant) As String
        If Len(varIn) > 0& Then
            If Right(varIn, 1&) = "\" Then
                TrailingSlash = varIn
            Else
                TrailingSlash = varIn & "\"
            End If
        End If
    End Function
    Simply use it around any filepath and you know that it has the trailing slash added as required.

    Your code in completeness should be something like;
    Code:
    Private Sub Command125_Click()
        
        Dim sFileName As String
        Dim sFilePath As String
        
        sFileName = "Plot_" & Me.Customer_Plot_No & "_" & Me.Development & "_Remedial_works_report"
        sFilePath = CurrentProject.Path & "\" & sFileName & ".pdf"
    
        Debug.Print sFilePath
    
    
        DoCmd.OutputTo acOutputReport, "Report1", acFormatPDF, FilePath
        MsgBox "Report Saved Successfully", vbInformation, "save confirmed"
    
    
    End Sub
    I've removed all the spaces from your filename to ensure it works, and added a debug to print the filepath to the immediate window for debugging purposes.

    Thank you all for your input, I got sorted with the above code ( I just needed to put an 's' before FilePath on the 2nd to last line and it worked perfectly.

    you guys are great for assistance and it is really appreciated!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 43
    Last Post: 03-27-2014, 01:51 AM
  2. Replies: 2
    Last Post: 05-25-2012, 07:36 AM
  3. save folder to another directory
    By focosi in forum Programming
    Replies: 7
    Last Post: 08-30-2011, 07:22 AM
  4. Replies: 20
    Last Post: 08-08-2011, 01:34 PM
  5. Directory Database
    By bwaters in forum Access
    Replies: 1
    Last Post: 10-29-2010, 05:51 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums