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

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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!

Reply | Quote

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..

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote