Continuous subform - control to display image / icon based on another field (AC2007)

[AOB]

Hi there,

I have a continuous subform which queries a table of attachments. I'm using Access 2007 but not using the Attachment datatype; this DB will grow considerably and I don't want to waste precious space by filling it with bulky files. So instead, I have code which makes a copy of the attachment and adds the hyperlink (to the copy) to the table instead.

Now - I want to add a control to the subform to display an icon / image reflecting the file type of the attachment (Word doc, Excel s/s, PDF etc.)

But not sure how to go about it.

I was thinking I could use FileSystemObject.GetFileExtension at the point in the code where the attachment is added, and add a new field to my attachments table (i.e. translate "*.xls*" to "Excel", etc.) Then store / embed a handful of images for the core types I would expect and use an image control on the subform to display the image based on the value of that field.

But is that even possible / feasible?

Or, is there a handy API which can retrieve the icon associated with a file type based on what has been installed on the local machine (even if there is, there's still the problem of setting up the image control to display the appropriate icon specific to each record...)

Or, is there another control available which would be better suited to something like this than an Image control?

Or is there another method?...

I'll continue to tinker here but if anybody has any pointers they would be very gratefully received!

Thanks

AOB

 

[CJ_London]

It sounds like the issue is that you want to display an icon rather than text to indicate the filetype?

This is untested but works for displaying images in continous forms so give it a try.

1. Create a folder and put images for the different file extensions in it. We'll call the folder C:\TypeImages. For simplicity give each of the images the name of the extension (xls,xlsm,xlsx,doc,pdf, etc) and ensure they are the same filetype (I'll assume .bmp) - you can make it more sophisticated later

2. in your continuous form recordsource, create a new column to extract the file type from the file name and add the image filetype and path to your image folder

Ftype:"C:\TypeImages\" & mid(filename,instrrev(filename,".")+1) & ".bmp"

(result should be C:\TypeImages\xls.bmp)

3.in your continuous form add an unbound image control (not OLE object)and set its control source to Ftype

That should be all you need

 

[AOB]

Perfect! Works really well too!

CJ, as usual, I am indebted. Thanks very much for this!

AOB

 

[CJ_London]

No problem, happy to help Just out of curiosity, did you use make your own icons/download from web? or use the ones on the system? I couldn't find them when answering your question!

 

[AOB]

Yeah I just Google-imaged jpg files. Probably in breach of some kind of copyright but it's not for commercial use so should be safe enough

I just have the 5 or 6 main types and then a default paperclip for anything else. Can easily add new icons and update the code if the end users want.

Ideally, I would use some kind of API (if one existed) to determine the .ico file already saved as part of the install process for each application and just use that. Windows can do it (to display the icon in Explorer) so I'm sure there's a way to replicate it in VBA. Would save me having to maintain the folder of images.

I just don't know how to do it. But I'm sure it could be done...

Thanks again!