Page 1 of 2 12 LastLast
Results 1 to 15 of 17

VBA code to open A single file stored in the Attachment field by clicking ID field

  1. #1
    aspen15 is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2012
    Location
    Maldives
    Posts
    1

    Unhappy VBA code to open A single file stored in the Attachment field by clicking ID field

    Hi Every body
    I would apreciate if any one would be able to help me do this. I am quet sure this is posible though not siple.I have a database with just one table named Table1. It has two fields an "ID' field and 'Files' (wich is the name of the the attachment field). I have build a form based on that table and on the Forms 'ID's on click event I have put a cord
    to open the attachment. It opens the attachment just by one click on the 'ID' field. But the problem is it only opens the attachment file of the first record even if i click on the second records 'ID'. Can some Body help me make it open The attachment for the record's 'ID' Thats being clicked. here is the cord its on an access blog.
    Can some body please help me. I mainly use macros and very new to VBA.

    Thanks

    Private Sub ID_CLICK()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Const strTable = "Table1" '<- Replace this with your table with attachment
    Const strField = "Files" '<- Replace this with the fieldname of your attachment
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strTable)
    'rst.MoveNext ' Uncomment this line to go to the 2nd row in the Table.
    OpenFirstAttachmentAsTempFile rst, strField
    rst.Close
    End Sub

    Public Function OpenFirstAttachmentAsTempFile(ByRef rstCurrent As DAO.Recordset, ByVal strFieldName As String) As String


    Dim rstChild As DAO.Recordset2
    Dim fldAttach As DAO.Field2
    Dim strFilePath As String
    Dim strTempDir As String
    strTempDir = Environ("Temp") ' Get the Temp directory from the environment variable.
    If Right(strTempDir, 1) <> "\" Then strTempDir = strTempDir & "\" ' Make sure the path always ends with a backslash.
    Set rstChild = rstCurrent.Fields(strFieldName).Value ' the .Value for a complex field returns the underlying recordset.
    strFilePath = strTempDir & rstChild.Fields("FileName").Value ' Append the name of the first (and only) attached file to temp dir.
    If Dir(strFilePath) <> "" Then ' the file already exists--delete it first.
    VBA.SetAttr strFilePath, vbNormal ' remove any file attributes (e.g. read-only) that would block the kill command.
    VBA.Kill strFilePath ' delete the file.
    End If
    Set fldAttach = rstChild.Fields("FileData") ' The binary data of the file.
    fldAttach.SaveToFile strFilePath
    rstChild.Close ' cleanup
    VBA.Shell "Explorer.exe " & Chr(34) & strFilePath & Chr(34), vbNormalFocus ' Use Windows Explorer to launch the file.
    End Function 'OpenFirstAttachmentAsTempFile

    Thank you

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,412
    Might have to abandon the Attachment datatype field and instead store documents outside the db and use a child table to store the multiple document paths for each ID.

    Review
    http://en.allexperts.com/q/Using-MS-...ion-access.htm
    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
    aspen is offline Competent Performer
    Windows Vista Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    127
    Thanks for trying to help. But These are confidential files I can't risk keeping it in a folder of a shared pc which I have to use ocationaly. so Frankly I dont want use the hyperlink method yet.
    so can you please help with the cording to open it using the ID and attachment field only. Or do you mean its impossible

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,412
    I tried to open the attachment from a query that lists the individual attachment items but that did not work. I couldn't find any example code to do what you want. It seems the AttachmentOpen dialog is necessary. I couldn't even find code that would open that dialog window.

    How is the database any more secure than a protected file folder?
    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
    aspen is offline Competent Performer
    Windows Vista Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    127
    Hi Thanks
    The original database is in my pendrive so each time i use it I am personally there
    this is the code I tried using Its in access blog. It opens the file just clicking the 'ID" field.But opens only the firstrecord's
    attachment.See if you can make it open the record being clicked.

    Private Sub ID_CLICK()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Const strTable = "Table1" '<- Replace this with your table with attachment
    Const strField = "Files" '<- Replace this with the fieldname of your attachment
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strTable)
    rst.MoveNext ' Uncomment this line to go to the 2nd row in the Table.
    OpenFirstAttachmentAsTempFile rst, strField
    rst.Close
    End Sub

    Public Function OpenFirstAttachmentAsTempFile(ByRef rstCurrent As DAO.Recordset, ByVal strFieldName As String) As String
    Dim rstChild As DAO.Recordset2
    Dim fldAttach As DAO.Field2
    Dim strFilePath As String
    Dim strTempDir As String
    strTempDir = Environ("Temp") ' Get the Temp directory from the environment variable.
    If Right(strTempDir, 1) <> "\" Then strTempDir = strTempDir & "\" ' Make sure the path always ends with a backslash.
    Set rstChild = rstCurrent.Fields(strFieldName).Value ' the .Value for a complex field returns the underlying recordset.
    strFilePath = strTempDir & rstChild.Fields("FileName").Value ' Append the name of the first (and only) attached file to temp dir.
    If Dir(strFilePath) <> "" Then ' the file already exists--delete it first.
    VBA.SetAttr strFilePath, vbNormal ' remove any file attributes (e.g. read-only) that would block the kill command.
    VBA.Kill strFilePath ' delete the file.
    End If
    Set fldAttach = rstChild.Fields("FileData") ' The binary data of the file.
    fldAttach.SaveToFile strFilePath
    rstChild.Close ' cleanup
    VBA.Shell "Explorer.exe " & Chr(34) & strFilePath & Chr(34), vbNormalFocus ' Use Windows Explorer to launch the file.
    End Function 'OpenFirstAttachmentAsTempFil

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,412

    Open File From Attachment Field

    I understand the code. It is intended for an attachment field with only 1 attachment. The site the code comes from even states so. I am not seeing how it can be made to work with multiple attachments.

    Here is more code from that same blog site {EDIT: sorry, web page no longer available}

    Between the two sets of code, I worked out this:
    Code:
    Private Sub tbxFileName_Click()
    Dim strFilePath
    With Me.RecordsetClone
    .Bookmark = Me.Bookmark
    strFilePath = "C:\Temp\" & Me.tbxFileName
    If Dir(strFilePath) <> "" Then VBA.Kill strFilePath
    ![Table1.MyDocs.FileData].SaveToFile strFilePath
    VBA.Shell "Explorer.exe " & Chr(34) & strFilePath & Chr(34), vbNormalFocus
    End With
    End Sub
    I created a saved query that expanded the attachment field to multiple records. Then based a form on that query. Bound textboxes to fields and used click event of one to run the code.

    Otherwise, I guess your option is a very large pendrive or portable hard drive and my suggested structure.
    Last edited by June7; 07-30-2014 at 12:25 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.

  7. #7
    aspen is offline Competent Performer
    Windows Vista Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    127
    Thanks

    Supose I have only one attachment per record in my table how would the code be. I am very new to vba . Can you please help.
    Thats without using test boxes
    Thank you
    Last edited by aspen; 04-10-2012 at 06:39 AM.

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,412
    My suggested code should work for any number of attachments but requires a form and textboxes. What do you want instead? Your original code also requires a form and textbox.

    I just reread your original post and realize I misunderstood issue. I will retest the original code later and see if I can get to work.
    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.

  9. #9
    aspen is offline Competent Performer
    Windows Vista Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    127
    Can you please explain what you mean by a saved query. are the text boxes bound to the table or the querry. and how many fields are there. is it ID and attachment field only. Actually my knwledge of vba is not enough to understand it looking at the code.
    words are not enough to thank you

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,412
    Saved query is an Access query object that you see in the Navigation pane. The query would be the RecordSource of the form. My test included the ID and attachment field only.

    I modified the original code ID_Click event with:

    Set rst = dbs.OpenRecordset("SELECT * FROM Table1 WHERE ID=" & Me.ID)
    Last edited by June7; 04-10-2012 at 02:12 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.

  11. #11
    aspen is offline Competent Performer
    Windows Vista Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    127
    Is a query needed because we use more than one attachment per record? Is that a normal query? I mean I tried a lot with a normal query. But as you mentioned that itss saved query that expanded the attachment field to multiple records I think its different. And if each record has a single attachment can we avoid a saved query to expand multiple records.

    here is what i did . Icreated a normal query based on the table. than i created a simple form on the query. I put 2 text boxes and opened their property sheet and in its data tabs drop down list I selected one of the two field for each one. or should i bound it to the table?. Then I put your cord onthe ID fields on click event and changed "tbxFileName" to the apropriat name.
    When i click I get the error "Item not find i this collection" and This line in the code is yellow "![Table1.MyDocs.FileData].SaveToFile strFilePath"
    You shold know by now what i am doing wrong.

    Thank you

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,412
    As I said, I originally misunderstood the issue.

    If only one attachment per record, use the original code with the change I suggested in my previous post. The form RecordSource can be just the table itself. That worked for me.

    If there are multiple attachments per record then need the query that expands the attachment field as form RecordSource and use my code.
    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.

  13. #13
    aspen is offline Competent Performer
    Windows Vista Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    127
    I am extremly sory that i didnt see the second line of the 8th post "That you mis understood my first first post. The first line made be so happy and excited I just went to make it work.
    I am really sorry. Please rest the original code as you said you would try. I have no other choice and no hope but you

    Thank you a lot

  14. #14
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,412
    I did try the original code. I had to make one modification as explained in post #10.
    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.

  15. #15
    aspen is offline Competent Performer
    Windows Vista Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    127
    Wonder full!
    Its working . I don't know how thank you. You are really so so great
    Thank you so much.
    Here is the code to open an attacment by clicking the id field by Super Moderator

    Private Sub ID_CLICK()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Const strTable = "Table1" '<- Replace this with your table with attachment
    Const strField = "Files" '<- Replace this with the fieldname of your attachment
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("SELECT * FROM Table1 WHERE ID=" & Me.ID)
    'rst.MoveNext ' Uncomment this line to go to the 2nd row in the Table.
    OpenFirstAttachmentAsTempFile rst, strField
    rst.Close
    End Sub

    Public Function OpenFirstAttachmentAsTempFile(ByRef rstCurrent As DAO.Recordset, ByVal strFieldName As String) As String
    Dim rstChild As DAO.Recordset2
    Dim fldAttach As DAO.Field2
    Dim strFilePath As String
    Dim strTempDir As String
    strTempDir = Environ("Temp") ' Get the Temp directory from the environment variable.
    If Right(strTempDir, 1) <> "\" Then strTempDir = strTempDir & "\" ' Make sure the path always ends with a backslash.
    Set rstChild = rstCurrent.Fields(strFieldName).Value ' the .Value for a complex field returns the underlying recordset.
    strFilePath = strTempDir & rstChild.Fields("FileName").Value ' Append the name of the first (and only) attached file to temp dir.
    If Dir(strFilePath) <> "" Then ' the file already exists--delete it first.
    VBA.SetAttr strFilePath, vbNormal ' remove any file attributes (e.g. read-only) that would block the kill command.
    VBA.Kill strFilePath ' delete the file.
    End If
    Set fldAttach = rstChild.Fields("FileData") ' The binary data of the file.
    fldAttach.SaveToFile strFilePath
    rstChild.Close ' cleanup
    VBA.Shell "Explorer.exe " & Chr(34) & strFilePath & Chr(34), vbNormalFocus ' Use Windows Explorer to launch the file.
    End Function 'OpenFirstAttachmentAsTempFile

    Thank you

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

Similar Threads

  1. Replies: 11
    Last Post: 09-27-2011, 07:19 AM
  2. open file as per field in the table
    By yoyo46 in forum Access
    Replies: 1
    Last Post: 06-16-2011, 12:28 PM
  3. Replies: 7
    Last Post: 01-12-2011, 08:59 AM
  4. How do you file save an 'Attachment' content through code?
    By morespamforya in forum Programming
    Replies: 3
    Last Post: 08-06-2010, 08:58 AM
  5. Print files stored in an OLE field
    By Brad Harris in forum Programming
    Replies: 1
    Last Post: 12-28-2009, 07:33 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
  •  
Tech Forums: Microsoft Office Forums