Need help with jpg.

[robertoathome]

We use Access 2002.
I have a table that contains a column with hiperlinks to jpgs stored on a disk on the network.
I created a form that shows all the fields in the table including the pictures. If I use and OLE object with bmp images all is good. The problem is that I can't use bmp. If I try to display a jpg on an image box/ bound or unbound object box / text box or.... nothing comes up. I put a bmp on the first 2 records and they work; as move to the 3rd the other records no jpg is displayed. I'm not good with coding but I read somewhere that I can create a macro or code that can be a workaround.
Can any1 help me? It's really getting to me :) I've been reading for days on the syntax to use in coding but my company doesn't want me to spend all this time "training myself"
Can I use a macro or do I need to write a code?
If the workaround needs a button to open the pic in the form, ..not the ideal but...so be it.

Table name= table1
Form name= ViewRecord
field name= Photo

Thanks!

[Denburt]

O.K. it's bee a while since I fooled with linked pics but you should be able to use the on Current event of the form.

Private Sub Form_Current()
Rename the folowing Me!myLink with the field that hold the links
If Not IsNull(Me!MyLink) And Len(Me!MyLink) > 0 Then
'Me!Image0 should be the image placeholder also replace the myLink as stated above
Me!Image0.Picture = Me!MyLink
End If
End Sub
 

This should get you close any issues let me know.

[robertoathome]

OK, something is working but not all.

1) I'm assuming I need to use the first type of image holder, not the unbound or bound OLE object.
2) when I run the code I get an error that says"

"run-time error '2220':
Microsoft access can't open the file 'T:\SHARED FILES\Administration\Masters and Molds Photos\040F-6F222032-TL.JPG#T:\SHARED FILES\Administration\Masters and Molds Photos\040F-6F222032-TL.JPG#.

Basically it dubles the link I have in the table1 and adds a ' # ' after it.
If I go to debud I get the following highlighted
' Image42.Picture = Photo'

Here's the code I used:

Private Sub Form_Current()
If Not IsNull(Photo) And Len(Photo) > 0 Then
Image42.Picture = Photo
End If
End Sub

THANKS!

O.K. it's bee a while since I fooled with linked pics but you should be able to use the on Current event of the form.

Private Sub Form_Current()
Rename the folowing Me!myLink with the field that hold the links
If Not IsNull(Me!MyLink) And Len(Me!MyLink) > 0 Then
'Me!Image0 should be the image placeholder also replace the myLink as stated above
Me!Image0.Picture = Me!MyLink
End If
End Sub
 

This should get you close any issues let me know.

[robertoathome]

In doubt of the syntax I aslo added the " Me!" :

Private Sub Form_Current()
If Not IsNull(Me!Photo) And Len(Me!Photo) > 0 Then
Me!Image42.Picture = Me!Photo
End If
End Sub

"Photo" is the column in my table that holds the links to the pictures.

[Denburt]

??? How large are your pics?

I tried tried it with some windows sample pics and hadd no problem what so ever. Did you change any of the properties on the control? I simply placed the control added a dummy pic as it asked for 1 but when it opens it opens whith my first pic from the bd.
I tried it using the field photo and didn't have an issue but I don't like using any 1 word for a fieldname since you never know when or if MS has designated it as a property, method, or function.

MyPhoto as Text Field
C:\Documents and Settings\All Users\Documents\My Pictures\Sample Pictures\Blue Hills.jpg
C:\Documents and Settings\All Users\Documents\My Pictures\Sample Pictures\Sunset.jpg
C:\Documents and Settings\All Users\Documents\My Pictures\Sample Pictures\Water Lilies.jpg
C:\Documents and Settings\All Users\Documents\My Pictures\Sample Pictures\Winter.jpg

Each one in a separate record and it went smoothly.

The following site is using a class module for his example and maybe this might be a resolution for you. At this point i don't know what else I can offer.

Image Sample DB

[Denburt]

UGH Just reread your Post you are using Hyoperlink as a field to hold your pics that explains it!

If you have to use a hyperlink field then you will need to parse it.

In your current code before you set the picture.

Ytou will need to use something to the effect of:

instr(Me!'Photo,"#")
if instr>0 then
strPhoto = left(me!Photo,instr)
End if
 

A Hyperlink field stores the link and the text you want displayed. If you don't enter text to display it will display the link twice using # as a separator, as you posted.

[robertoathome]

BETTER news !
I don't "have to" use hyperlinks but it is easier than changing all my database to text.
With simple text the code in the "Photo" field actually now works! I had to change the allowable number of characterd in the field since the links are long.

I tried to add your second piece of code u gaver me to 'parse the link' but I get a syntax error no matter where I place the code. Below is where I 'think' you wanted me to insert it.

Private Sub Form_Current()
instr(Me!'Photo,"#")
if instr>0 then
strPhoto = left(Me!Photo,instr)
End If
If Not IsNull(Me!Photo) And Len(Me!Photo) > 0 Then
Me!Image42.Picture = Me!Photo
End If
End Sub

The debugger stops at the very first line and makes the new code part red.

I appreciate your help!

[robertoathome]

BETTER news !
I don't "have to" use hyperlinks but it is easier than changing all my database to text.
With simple text the code in the "Photo" field actually now works! I had to change the allowable number of characterd in the field since the links are long.

I tried to add your second piece of code u gaver me to 'parse the link' but I get a syntax error no matter where I place the code. Below is where I 'think' you wanted me to insert it.

Private Sub Form_Current()
instr(Me!'Photo,"#")
if instr>0 then
strPhoto = left(Me!Photo,instr)
End If
If Not IsNull(Me!Photo) And Len(Me!Photo) > 0 Then
Me!Image42.Picture = Me!Photo
End If
End Sub

The debugger stops at the very first line and makes the new code part red.

I appreciate your help!

UGH Just reread your Post you are using Hyoperlink as a field to hold your pics that explains it!

If you have to use a hyperlink field then you will need to parse it.

In your current code before you set the picture.

Ytou will need to use something to the effect of:

instr(Me!'Photo,"#")
if instr>0 then
strPhoto = left(me!Photo,instr)
End if
 

A Hyperlink field stores the link and the text you want displayed. If you don't enter text to display it will display the link twice using # as a separator, as you posted.

[Denburt]

Glad you got it working :) sorry for my sloppy coding I just looked at it and it WAS bad. My appologies it should have looked like the following. I did test this one btw

Private Sub Form_Current()
'Always declare your variables
Dim inInt as integer
Dim strPhoto as string
inInt = InStr(Me!Photo, "#")
If inInt > 0 Then
Debug.Print Left("MyPhoto.html#photo.html", inInt - 1)
strPhoto = Left(Me!Photo, inInt)
End If
If Not IsNull(Me!Photo) And Len(Me!Photo) > 0 Then
Me!Image42.Picture = Me!Photo
End If
End Sub
 

[robertoathome]

I'm sorry to bug you one more time but now at least the problem seems almost solved.
I still get this error:
Run-tme error '2220'
Microsoft Access can't open the file '#C:\Documents and Settings\ELIAS\Desktop\new\2002\040F-6F182424.jpg#'

The debug highlights:
Me!Image42.Picture = Me!Photo

It looks like the '#' still gets added in the string. :(

I don't get it. I tried links that work with using just text so they are working. If I click them I can open the pics. I also used local pics, in the same folder at the database. This is what I have as the first 2 links:
C:\Documents and Settings\ELIAS\Desktop\new\2002\040F-6F182424.jpg
or
T:\SHARED FILES\Administration\Masters and Molds Photos\040F-6F18D24.JPG

The pics are only 200kb

I tried to put nothing in the "text to display" and no changes.
Thanks!!

=================

Glad you got it working :) sorry for my sloppy coding I just looked at it and it WAS bad. My appologies it should have looked like the following. I did test this one btw

Private Sub Form_Current()
'Always declare your variables
Dim inInt as integer
Dim strPhoto as string
inInt = InStr(Me!Photo, "#")
If inInt > 0 Then
Debug.Print Left("MyPhoto.html#photo.html", inInt - 1)
strPhoto = Left(Me!Photo, inInt)
End If
If Not IsNull(Me!Photo) And Len(Me!Photo) > 0 Then
Me!Image42.Picture = Me!Photo
End If
End Sub
 

[robertoathome]

Now as my brain turns ( this thing is obsessing me! ) I thought maybe importing the data put something funky in the field so I created a fresh columb in the table an put a fresh link.
Same problem which alternates with the link getting doubled.

The I put an "NA" in the 'text to display' field of the hyperlink and the error now is:

Run-tme error '2220'
Microsoft Access can't open the file 'NA#C:\Documents and Settings\ELIAS\Desktop\new\2002\040F-6F182424.jpg#'

[Denburt]

O.K.

Debug.Print Left("MyPhoto.html#photo.html", inInt - 1)
strPhoto = Left(Me!Photo, inInt-1)
 

The debug.print statement in the VBA window will show you the results in the immediate window. In the VBA window go to the View Menu then click on immediate window (Ctrl G) you should see it show up.

I had that in while testing and if you look closely the debug statement had a minus 1 after the variable inInt that will remove the trailing # symbol. The above should resolve your issue. Good luck let me know how it works.

[robertoathome]

Well, I swear, if some1 asked me now to rate my intelligence I'd be very crititcal....and confused :)

I'm still getting the same error.

I opened the 'immediate window' and the first time it showed a few lines with " "myphoto" or similar.
Now nothing, only return lines, empty.
I added the - before the 1 :

Private Sub Form_Current()
'Always declare your variables
Dim inInt As Integer
Dim strPhoto As String
inInt = InStr(Me!Photo, "#")
If inInt > 0 Then
 
Debug.Print Left("MyPhoto.html#photo.html", inInt - 1)
strPhoto = Left(Me!Photo, inInt - 1)
 
End If
If Not IsNull(Me!Photo) And Len(Me!Photo) > 0 Then
Me!Image42.Picture = Me!Photo
End If
End Sub
 
 

Also noticed that you have

Debug.Print Left("MyPhoto.html#photo.html", inInt - 1)

Shouldn't it be Me!Photo and capital P after # ?

How about the space, on the first one you have
inInt - 1

on the following line
inInt-1

I just I'm just reaching :)

If I'm understanding correctly the code is only trying ot take the initial #. There is one also at the end of the hyperlink.

The time you're taking is immensly appreciated.

[Denburt]

The code I supplied will pick up the first part of the link, I take it that this would be the text to display so if that isn't the link my bad. Slight adjustment needs to be made.

The I put an "NA" in the 'text to display' field of the hyperlink and the error now is:
Run-tme error '2220'
Microsoft Access can't open the file 'NA#C:\Documents and Settings\ELIAS\Desktop\new\2002\040F-6F182424.jpg#'

Also my debug statement was just parsing a string I wasn't trying to tie it into your form or anything.
Debug.Print Left("MyPhoto.html#photo.html", inInt - 1)

Dim inInt As Integer
Dim strPhoto As String
inInt = InStr(Me!Photo, "#")
If inInt > 0 Then
    strPhoto = Mid(Me!Photo, inInt + 1, Len(Me!Photo) - inInt - 1)
End If
If Not IsNull(Me!Photo) And Len(Me!Photo) > 0 Then
    Me!Image42.Picture = Me!Photo
End If
 

[robertoathome]

Here I am again.
Same problem.

The # is still there. The code is reading from the "text to display" which is ok to me.

When I insert a new link in the table the code reads it twice and puts it twice in the len part. If I point the mouse on the last Len statement the tooltip shows a count of 142 instead of 71. Plus is still puts the ' # ' . This only happens when I insert a new link in my table.

What I also don't understand is why if I look at

strPhoto = Mid(Me!Photo, inInt + 1, Len(Me!Photo) - inInt - 1)
 

and point the mouse on strPhoto, it shows the correct link without the # but if I scroll down to the last line of code

Me!Image42.Picture = Me!Photo
 

and point the mouse to Me!Photo it shows the link with the #.


Aren't you happy to have decided to help me? :)

Current code:

Private Sub Form_Current()
Dim inInt As Integer
Dim strPhoto As String
inInt = InStr(Me!Photo, "#")
If inInt > 0 Then
    strPhoto = Mid(Me!Photo, inInt + 1, Len(Me!Photo) - inInt - 1)
End If
If Not IsNull(Me!Photo) And Len(Me!Photo) > 0 Then
    Me!Image42.Picture = Me!Photo
End If
End Sub
 

[Denburt]

Me!Photo is the field we are parsing.

Private Sub Form_Current()
Dim inInt As Integer
Dim strPhoto As String
inInt = InStr(Me!Photo, "#")
If inInt > 0 Then
    strPhoto = Mid(Me!Photo, inInt + 1, Len(Me!Photo) - inInt - 1)
    Me!Image42.Picture = strPhoto
else
If Not IsNull(Me!Photo) And Len(Me!Photo) > 0 Then
    Me!Image42.Picture = Me!Photo
End If
end if
End Sub
 

[robertoathome]

Ahh, fantastic! It' working great now. Thanks for your time and I hope someday, somehow to return the favor. If you ever need furniture design or cad drawing don't hesitate to let me know.
Thanks!

[Denburt]

Ahh, fantastic! It' working great now. Thanks for your time and I hope someday, somehow to return the favor. If you ever need furniture design or cad drawing don't hesitate to let me know.
Thanks!

Great glad it is working for you now.

Thanks for the offer and if you ever need a quick way of pulling info out of your cad drawings let me know as well, i'll be here.

[ADezii]

OK, something is working but not all.

1) I'm assuming I need to use the first type of image holder, not the unbound or bound OLE object.
2) when I run the code I get an error that says"

"run-time error '2220':
Microsoft access can't open the file 'T:\SHARED FILES\Administration\Masters and Molds Photos\040F-6F222032-TL.JPG#T:\SHARED FILES\Administration\Masters and Molds Photos\040F-6F222032-TL.JPG#.

Basically it dubles the link I have in the table1 and adds a ' # ' after it.
If I go to debud I get the following highlighted
' Image42.Picture = Photo'

Here's the code I used:

Private Sub Form_Current()
If Not IsNull(Photo) And Len(Photo) > 0 Then
Image42.Picture = Photo
End If
End Sub

THANKS!

The '#' you are seeing is part of the Hyperlink Address which may consist of up to 4 parts separated by the number ('#') sign as in: displaytext#address#subaddress#screentip. You are getting doubles because you are both navigating to the Hyperlink Address and dynamicallly loading the images at the same time. If Hyperlinks are giving you trouble, you can dynamically load graphic Images into an Image Control at run time in the Current() Event of your Form.

[AKdlm]

I know this thread is older, but my problem sounds very similar. I have a table that has a hyperlink field. I have one of my forms set up with a command button that is inserting hyperlinks into a textbox, which is then being stored into the previously mentioned table. This is all working just fine. The problem is with the report in which I want to display the images. I have a search command set up to pull a report with the corresponding images of that particular record number. It is pulling the report fine, except for the images. The error I get is Run-time error 2220 "Quality Feedback System can't open the file '#C:\documents and settings\test2.jpg#'. It is automatically adding they # before nad after the file path. This occurs no matter what I do. If I tell the report to pull the file path from just a text field (not a hyperlink field) the image displays just fine.

To correct this I tried putting an After Update Event, in my form that stores the hyperlink, to copy the text to a textbox and then store that in the table instead. The probelm is that it is storing the correct path, but it is storing it all as text, even the # before and after the file path.

I have this in the Details On Format Event of the report:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me!ImageFrame1.Picture = Me![ImageFilePath1]
End Sub

And it pulls from the table just fine, but the table is storing the hyperlink (which is adding the #'s and the beggining and end) or it is storing the After Update textbox with #'s as well.

ImageFrame1 is on the report and ImageFilePath1 is the name of the field in the table. They each have a 1 because there is a total of 4 possible images on each report. But once I figure it out with 1, it should be easy to apply it to all 4.

This has been driving me crazy!! Can anyone help with my report?

[ADezii]

I know this thread is older, but my problem sounds very similar. I have a table that has a hyperlink field. I have one of my forms set up with a command button that is inserting hyperlinks into a textbox, which is then being stored into the previously mentioned table. This is all working just fine. The problem is with the report in which I want to display the images. I have a search command set up to pull a report with the corresponding images of that particular record number. It is pulling the report fine, except for the images. The error I get is Run-time error 2220 "Quality Feedback System can't open the file '#C:\documents and settings\test2.jpg#'. It is automatically adding they # before nad after the file path. This occurs no matter what I do. If I tell the report to pull the file path from just a text field (not a hyperlink field) the image displays just fine.

To correct this I tried putting an After Update Event, in my form that stores the hyperlink, to copy the text to a textbox and then store that in the table instead. The probelm is that it is storing the correct path, but it is storing it all as text, even the # before and after the file path.

I have this in the Details On Format Event of the report:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me!ImageFrame1.Picture = Me![ImageFilePath1]
End Sub

And it pulls from the table just fine, but the table is storing the hyperlink (which is adding the #'s and the beggining and end) or it is storing the After Update textbox with #'s as well.

ImageFrame1 is on the report and ImageFilePath1 is the name of the field in the table. They each have a 1 because there is a total of 4 possible images on each report. But once I figure it out with 1, it should be easy to apply it to all 4.

This has been driving me crazy!! Can anyone help with my report?

Try:

Me!ImageFrame1.Picture = Replace(Me![ImageFilePath1], "#", "")

[AKdlm]

Thanks for the help thus far.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me!ImageFrame1.Picture = Replace(Me![ImageFilePath1], "#", "")
End Sub

I put this in the Format Event of the Details section of the report, replacing what I had. I now get the error "Compile error: Sub or Function not defined". It highlights the first line in yellow and the word replace in blue. Any ideas? Does it matter that my company is still using Access 97? (probably should've mentioned that, sorry).

Can we fix the problem at the source in the form? Instead of having a textbox = to exactly what the hyperlink field shows (which would include the #'s at both ends), can you have it equal to the hyperlink field minus the far left and far right characters or something?

Maybe that wouldn't work at all and we should just stay in the report. Thanks again for the help.

[AKdlm]

So I converted the database to 2007 to test this out just for kicks, and the replace statement you suggested works amazingly. I can't get it to work for multiple images, and now the insert hyperlink is adding a double file path in 2007 - meaning if the path is C:\pictures\image it is adding it as C:\pictures\imageC:\pictures\image so now the report won't pull up any new entries I make. This sounds like a whole other problem.

The biggest problem though is that I am adding this to an already existing database that is running on Access 97 and 2007 says some of the queries are too complex to run in 2007. So I really should worry about a solution in 97. Is there a similar Replace function in 97?

[ADezii]

So I converted the database to 2007 to test this out just for kicks, and the replace statement you suggested works amazingly. I can't get it to work for multiple images, and now the insert hyperlink is adding a double file path in 2007 - meaning if the path is C:\pictures\image it is adding it as C:\pictures\imageC:\pictures\image so now the report won't pull up any new entries I make. This sounds like a whole other problem.

The biggest problem though is that I am adding this to an already existing database that is running on Access 97 and 2007 says some of the queries are too complex to run in 2007. So I really should worry about a solution in 97. Is there a similar Replace function in 97?

Make a slight adjustment for Access 97:

If Not IsNull(Me![ImageFilePath1]) Then
  Me!ImageFrame1.Picture = Mid$(Me![ImageFilePath1], 2, Len(Me![ImageFilePath1]) - 2)
End If
 

[AKdlm]

Beautiful!!! ADezii, you have no idea how much this has plagued me.

That worked amazingly and I have it going for all 4 images on the report. It has made me aware of one more problem that maybe you can help with though. On my form where they initially add the hyperlink, I've enabled a command button to open the insert hyperlink menu. If they click the "use relative path for hyperlink" checkbox, then it will do the ..\folder\images\image843.jpg etc.

The first problem was trying to open this as a hyperlink, which you've corrected with removing the #'s at the beginning and end of the file path. But now if they use a relative path instead of the absolute path, when the report opens, Access tries to open "..\folder\images\image843.jpg" instead of the absolute path. And now since it is text and hot a hyperlink, it will not open the file.

So, is there a way to make sure all paths are saved as absolute paths? Or to disable relative paths? Maybe change what you gave me to turn relatives into absolutes also?

Thanks again, I so appreciate the help.

[ADezii]

Beautiful!!! ADezii, you have no idea how much this has plagued me.

That worked amazingly and I have it going for all 4 images on the report. It has made me aware of one more problem that maybe you can help with though. On my form where they initially add the hyperlink, I've enabled a command button to open the insert hyperlink menu. If they click the "use relative path for hyperlink" checkbox, then it will do the ..\folder\images\image843.jpg etc.

The first problem was trying to open this as a hyperlink, which you've corrected with removing the #'s at the beginning and end of the file path. But now if they use a relative path instead of the absolute path, when the report opens, Access tries to open "..\folder\images\image843.jpg" instead of the absolute path. And now since it is text and hot a hyperlink, it will not open the file.

So, is there a way to make sure all paths are saved as absolute paths? Or to disable relative paths? Maybe change what you gave me to turn relatives into absolutes also?

Thanks again, I so appreciate the help.

So, is there a way to make sure all paths are saved as absolute paths? Or to disable relative paths? Maybe change what you gave me to turn relatives into absolutes also?

Yes, instead of the Insert Hyperlink Dialog Box, open up the standard Office File Dialog, select a File, then copy it to a Field on a Form Bound to a Hyperlink Data Type. It will now be stored as an Absolute Path Hyperlink. Download the Test Database to see what I mean.