Print hyperlink images in a report?

Posted on 2004-09-09
Medium Priority
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

How to embed a bitmap object in a report at run time in Access 2002

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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 66

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.
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.


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 1000 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 66

Accepted Solution

Jim Horn earned 1000 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 = "//home.swbell.net/tmcdonal/tmp/Logo-cropped.bmp"
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 www.someplace.com.

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="http://c.mii.instacontent.net/adt/img/directeducation/email/advance/images/advance_01.gif" 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

801 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