Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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.
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

609 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