Solved: Product Picture Issue

Hello Everyone,

I need your help with an issue regarding showing my products picture on a user form after its been selected from a combo box. I've attached a sample of the database so you'll have a better understanding of what I'm about to describe. On my table 'Tbl_Prods' I have four fictional products. I've added to bitmap images to two of these products. I would like these pictures to appear on the user form when selected from the drop down list. I'm not sure what I'm doing wrong, but I'm running into a problem when I add the Tbl_Prod table to the query. When the Tbl_Prod table is added to the query the form will show the correct picture but will no longer let me add new data to the table called Tbl_Estimates. Can anyone help me with this?
Thank you in advance for your assistance.

My apologies, here is the attachment.

I downloaded your zip. The database inside is in ACCDB format. If I get time later, I will fire up a machine with Access 2007 to look at it.

Meanwhile, if you're not using ACCDB-only features, consider converting the sample db to the older MDB format for sharing on this forum. I think you might get a wider response. A lot of folks are still running older Access versions; your ACCDB requires Access >= 2007.

Did you forget to include the form which has the image control? I only see one form, "Form1", and it has neither the form nor the query you described.

This is the working version. When I add the Tbl_Prod to the query and show a relationship b/w Tbl_Estimates and Tbl_Prod the form won't show the prod pic or allow me to add additional entries. I did add a field on the form for the pic (bitmap image). In the query I tried using the ProdPic field from both Tbl_Prod and Tbl_Estimates - neither worked. Any suggestions?
Thank you.

I prefer not to store images in an Access database, so I took a different approach. The attached Zip contains an MDB format database and 2 BMP files. Extract them to the same folder, open the database, then Form2. I tested it with both Access 2003 and 2007; it seems fine to me.

I changed the structure of Tbl_Prods to remove the OLE field and add a file_name field for the name of the picture file. And Form2 includes an image control rather than a bound OLE control, which is what I think you were using.

In addition to avoiding file bloat from storing images in the database, this approach avoids the problem you had where the records in your form became read-only when displaying the images.

modLoadImage provides the function to load an image file (doesn't have to be BMP; I've also used JPEG and GIF images) into the form's image control.

[vba]Option Compare Database
Option Explicit

Public Function LoadImage(ByRef img As Image, _
ByVal pstrFile As String)

Dim strFile As String
If Len(pstrFile) > 0 Then
strFile = CurrentProject.Path & Chr(92) & pstrFile
End If
If Len(Dir(strFile)) > 0 Then
img.Picture = strFile
Else
img.Picture = "(none)"
End If
End Function[/vba]
Then I use this procedure in the form's code module to call the LoadImage() function.

[vba]Private Sub RefreshProductPic()
Dim strFileName As String
Dim strCriteria As String
strFileName = vbNullString
If Len(Me.ProdID & vbNullString) > 0 Then
strCriteria = "ProdTypeID=" & Me.ProdID
'Debug.Print "strCriteria: '" & strCriteria & "'"
strFileName = Nz(DLookup("file_name", "Tbl_Prods", strCriteria))
End If
'Debug.Print "strFileName: '" & strFileName & "'"
Call LoadImage(Me.Prod_Pic, strFileName)
End Sub[/vba]
And RefreshProductPic() is called from the form's On Current event ...

[vba]Private Sub Form_Current()
RefreshProductPic
End Sub[/vba]
... as well as from the ProdID control's After Update event (so that the displayed image will change to match an updated product ID value):

[vba]Private Sub ProdID_AfterUpdate()
RefreshProductPic
End Sub[/vba]
If you prefer to store the image files in a different folder, rather than in the same folder as the database, you can modify LoadImage() to give it your folder path:

[vba]'strFile = CurrentProject.Path & Chr(92) & pstrFile
strFile = "C:\my_product_pics\" & pstrFile[/vba]

I don't know what to say other than of course "Thank You!!" This is great!! Thank you so much for all of your great help!! I hope I can one day return the favor. You're Great!
Thank you, again!!

You're welcome, mtnco80517. Since we're both in Colorado, I'll let you buy me a beer if we ever cross paths. :beerchug:

Gladly! Just one more issue for you. I copied your code into my '07 DB and can't get the pics to appear on my form. Here's the code:
Option Compare Database
Option Explicit
Private Sub Form_Current()
RefreshProductPic
End Sub
Private Sub RefreshProductPic()
Dim strFileName As String
Dim strCriteria As String
strFileName = vbNullString
If Len(Me!BoxType & vbNullString) > 0 Then
strCriteria = "BoxTypeID=" & Me!BoxType
'Debug.Print "strCriteria: '" & strCriteria & "'"
strFileName = Nz(DLookup("file_name", "tblBoxTypes", strCriteria))
End If
'Debug.Print "strFileName: '" & strFileName & "'"
Call LoadImage(Me!Prod_Pic, strFileName)
End Sub
Private Sub ProdID_AfterUpdate()
RefreshProductPic
End Sub

This code is in a Class Module called Form1, the name of my form. The modLoadImage is in its own module. The code compiles without any errors, just no pics on the form. Any suggestions?
Thank you again.

The MDB version I created runs correctly on your system, right?

You said the code compiled without errors in your new version. I'll have to assume you also get no runtime errors when the code is used.

How are your VBA debugging skills? Remove the quotes from the beginning of the Debug.Print lines. Then look in the Immediate Window to see what you're getting for strCriteria and strFileName as you navigate through the form to a row for which a picture should be displayed. (I use the Ctrl+g keystroke combination to go to the Immediate Window.)

If the values of both those variables look reasonable, double-check the values passed to the LoadImage function by executing this line in the Immediate Window:

[VBA]? Me!Prod_Pic, strFileName[/VBA]

If those efforts don't pinpoint the problem, set a temporary break point in RefreshProductPic and step through the code line by line with the F8 key. (An alternative is to add a permanent break point by including the keywork Stop on a line by itself; of course then you need to remove it after you've finished debugging.) Make sure the proper lines are executed.

As you're stepping through the code, you can check the value of any variable in the Immediate Window:

[VBA]? VariableName[/VBA]

Sorry that was kind of long-winded. I can't see any errors in the code you posted. My guess would be, since you've done some re-naming, somewhere the names don't match up properly. But that's just a guess.

Good luck,
Hans

Hi Hans,
Here's what I get when I rung the Debug.Print lines through the Immediate Window:
Debug.Print "strCriteria: '" & strCriteria & "'"
strCriteria: ''
Debug.Print "strFileName: '" & strFileName & "'"
strFileName: ''
When I tried checking the values passed to the LoadImage Function

? Me!Prod_Pic, strFileName

using the Immediate Window I receieved a Compile Error stating "Variable not yet created in this context"
I received this same message when I ran this in the mdb version as well. Any suggestions? Looks like I'm going to buy you a case of beer...

I made a mistake about Me!Prod_Pic ... "Me" is only meaningful in the context of the running form code, so I should have to told you to use it when the form's code is in break mode (which you trigger with the break point).

As for those Debug.Print lines, I don't know what's going on. Both variables look like empty strings. Was the the output from the running code, or did you type or paste the Debug.Print statements into the Immediate Window. If the latter, the same issue applies as for "Me".

However I think we have a bigger problem here. I was operating on the assumption that the MDB I created worked properly on your system, and that your problem was about adapting that approach to your other database. But if the MDB does not work correctly, I've just been wasting my time.

The MDB works properly I'm just having difficulty getting the pic to display on the other DB (accdb format). I'll keep looking and get back to you.
Thanks for your help!