Print hyperlink images in a report?

Using WinXP / Access 2002 -

A client of ours uses a DB designed by another vendor to track patient information, which is gathered remotely on a paperless system and stored on a central server.  We in turn use this information for patient billing and insurance filing.  We access the DB offsite with MS-Access via ODBC.

One of the fields is a signature field, which stores a BMP of the signature in a directory on the server and path/filename of the BMP is stored in a DB field (which could be turned into a hyperlink).  This signature isn't always obtained, but we must accurately know which patients signed and which didn't.  The path field is null if nothing was obtained, which is easy enough to discern if something is there or not.  

However, at times the employees gathering the information will place an "X" in the signature block, or write something like "Unable".  To us it looks like a valid signature based on the path existing (not null), but in reality, it's worthless.  To further complicate things, sometimes the BMP gets scrambled in transmission; in which case, again we don't have a valid signature.

As a result, we must manually look at each of these via a utility built to view all patient data, including the signature, but it is cumbersome and *slow*.  We have to pull a couple hundred a day, so this gets very time consuming.  

What I'm looking to do is generate an Access report, that shows the identifying information (account number/name/etc.) and shows the actual BMP picture based on the path.  Then we can easily spot the ones that are invalid and mark them as so on our system.  However, all of the information I've read toward doing this relates to the picture being locally stored on your hard drive and not on an external server, specifically:

How to display images from a folder in a form, a report, or a data access page;EN-US;285820

How to embed a bitmap object in a report at run time in Access 2002;EN-US;286459

However, these don't work when the files/links are not local.

Any ideas how I can build such a report?  Thanks.

Who is Participating?
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
Try something like this...

Option Compare Database
Option Explicit

Dim sLocalPath as string

Private Sub Report.Open

dim sServerPath as string, dbl as double

sServerPath = Me.WhateverYourFieldIsThatStoresThis   '(Prob. should be hidden on the report)
sLocalPath = Application.Path & "\images\" &  fn_get_file_name(sServerPath)

FileCopy sServerPath, sLocalPath

Me.WhateverYourImageControlIs.Picture = sLocalPath

End Sub

Private Sub Report.Close

'Delete the .bmp after you're done
Kill sLocalPath

end sub

Function fn_get_file_name(ByVal strString As String) As String

'Searches from right to left in strString and returns string to the right of "\"
'if no file name was found--returns NO_FILE
'ex. c:\vb\programs\it.exe returns it.exe
'    it.exe                returns it.exe
'    c:\                   returns NO_FILE
'    c:                    returns NO_FILE

On Error Resume Next

Dim intIndex As Integer
For intIndex = Len(strString) To 1 Step -1
    If ((Mid$(strString, intIndex, 1) = "\") Or (Mid$(strString, intIndex, 1) = ":")) Then
        fn_get_file_name = Mid$(strString, intIndex + 1)
        If (Len(Mid$(strString, intIndex + 1)) = 0) Then
            fn_get_file_name = "NO_FILE"
        End If
        Exit Function
    End If
Next intIndex

'no ":" or "\" found so return entire string
fn_get_file_name = strString

End Function
You must have access to the file across a UNC path at the very least - an image control cannot obtain display an image using an http hyperlink as the source. Can you reach the image path across the network?
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
How about adding code to your Report.Open event that moves the .bmp from the server location to a standardized location on the user's hard drive, then display the report where the .ImageControl.Picture = {local path/file} ?

Hope this helps.
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

You *might* be able to get away with using a web browser control to display the image, but I haven't tested this so I have no idea if it will work or not.
tmcdonalAuthor Commented:

Yes, I can access the files when I'm connected to this network.  What's stored in the DB field is:

If I point a browser to:
the image shows.  However, I'm looking generate this as a report.  I think this answers your question.
Ah ok, in that case, you just need to use the code in the first link, but modify it slightly to append "\\box\files\" to the path stored in the DB, and set the image's Picture property to the combined string.
tmcdonalAuthor Commented:
This seems like a cumbersome way to have to do it, where it "makes sense" that I should be able to generate the actual picture.  However, I see what you're getting at here, but admittedly, my VB experience is limited.  Could you give me an example?  
shanesuebsahakarnConnect With a Mentor Commented:
Just to combine the example from the article you linked to with your situation - if your report is bound to the table, the document path is in the txtPath text box and your image control is called imgSignature, you would just need this line of code in the OnFormat event of the report's detail section:

Me!imgSignature.Picture = "\\box\files\" & Me!txtPath
tmcdonalAuthor Commented:

I agree that's how I would expect it to work, but it doesn't seem to.  I went back to the MS example and can easily get it to work with any local picture, i.e.:
Me!Image0.Picture = "C:\WINDOWS\FeatherTexture.bmp"
Thinking maybe this has something to do with the network, and trying to simplify the problem, I put a small BMP at: 
which I (and you) can open via a browser, and changed the line to read:
Me!Image0.Picture = "//"
When I preview the report, I receive an error:
Run-Time Error '2200'
Microsoft Access cannot open the file:
This is the same error I receive across the client network.  Any thoughts?
tmcdonalAuthor Commented:
Maybe copying it locally is the only way.  Let me play with this and I'll see what happens.  Thanks.
You won't be able to open the file via an internet address as you have above. This will work:

That opens a file on somepc, in a share called sharename. But this won't work:

since this tries to open a file on the website

You probably need to make sure as well that you are using the correct / character. Filenames are separated by \, instead of /. I *think* the different slashes will be correctly resolved, but I couldn't swear to that.

Are you able to access the file via a UNC path, i.e. \\computername\sharename\filename.bmp ?
This is how I have done it for one of our Reports,
I used the IMAGE , not the Unbound object Frame NOR the Bound object Frame
The Image properties is:

Picture Type:Linked

Another way to do it is to do an HTML page,
put your image on your server,
and point to your URL, for example
src="" width="450" border="0">

then make a printout from the web page.

tmcdonalAuthor Commented:
I misunderstood what was meant by a UNC path.  I can aceess the image via \\computername\sharename\filename.bmp, which I thought meant I had access, but in fact, I don't have access to the file itself.  It appears to me that a web control will be the way to go with this.  Both shanesuebsahakarn and jimhorn answered my question corectly in different ways if I had UNC path access, so I split the points.  Thanks anyway - I learned a bit on this one..
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.