Problem with displaying images when the stored link is a relative address

[jeh35]

I have what should be a simple problem, yet so far I’ve not found a solution although it must have been solved by others a few zillion times. A post about it to Access World Forums generated plenty of reads but no replies. Can anyone help here?

Each record in a table in my Access 2010 database includes a field containing a hyperlink to an associated image file. The link has to be stored as a relative address since the whole system (database plus image files) is used from time to time on different computers.



Displaying images in Forms and Reports seems to require that the image control contain the absolute path. Therefore I can’t set the image control directly to the link field in the record. This is easy to deal with when the image is needed in a Form. An Event Procedure on the After Update event calls a decoding routine that, amongst other things, uses VBA to find the full path to the current folder, as well as performing some other checks.

Reports are a different matter, there being no equivalent in Report properties to After Update and similar events in Form properties. One helpful suggestion on the web is to add the path to the database files in the underlying query. That will work well for one computer, where the path is fixed and thus can be hard-coded into the query, but I don’t know any dynamic way to load the current path into a query. Is it possible to do this?

Alternatively, and perhaps better, could someone please suggest another way of displaying the images?

I hope this is a clear descrption of the problem. I'm using Win 7.

[June7]

Why are image files copied to other computers? The different computers cannot connect to central network server?

Is this a split database?

Is the field actually a hyperlink type or just text with a path/filename?

Image ControlSource can have dynamic path. With name of image file in text field, this works for me:

=CurrentProject.Path & "\subfoldername\" & [fieldname]

CurrentProject.Path will also work in a query.

[jeh35]

Why are image files copied to other computers? The different computers cannot connect to central network server?

Is this a split database?

Is the field actually a hyperlink type or just text with a path/filename?

Image ControlSource can have dynamic path. With name of image file in text field, this works for me:

=CurrentProject.Path & "\subfoldername\" & [fieldname]

CurrentProject.Path will also work in a query.

Thanks for this,

1. The entire system normally runs on a desktop. From time to time it is transferred to a laptop for use while travelling and connection back to base isn't available.

2. The database isn't split

3. The field is a hyperlink, and needs to stay that way because of the way it's entered into the DB via a Form. Setting the control to "=CurrentProject.Path & "\subfoldername\" & [fieldname]" works as long as the filename is set as text but doesn't when it's a hyperlink. Is there any way round this?

4. I'll try the Query route when I get the chance in an hour or two.

Thanks John

[June7]

Build a custom function to extract the filename part from the hyperlink. This article describes the composition of value in a hyperlink field http://www.allenbrowne.com/casu-09.html

Alternative to hyperlink field and HyperlinkDialog, use Windows FileDialog to capture file name and save to text field.

Also review http://allenbrowne.com/func-GoHyperlink.html

[jeh35]

I've built, and have used for some time, a custom function to extract the filename part from the hyperlink. I use it when I need to display the image in a Form. I'd assumed I could use it directly in a Report, but I've not been able to find how to incoporate it when the Report is launched.

[June7]

Should be the same for Image control on report.

[jeh35]

Unless I've misunderstood something (always possible), that doesn't work. For Forms, which refer to a single Record in the underlying Query, the custom function "DisplayMyImage(ctlName,FileRelAddr)" is called via the Form's After_Update event. This works, because the Form is refreshed for every new input record. Public Sub DisplayMyImage is in VBA module1 in the database.

However a Report displays results from many records at once, so as far as I can see this technique can't be used. I've just tried setting the image Control Source to "=[CurrentProject].[DisplayMyImage]([ctlName],[FileRelAddr])]". Access accepts this syntax but doesn't produce any visible output.

Apologies for the intermittent responses. It's the middle of a Spring Saturday here and I'm in and out.

[June7]

What does the DisplayMyImage function do?

If you have a function to extract filename part from hyperlink:

=[CurrentProject].[Path] & "\" & GetFilenameFunction([fieldname])

[jeh35]

What does the DisplayMyImage function do?

If you have a function to extract filename part from hyperlink:

=[CurrentProject].[Path] & "\" & GetFilenameFunction([fieldname])

Thank you June7. That's got it working.

best wishes John