Solved

Print hyperlink images in a report?

Posted on 2004-09-09
13
302 Views
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
http://support.microsoft.com/default.aspx?scid=kb;EN-US;285820

How to embed a bitmap object in a report at run time in Access 2002
http://support.microsoft.com/default.aspx?scid=kb;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.

-Tom
0
Comment
Question by:tmcdonal
  • 5
  • 5
  • 2
  • +1
13 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
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?
0
 
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.
-Jim
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
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.
0
 
LVL 1

Author Comment

by:tmcdonal
ID: 12019936
shanesuebsahakarn,

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

If I point a browser to:
http://box/files/docs\12345.bmp
the image shows.  However, I'm looking generate this as a report.  I think this answers your question.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
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.
0
 
LVL 1

Author Comment

by:tmcdonal
ID: 12020025
jimhorn,
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?  
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 41

Assisted Solution

by:shanesuebsahakarn
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
0
 
LVL 65

Accepted Solution

by:
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
Doevents

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
0
 
LVL 1

Author Comment

by:tmcdonal
ID: 12021530
shanesuebsahakarn,

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:
http://home.swbell.net/tmcdonal/tmp/Logo-cropped.bmp
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:
'//home.swbell.net/tmcdonal/tmp/Logo-cropped.bmp'.
This is the same error I receive across the client network.  Any thoughts?
0
 
LVL 1

Author Comment

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

Expert Comment

by:shanesuebsahakarn
ID: 12021580
You won't be able to open the file via an internet address as you have above. This will work:
\\somepc\sharename\myimage.bmp

That opens a file on somepc, in a share called sharename. But this won't work:
\\www.someplace.com\myimage.bmp

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 ?
0
 
LVL 27

Expert Comment

by:jjafferr
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:\\Server01\templates\Enquiry\Almosawe_Header.gif
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.

jaffer
0
 
LVL 1

Author Comment

by:tmcdonal
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..
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now