Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    ck4794 is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Oct 2013
    Posts
    80

    add attachment with file dialog from command button

    the title says exactly what i want to do. i had the file dialog working earlier and started tweaking and broke it. the problem here is i'm peice parting examples from all over the interwebs and can't make them jive. here's the code i have on the button so far:


    Private Sub filedialog_Click()
    Dim filepath As String
    Dim fDialog As Office.filedialog
    Dim varFile As Variant
    filepath = fDialog()
    ' This requires a reference to the Microsoft Office 11.0 Object Library.
    ' Set up the File dialog box.
    Set fDialog = Application.filedialog(msoFileDialogFilePicker)
    With fDialog

    ' Allow the user to make multiple selections in the dialog box.
    .AllowMultiSelect = False

    ' Set the title of the dialog box.
    .Title = "Select a File"
    ' Clear out the current filters, and then add your own.
    .Filters.Clear
    .Filters.Add "All Files", "*.*"
    ' Show the dialog box. If the .Show method returns True, the
    ' user picked at least one file. If the .Show method returns
    ' False, the user clicked Cancel.
    If .Show = True Then
    ' Loop through each file that is selected and then add it to the list box.
    For Each varFile In .SelectedItems
    Me.files.varFile
    Next
    Else
    MsgBox "You clicked Cancel in the file dialog box."
    End If
    End With
    End Sub
    ' Check that the user selected something
    If Len(filepath) = 0 Then
    Debug.Assert "No file selected!"
    Exit Sub
    End If
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblAttach")
    ' Add a new row and an attachment
    rst.AddNew
    AddAttachment rst, "Files", filepath
    rst.Update
    ' Close the recordset
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing

    Exit Sub

    all i have, since this is a test database, is a table, "tblattach", and an attachment field, "files".

    Can anybody help me edit that code, so that when i click the button, i browse through the file dialog, select the file i want, and attach it to that field in that table.

    Thanks in advance.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Do you want to actually embed the file into attachment type field or just the file name in a text field?

    You understand embedding files in table uses up a lot of Access file size limit - really bloats a db. Review {EDIT: sorry, web page no longer available}
    Last edited by June7; 07-30-2014 at 11:58 AM.
    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
    ck4794 is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Oct 2013
    Posts
    80
    Quote Originally Posted by June7 View Post
    Do you want to actually embed the file into attachment type field or just the file name in a text field?

    You understand embedding files in table uses up a lot of Access file size limit - really bloats a db. Review http://blogs.office.com/b/microsoft-...cess-2007.aspx
    i do understand this. from a security perspective my database will contain a lot of personal information that i prefer to control access to. From a portability perspective, the database does a lot of moving around, from home to work, etc. I wish to have the pictures actually in it. but thanks for the consideration.

  4. #4
    ck4794 is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Oct 2013
    Posts
    80
    i'm back at square one. i'm getting a ton of errors i don't understand. here is what i have in the on click event of the "upload" button. the table is tblimages, and the filed is just file.
    Private Sub Command10_Click()
    Dim SelectFile() As String
    Dim FD As Application
    FileDialog = "Application.FileDialog(msoFileDialogOpen)"
    With FD
    .AllowMultiSelect = False
    .title = "Please select file to attach"
    If .show = True Then
    SelectFile = .SelectedItems(1)
    Else
    Exit Sub
    End If
    End With
    Set FD = Nothing
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    ' Ask the user for the file
    Dim filepath As String
    filepath = SelectFile()
    ' Check that the user selected something
    If Len(filepath) = 0 Then
    Debug.Assert "No file selected!"
    Exit Sub
    End If
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblimages")
    ' Add a new row and an attachment
    rst.AddNew
    AddAttachment rst, "Files", filepath
    rst.Update
    'AddAttachment
    rst.Update
    ' Close the recordset
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    End Sub
    Sub AddAttachment(ByRef rstCurrent As DAO.Recordset, ByVal strFieldName As String, ByVal strFilePath As String)
    Const CALLER = "AddAttachment"
    On Error GoTo addAttachment_ErrorHandler


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


    If Dir(strFilePath) = "" Then ' the specified file does not exist!
    msgbox "The specified input file does not exist: " & vbCrLf & strFilePath, vbCritical, "File not found"
    Exit Sub
    End If


    Set rstChild = rstCurrent.Fields(strFieldName).Value ' the .Value for a complex field returns the underlying Recordset.
    rstChild.AddNew ' add a new row to the child Recordset
    Set fldAttach = rstChild.Fields(m_strFieldFileData) ' set the DAO.Field2 object to the field that holds the binary data.
    fldAttach.LoadFromFile strFilePath ' store the file's contents in the new row.
    rstChild.Update ' commit the new row.
    rstChild.Close ' close the child Recordset.
    addAttachment_ErrorHandler:
    'Check for Run-time error '3820': (occurs if the file with the same name is already attached)
    'You cannot enter that value because it duplicates an existing value in the multi-valued lookup or attachment field.
    'Multi-valued lookup or attachment fields cannot contain duplicate values.
    Debug.Print "Error # " & Err.Number & " in " & CALLER & " : " & Err.Description
    If Err.Number <> 3820 Then
    msgbox Err.Description, VbMsgBoxStyle.vbCritical, "Error # " & Err.Number & " in " & CALLER
    Debug.Assert False ' always stop here when debugging
    Else
    msgbox "File of same name already attached", VbMsgBoxStyle.vbCritical, "Cannot attach file"
    End If
    End Sub

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    If you want to attach multiple documents in one field for each record, that is trickier. Review https://www.accessforums.net/program...ent-23686.html

    Basically, within the loop that reads each selected file name from file picker dialog, instead of writing file name to listbox, have code to save the attachment to the field.

    Two other threads with code to manage attachments:
    https://www.accessforums.net/program...tes-24755.html
    https://www.accessforums.net/access/...nts-14477.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.

  6. #6
    ck4794 is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Oct 2013
    Posts
    80
    Quote Originally Posted by June7 View Post
    If you want to attach multiple documents in one field for each record, that is trickier. Review https://www.accessforums.net/program...ent-23686.html

    Basically, within the loop that reads each selected file name from file picker dialog, instead of writing file name to listbox, have code to save the attachment to the field.

    Two other threads with code to manage attachments:
    https://www.accessforums.net/program...tes-24755.html
    https://www.accessforums.net/access/...nts-14477.html
    i see what you're doing here... not just in my post but others... "teach a man to fish" kind of scenario. but i'm kind of lost in the sauce, you're trying to show me reelin' in and i'm sitting here trying to figure out how to bait my hook... i'm not pickin' up what you're layin' down...

    also no i do not want a multiple documents. i want one document per record, using the ID from the main table as a fk. (Side note: this is because i have a finite set of documents i want. ie this is a ... document, that is a ... document. my labels are irrelevant here and out of context).
    my tblimages should look like this:
    ID last FILE type
    1 billy joe bob (paperclip)1 type 1
    2 mary sue (paperclip)1 type 3
    3 tammy lynn (paperclip)1 type 4
    4 jimmy john (paperclip)1 type 2
    *new

    i want to click a button to open a file dialog to add that new record at the bottom, attaching a single file. I already have the images displayed and reported how i want them. also this meets another requirement i have of querying who does not have type 1 type 2 type 3 or type 4 etc.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Post your code with code tags and it will retain indentation and be easier to read.

    The second code is not looping through the selected files of the file picker.

    Do you want multiple documents saved in single field or only one document?

    Get one thing at a time to work. Can you get code to select files and save name to listbox working?

    Refer to link at bottom of my post on debugging techniques.
    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.

  8. #8
    ck4794 is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Oct 2013
    Posts
    80
    Quote Originally Posted by June7 View Post
    Post your code with code tags and it will retain indentation and be easier to read.

    The second code is not looping through the selected files of the file picker.

    Do you want multiple documents saved in single field or only one document?

    Get one thing at a time to work. Can you get code to select files and save to listbox working?

    Refer to link at bottom of my post on debugging techniques.
    damn i was in the middle of editing my last when you replied haha.
    can you be more specifics on "tags"?
    i only want single documents
    i can in fact make the button work to put the file name into the list box. that example i can articulate on my own. but getting it from there to the attachment field is my problem.

    i do appreciate the help you are offering here. thank you for your time and patience.

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    The Advanced post editor has some additional post formatting tools, one of which is code tags. Enclose the code you copy/paste from the VBA editor between tags and the indentation will be retained. Tags are little code words within [ ] placed at each end of the text. The end tag uses a /, like: [/code]
    Use the tool button or type the tags. Once you learn them, can type them in the regular post editor. The regular editor has a few tag buttons (url, quote, etc).

    Post that working, error-free code that allows only single file selection.
    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.

  10. #10
    ck4794 is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Oct 2013
    Posts
    80
    Code:
    Private Sub Command10_Click()
     Dim fDialog As Office.FileDialog
       Dim varFile As Variant
    
    
       ' Clear the list box contents.
       Me.File.RowSource = ""
    
    
       ' Set up the File dialog box.
       Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
       With fDialog
          ' Allow the user to make multiple selections in the dialog box.
          .AllowMultiSelect = False
                
          ' Set the title of the dialog box.
          .title = "Select a File"
    
    
          ' Clear out the current filters, and then add your own.
          .Filters.Clear
          .Filters.Add "All Files", "*.*"
    
    
          ' Show the dialog box. If the .Show method returns True, the
          ' user picked at least one file. If the .Show method returns
          ' False, the user clicked Cancel.
          If .Show = True Then
             ' Loop through each file that is selected and then add it to the list box.
             For Each varFile In .SelectedItems
                Me.File.AddItem varFile
             Next
          Else
             msgbox "You clicked Cancel in the file dialog box."
          End If
       End With
    End Sub
    sorry had a 'der da der' moment with the word tags.

    this i can make work. click a button, open a dialog, insert a file name into an unbound listbox who's source is a value list. can't quite make that jump to inserting an attachment into the attachment field in a record in a table (tblimages, field= "file")

    also this is a straight copy and paste from microsoft's support sight

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Need some more understanding of your structure. You said "i want one document per record, using the ID from the main table as a fk". So is this button and code on a form bound to 'main' table? You want the attachment in a related dependent table? Is this dependent table bound to a subform on the 'main' form?

    Dependent table is named tblAttach or tblImages? What are the FK ID and attachment fields named?
    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.

  12. #12
    ck4794 is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Oct 2013
    Posts
    80
    whooops! sorry i'm going back and forth between two databases, one at work and one at home. in the one at work, when i posted this, i was goofing off with one that had "tblattach". the one at home, which is my love child, has "tblimages".

    i'm going to try to explain this but it may come out kind of jumbled...
    main table
    name: tblprofile_active
    PK = "ID" Autonumber
    Text Field = "Last"

    attachment table i want to insert into
    name: tblimages
    PK = "ID" Autonumber
    FK = "Last" Looks up ID from tblProfile_Active (Shows me "Last")
    Attachment field = "File"
    Lookup Field = "Type" (Looks up from another table with the preset labels for this image i want the user to choose from)

    a user would:
    open form - "frmprofile_active"
    bound to "tblprofile_active"

    click a button called "upload" which is located on a sub form "subImages" which opens a pop up form "frmuploadimages"

    "frmuploadimages" is bound to "tblimages", onopen event is to go to new record (this was before i attempted this new venture, don't know if it needs to go or stay)
    form contains:
    combobox "type" where users select type
    cmdbutton "upload" which is the target of conversation here, the one i want to open the dialog and insert an image
    listbox "file" (added during this venture)
    used to have "file" attachment field, which i deleted cause i don't think it's relevant any more.
    used to have combobox "last" which was set to not visible, and which on open was set to the ID from frmprofile_active, and using my previous method of the native attachment manager this was working to relate the record with the attachment properly)

    does this, combined with the example from post #8 paint a better picture?

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Here's how to use the code from the Access Blog link. Copy/paste the code into a general module. Make sure the module level constant declarations are in the module header. Call the appropriate sub from the form code:
    Code:
     
         If .Show = True Then
             For Each varFile In .SelectedItems
                Dim rst As DAO.Recordset
                Const strTable = "tblImages"
                Const strField = "File"
                Set rst = CurrentDb.OpenRecordset(strTable)
                rst.AddNew
                rst!Last = Me.ID
                AddAttachment rst, strField, varFile
                rst.Update
                rst.MoveLast
                rst.Close
             Next
    It would be possible to merge the two procedures and have all code behind the form but go with this for now.

    EDIT: Ooops, can't put in general module because of the Me alias.
    Last edited by June7; 07-30-2014 at 11:56 AM.
    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.

  14. #14
    ck4794 is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Oct 2013
    Posts
    80
    so i let this go for a while and i'm back at it. below is the code i've put together. i get this weird error "item is not in this collection". don't know what to do.
    Code:
    Sub AddAttachment(ByRef rstCurrent As DAO.Recordset, ByVal strFieldName As String, ByVal strFilePath As String)
        Const CALLER = "AddAttachment"
        On Error GoTo AddAttachment_ErrorHandler
    
    
        Dim rstChild  As DAO.Recordset2
        Dim fldAttach As DAO.Field2
    
    
        If Dir(strFilePath) = "" Then ' the specified file does not exist!
            msgbox "The specified input file does not exist: " & vbCrLf & strFilePath, vbCritical, "File not found"
            Exit Sub
        End If
    
    
        Set rstChild = rstCurrent.Fields(strFieldName).Value ' the .Value for a complex field returns the underlying Recordset.
        rstChild.AddNew ' add a new row to the child Recordset
        Set fldAttach = rstChild.Fields(m_strFieldFileData) ' set the DAO.Field2 object to the field that holds the binary data.
        fldAttach.LoadFromFile strFilePath ' store the file's contents in the new row.
        rstChild.Update ' commit the new row.
        rstChild.Close ' close the child Recordset.
    
    
        Exit Sub
    AddAttachment_ErrorHandler:
        'Check for Run-time error '3820': (occurs if the file with the same name is already attached)
        'You cannot enter that value because it duplicates an existing value in the multi-valued lookup or attachment field.
        'Multi-valued lookup or attachment fields cannot contain duplicate values.
        Debug.Print "Error # " & Err.Number & " in " & CALLER & " : " & Err.Description
        If Err.Number <> 3820 Then
            msgbox Err.Description, VbMsgBoxStyle.vbCritical, "Error # " & Err.Number & " in " & CALLER
            Debug.Assert False ' always stop here when debugging
        Else
            msgbox "File of same name already attached", VbMsgBoxStyle.vbCritical, "Cannot attach file"
        End If
        Exit Sub
    End Sub 'AddAttachment
    
    
    Private Sub Command10_Click()
     Dim fDialog As Office.FileDialog
       Dim varFile As Variant
       Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
       With fDialog
          ' Allow the user to make multiple selections in the dialog box.
          .AllowMultiSelect = False
              
          ' Set the title of the dialog box.
          .title = "Select a File"
    
    
          ' Clear out the current filters, and then add your own.
          .Filters.Clear
          .Filters.Add "All Files", "*.*"
    
    
          ' Show the dialog box. If the .Show method returns True, the
          ' user picked at least one file. If the .Show method returns
          ' False, the user clicked Cancel.
          If .Show = True Then
             ' Loop through each file that is selected and then add it to the list box.
          Dim rst As DAO.Recordset
                Const strTable = "tblImages"
                Const strField = "File"
                Set rst = CurrentDb.OpenRecordset(strTable)
                rst.AddNew
                AddAttachment rst, strField, varFile
                rst.Update
                rst.MoveLast
                rst.Close
          Else
             msgbox "You clicked Cancel in the file dialog box."
          End If
       End With
    End Sub

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    The error is for which line? Which line does the debugger highlight?
    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.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 08-15-2013, 08:33 AM
  2. Replies: 10
    Last Post: 09-15-2011, 03:58 PM
  3. Command Button to Find .doc file
    By cg1465 in forum Forms
    Replies: 8
    Last Post: 09-15-2010, 08:28 AM
  4. Replies: 1
    Last Post: 07-27-2010, 02:27 PM
  5. Create a command button to Browes for file
    By sawill in forum Programming
    Replies: 3
    Last Post: 03-15-2009, 05:02 PM

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