Results 1 to 7 of 7

VBA to save attachments to shared drive via Forms

  1. #1
    shannonsims is offline Novice
    Windows 7 32bit Access 2010 64bit
    Join Date
    Mar 2013
    Location
    San Antonio TX
    Posts
    4

    VBA to save attachments to shared drive via Forms

    In an effort to digitize our personnel files, I am currentlybuilding an Access database that tracks stores employee information, to includeadmin documents, leave requests, training docs etc. After attending anAdvanced Access user class, I discovered that Access databases are limited to2GB. Because each document must be uploaded, loading them into thedatabase will not be sustainable using the attachment feature in Access 2010.
    My goal is to make thepreviously created forms that had been built to save the attachments within theTables now save to a shared drive and create a hyperlink to those stored fileswithin the table using VBA. I'm a Novice with VBA but have slowly startedpicking it up.
    An example of the fieldsexisting in my Admin Document Upload Form "AdminDocUploadfrm" are
    Employee- combo box


    Document Description- combo box
    Doc Date - Date
    Doc Upload - Attachment
    Submit - button

    My intent is for the VBAprogramming behind the "Submit Button" to format the save nameof the doc by combining the Doc Date+Employee name+DocDescription, save it to adesignated location on the shared drive and create a hyperlink in the Accesstable. And complicating things further, the file format is PDF for allsaved docs (Although I don't think I'll need to open the PDF's within Access.
    Any help in where to start onthis or where I can find some source code to start is appreciated. Thanks.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,384
    Documents are uploaded from where? If you are simply copying files from one folder location to another that can be fairly simple.

    If you need to extract documents from the attachment field and save to folder location:
    http://blogs.office.com/b/microsoft-...cess-2007.aspx

    For using hyperlinks, review:
    http://www.allenbrowne.com/casu-09.html
    http://allenbrowne.com/func-GoHyperlink.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    shannonsims is offline Novice
    Windows 7 32bit Access 2010 64bit
    Join Date
    Mar 2013
    Location
    San Antonio TX
    Posts
    4
    June7,

    Thanks for the links as I think they get me on the right track....but my inexperience is still hampering my efforts. The way the dBase is set up currently, it resides on a shared drive and remote users upload files from their hard drive into the table via a form with aforementioned fields. i now know that I have to save these files in a folder on the hard drive to keep from breaking the 2GB ceiling down the road. I have been using the code at the link http://blogs.office.com/b/microsoft-...cess-2007.aspx. I have modified it as shown below and while it compiles, I think that my basic coding skills are so weak that I'm missing an obvious mistake. It compiles, but obviously my logic is incorrect somewhere.



    Private Sub SubmitAdminDoc_Click()
    Const CALLER = "SaveAttachments"
    On Error GoTo SaveAttachments_ErrorHandler


    Dim rstChild As DAO.Recordset2
    Dim fldAttach As DAO.Field2
    Dim strFilePath As String


    If Right(strOutputDir, 1) <> "C:\Users\swsims\Desktop\Access_test_folder" Then strOutputDir = strOutputDir & "C:\Users\swsims\Desktop\Access_test_folder" 'Using this path as a test path


    Set rstChild = rstCurrent.Fields(AdminDocUpload).Value 'AdminDocUpload is the name of the attachment field on the form.

    rstChild.Close 'cleanup


    Exit Sub
    SaveAttachments_ErrorHandler:
    Debug.Print "Error # " & Err.Number & " in " & CALLER & " : " & Err.Description
    MsgBox Err.Description, VbMsgBoxStyle.vbCritical, "Error # " & Err.Number & " in " & CALLER
    Debug.Assert False 'always stop here when debugging
    Resume Next

    End Sub



    The error I get is "Error #424 in SaveAttachments" "Object Required". I made no changes to the first 6 lines of code (Because I didn't completely understand them) except for the first line that associates the code with a SubmitAdminDoc button that will someday execute the save. Any help is GREATLY appreciated. Thanks again for your time in this June7.

    Shannon

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,384
    I don't understand the conditional expression using strOutputDir. This variable is not declared in this procedure. Is it declared as a public and set outside this procedure? Why would you want to compare 1 character to a full path string? Why the right-most character?

    Field AdminDocUpload must be within quotes because it is not a variable, it is a literal reference.

    rstCurrent.Fields("AdminDocUpload")

    However, the code does nothing with the attachment. It simply opens then closes the recordset. The example procedure has While loop in there.

    Refer to link at bottom of my post for debugging guidelines.
    Last edited by June7; 03-15-2013 at 02:14 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    shannonsims is offline Novice
    Windows 7 32bit Access 2010 64bit
    Join Date
    Mar 2013
    Location
    San Antonio TX
    Posts
    4
    I had pulled some lines out of the originally provided code thinking that I didn't need them (an incorrect assumption). The strOutput Dir piece is right from the original source code and comparing the right-most character was not the intention. My biggest issue is that I don't understand all the terminologies and requirements in the existing code. I've parsed the dBase down for easy perusing in hopes that it may make what I'm trying to accomplish more easily understood. There are several other tables/forms and reports set up similarly in the full database but I believe if I can get the code down for this one, the others should be a matter of transposing and small adjustments.

    Thanks again for your patience and help in this June7. You've already gotten my further than I had gotten in weeks of searching and reading through VBA books.

    Shannon
    Attached Files Attached Files

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,384
    You have altered the original code too much. The example is designed to work with several procedures. The TestAddRemoveAndSave() calls the other three procedures. The calls pass the parent recordset to the 3 subs which then use the parent recordset to open the attachment field recordset. Your version is missing the part that opens the parent recordset.

    I suggest you paste the exact code into a general module (not behind the form) and test it before trying to customize for your situation. Create a table called Table1 with an attachment field named Files.

    You can run a sub in a general module with the VBA Editor menu Run options.

    Step debug. Follow the code as it executes. Very enlightening.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    shannonsims is offline Novice
    Windows 7 32bit Access 2010 64bit
    Join Date
    Mar 2013
    Location
    San Antonio TX
    Posts
    4
    June,
    Canned it and started from scratch. With much research and some mentorship from another site we were able to put together the following that works well for uploading the files from a Form. Still working on automating the generation of a hyperlink while maintaining the original name of the file. Here it is in hopes it may help someone else.

    Code:
    Private Sub AdminDocPath_DblClick(Cancel As Integer)
    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    'the number of the button chosen
    Dim FileChosen As Integer
    FileChosen = fd.Show
    '1) To set the caption of the dialog box,
    ' set the Title property
    fd.Title = "Select Admin Document to Upload"
    '2) Set the oddly named InitialFileName property to
    ' determine the initial folder selected
    fd.InitialFileName = "C:\Users\....."
    '3) Set the InitialView property to control how your files
    ' appear on screen (as a list, icons, etc.)
    fd.InitialView = msoFileDialogViewSmallIcons
    '4) To set the filters (you can have as many as you like)
    ' first clear any existing ones, then add them one by one
    fd.Filters.Clear
    fd.Filters.Add "PDF macros", "*.pdf"
    fd.Filters.Add "Excel macros", "*.xlsm"
    ' if there's more than one filter, you can control which
    ' one is selected by default
    fd.FilterIndex = 1
    ' if there's more than one filter, you can control which
    ' one is selected by default
    fd.FilterIndex = 1
    '5) Set the ButtonName property to control the text on
    ' the OK button (the ampersand means the following
    ' letter is underlined and choosable with the ALT key)
    fd.ButtonName = "Choose PDF file"
    If FileChosen <> -1 Then
    'didn't choose anything (clicked on CANCEL)
    MsgBox "Upload Cancelled"
    Else
    'display name and path of file chosen
    MsgBox fd.SelectedItems(1)
    End If
    'store the selected file to a variable
    strSelectedFile = fd.SelectedItems(1)
    'parse out the filename from the path
    strFilename = Right(strSelectedFile, Len(strSelectedFile) - InStrRev(strSelectedFile, "\"))
    'build the destination
    strDestination = "C:\Users\........" & strFilename
    'copy the file to the new folder
    FileCopy strSelectedFile, strDestination
    'store file as part of the record
    Me.AdminDocPath = strFilename
    End Sub

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

Similar Threads

  1. Replies: 1
    Last Post: 11-28-2012, 01:09 AM
  2. Front end on shared drive, modified date?!
    By redbull in forum Access
    Replies: 3
    Last Post: 09-13-2012, 10:32 AM
  3. Replies: 2
    Last Post: 03-01-2012, 04:14 AM
  4. Problems with Access 2003\2007 and shared drive
    By swasielewski in forum Access
    Replies: 3
    Last Post: 07-10-2009, 06:32 AM
  5. Multi Users on shared drive
    By wallen in forum Access
    Replies: 0
    Last Post: 08-25-2008, 08:57 AM

Tags for this Thread

Posting Permissions

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