Extracting the filename from a picture attachment in VBA

Question

Is it possible to extract the filename portion of the picture attachment and show that piece of data in a text box on the form?

I currently have a picture attachment box on a form where you attach the picture and it shows you it on the form, I need to extract the filename/type portion of the attachment and display it somewhere else on the form.

I am not sure if this can be done in VBA but when I look at a query for the attachment field I see this:

-   Field1

          Field1.FileData

          Field1.FileName

          Field1.FileType

Am I mistaken in thinking I can access these layers of information?

Thanks

Mick

Answer 1

How about the code by Tom van Stiphout found at

http://social.msdn.microsoft.com/Forums/en-US/accessdev/thread/233bb1fc-2318-4ce1-960e-4ce97161b224/

---

Daniel Pineault, 2010 Microsoft MVP
http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 2

I can see what he is doing with the code but, I probably didn't make myself clear with what I am trying to do.

Rather than modify a table of existing records, I wish to carry out a dynamic update in a form that is in use, in that when the person selects the picture attachment and clicks 'OK' the text box on the same form will immediately update with the file name of the attachment.

I intended to add an event under AfterUpdate in the attachment box, but as I was writing the code I started down the same direction as Tom, in that I targeted the data table, but the attachment info doesn't appear in the table as I am building the record on the form, so I steered away from that and tried to access the data in the form - with no success.

I was clutching at straws with this:-

Set db = CurrentDb()
Set rs = db.OpenForm("dataentry")

rs.Edit
Set rspics = rs.Form("photobox").Value

picturename = rspics.Fields("Filename").Value

Forms!DataEntry!hyperlinktopic = picturename

but I'm not sure if I have to target an Object in the form....

Hope that is clear as mud, and I still appreciate all advice.

Answer 3

See whether this helps http://social.msdn.microsoft.com/Forums/en/accessdev/thread/0d31239a-c389-4420-ae45-2a5cdac70b17/#6c4dc4be-1601-4ec1-80c9-a77757eb5304

---

Andrey V Artemyev | Saint-Petersburg, Russia
Russian blog artemyev.biztoolbox.ru

Answer 4

I understand, this allows you to put the information into a label, is there anyway it can be sent to a text box in the form?

The reason I ask is, because as part of a query to filter out records required and create a word document I need the file name to bring the picture into word from its source folder.

The query I have made generates a temporary table of the records required, but, if I try to take the attachment field over to the temptable, it generates a runtime error'3838' : Multi-valued fields are not allowed in SELECT INTO statements.

Therefore I was hoping to pull out the filename at the form stage and populate a textbox(linked to a field in the main table) on the form, which will then become a field in the table with just the filename in it, becoming a single valued field and I could then use it in the query.

I may be trying to use a sledge hammer to crack an egg here, but I have been at this for days and am getting nowhere(desperate).

Mick

Answer 5

Solved that particular problem with use of queries to cascade the information to a different table before exporting to word. Many thanks for the suggestions got my brain thinking in a different way.

Mick