Print hyperlink images in a report?

Posted on 2004-09-09
Last Modified: 2012-05-05
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.

Question by:tmcdonal
  • 5
  • 5
  • 2
  • +1
LVL 41

Expert Comment

ID: 12019775
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?
LVL 65

Expert Comment

by:Jim Horn
ID: 12019792
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.
LVL 41

Expert Comment

ID: 12019796
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.
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.


Author Comment

ID: 12019936

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

Expert Comment

ID: 12019985
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.

Author Comment

ID: 12020025
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?  
LVL 41

Assisted Solution

shanesuebsahakarn earned 250 total points
ID: 12020086
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
LVL 65

Accepted Solution

Jim Horn earned 250 total points
ID: 12020135
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

Author Comment

ID: 12021530

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?

Author Comment

ID: 12021576
Maybe copying it locally is the only way.  Let me play with this and I'll see what happens.  Thanks.
LVL 41

Expert Comment

ID: 12021580
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 ?
LVL 27

Expert Comment

ID: 12023276
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.


Author Comment

ID: 12059731
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..

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question