Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Oct 2007
    Posts
    172
    Thanks
    0
    Thanked 2 Times in 1 Post

    Exclamation MS Access 2010: Adding Attachments from Form using Unbound Field and VBA

    Hi All,

    I have a form in which the user inputs data and then some VBA processes and checks this data before putting into a table. The Form fields are not directly bound to the table.

    I want to modify this form to include an Attachment Control field, which is Unbound, which I can then assign to the table field in the VBA code.

    I have added an Attachement field to the table and an Attachment Control to the form and then added a line in the VB to assign the value of the Attachment to the table field in the normal way:

    Set rs = CurrentDb.OpenRecordset("tblDocumentReviews")
    rs.AddNew rs("Document Reference") = Me.Document_Reference
    rs("Version") = Me.Version
    rs("Revision") = Me.Revision
    rs("Attachment") = Me.Attachment
    rs.Update

    However, when I open the form the Attachment Control is apparently disabled and clicking it does nothing.

    If I create a form which is bound to the table, then I can add attachments in the way described in the MS Office online help.

    Can anyone let me know if it's simply not possible to do what I'm trying to do? What would be a good alternative? I can't have the form just bound to the table because I need to process the input data before assigning it to the table.

    thanks!
    Dom

  2. #2
    2 Star Lounger HiTechCoach's Avatar
    Join Date
    Sep 2011
    Location
    Oklahoma City, OK
    Posts
    162
    Thanks
    0
    Thanked 24 Times in 24 Posts
    Quote Originally Posted by dom_donald View Post
    Hi All,

    I have a form in which the user inputs data and then some VBA processes and checks this data before putting into a table. The Form fields are not directly bound to the table.

    I want to modify this form to include an Attachment Control field, which is Unbound, which I can then assign to the table field in the VBA code.

    I have added an Attachement field to the table and an Attachment Control to the form and then added a line in the VB to assign the value of the Attachment to the table field in the normal way:

    Set rs = CurrentDb.OpenRecordset("tblDocumentReviews")
    rs.AddNew rs("Document Reference") = Me.Document_Reference
    rs("Version") = Me.Version
    rs("Revision") = Me.Revision
    rs("Attachment") = Me.Attachment
    rs.Update

    However, when I open the form the Attachment Control is apparently disabled and clicking it does nothing.

    If I create a form which is bound to the table, then I can add attachments in the way described in the MS Office online help.

    Can anyone let me know if it's simply not possible to do what I'm trying to do? What would be a good alternative? I can't have the form just bound to the table because I need to process the input data before assigning it to the table.

    thanks!
    Dom
    The attachment data type field is tricky to work with via VBA code. Think of the attachment data type as a sub table. You will need to use work the field using a recordset.

    See: Microsoft® Access 2007 Working with the Attachment DataType

    I find it best to NOT store the fiels in the database using the Attachment data type. When using an SQL server 2005 or later as the back end I will store the files as BLOBs in the database.


    Here is how I attatch fiels: Document Links

    Boyd Trimmell aka HiTechCoach
    Microsoft MVP - Access Expert


    Boyd Trimmell aka HiTechCoach (Access Information here)
    Microsoft MVP - Access Expert
    "If technology doesn't work for people, then it doesn't work."

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Just to add a bit to Boyd's answer. Previous versions of Access did not handle the inclusion of documents or photos within the database well at all, so most users kept such things outside the database, and just stored the file path in the database.
    Now that the handling of photos has improved, and they have added the Attachment data type, many people, including me, just kept on doing it the "old" way because the old way worked well.
    Regards
    John



  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,329
    Thanks
    1
    Thanked 13 Times in 13 Posts
    Quote Originally Posted by johnhutchison View Post
    Just to add a bit to Boyd's answer. Previous versions of Access did not handle the inclusion of documents or photos within the database well at all, so most users kept such things outside the database, and just stored the file path in the database.
    Now that the handling of photos has improved, and they have added the Attachment data type, many people, including me, just kept on doing it the "old" way because the old way worked well.
    Yeah, same with me (but also maybe because I'm kind of lazy and it easy to cut and paste code). But I really need to "Eat That Frog" and start doing it the new way!
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    2 Star Lounger HiTechCoach's Avatar
    Join Date
    Sep 2011
    Location
    Oklahoma City, OK
    Posts
    162
    Thanks
    0
    Thanked 24 Times in 24 Posts
    The attachment data type has limitations. I would only consider using the attachment data type when creating a web database.

    FWIW: I have been getting lots of work lately helping people convert away from the attachment data type due to issues and limitations they create..
    Boyd Trimmell aka HiTechCoach (Access Information here)
    Microsoft MVP - Access Expert
    "If technology doesn't work for people, then it doesn't work."

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by HiTechCoach View Post
    I have been getting lots of work lately helping people convert away from the attachment data type due to issues and limitations they create..
    That's interesting to know. I just took the view that it was new, and I didn't want to trust it until it had been around a while and proved itself.
    Regards
    John



  7. #7
    2 Star Lounger HiTechCoach's Avatar
    Join Date
    Sep 2011
    Location
    Oklahoma City, OK
    Posts
    162
    Thanks
    0
    Thanked 24 Times in 24 Posts
    Most of the new table leve Features added in 2007 and 2010 were for creating a Web database and converting the tables to Sharepoint Lists. Like the Mulit-Vaule field, attachement, and calculated columns.

    You might find this helpful: Microsoft® Access 2007 Working with the Attachment DataType
    Boyd Trimmell aka HiTechCoach (Access Information here)
    Microsoft MVP - Access Expert
    "If technology doesn't work for people, then it doesn't work."

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,329
    Thanks
    1
    Thanked 13 Times in 13 Posts
    Quote Originally Posted by HiTechCoach View Post
    Most of the new table leve Features added in 2007 and 2010 were for creating a Web database and converting the tables to Sharepoint Lists. Like the Mulit-Vaule field, attachement, and calculated columns.

    You might find this helpful: Microsoft® Access 2007 Working with the Attachment DataType
    I'm glad you claried the intent of the new fields, because I've been playing with the Attachment field over the weekend, and I am less then enthusiastic about it. I can see how it might be useful for images, especially if you had like a single ID photo for a client. But beyond that it seems a little clunky.

    For example, if you want to add a new attachment, the standard windows file browse pop-up appears, but apparently you have no control over the starting directory (it goes to the last directory you used), and you can't even select which type of file you want to see. It comes up "All Files", and there are no other choices! And you can't enter a description for each file to help you later, you have to identify it merely by the filename.

    Image files apparently display on forms and reports, but not PDF or Word Doc files; with the latter 2 you only get little icons even when printing.

    Looks like I'm sticking with the "old" ways for now.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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