Display Picture Based on Database Field

[BlarneyFarneyBegoneyee]

Hello all. I am trying to display a picture on each record based on a field in the record. I have the following code:

Private Sub Form_Current()
...
If IsNull(Me![CaseID]) Then

Else
CaseStyle = "SELECT From Case Style Where CaseStyle = CaseID"
Me!ItemImage.Picture = ("\JunkboxDB\Images\") + CaseStyle + (".jpg")
End If

Which is real nice. Only problem is that it doesn't work!

Basically all I want to do is concatenate a string to use as a link to an image object (not embedded) for each record in the Database. This would change based on the CaseID which is a number (the key for that table) with a name in the next field of each record.
Can anyone give me a pointer.
Thanks,
Kevin

[CJ_London]

1. your select statement is not selecting anything
2. once you have corrected that, you then need to go and get the record - perhaps consider using dlookup
3. don't think .picture is relevant for vba - it as a vb property
4. you path is incorrect
5. you use & not + to concatenate strings
6. no need for brackets in your concatenation.

you say

I am trying to display a picture on each record based on a field in the record

assuming your record has been loaded all you need to do is set the controlsource of the image control to the name of your field with the image (CaseStyle?) with the correct path if not included in this value. No code required

[June7]

The + character will concatenate strings but it acts differently than & character if strings of digits are concatenated with a number (will do arithmetic, e.g. "1"+"2"+3 returns 15) or any have Null.

Before Access 2007 it was required to use VBA to dynamically display images. Yes, this involves Picture property. https://support.microsoft.com/en-us/kb/285820

The path needs drive letter designation or UNC path.

Can't use SELECT sql in VBA like that. SELECT statements are used to set recordset object variable, not a simple text variable. VBA would be:

CaseStyle = DLookup("CaseStyle", "[Case Style]", "CaseID = " & Me!CaseID)

Instead of using VBA to retrieve the CaseStyle value, why not include that data in the form RecordSource? Just don't use INNER join in the SQL statement.

[BlarneyFarneyBegoneyee]

I changed it to this:

Else
    CaseStyle = DLookup("Case Style", "[Case Style]", "CaseID = " & Me!CaseID)
                             ^^^^^^     ^^^^^^^         ^^^^
                                  1           2            3
1  Name of the Field from which the text will come...
2  Name of the Table from the which the record comes...
3  Number of the Case for this record...

    Me!ItemImage.Picture = ("\\Mac\JunkboxDB\Images\") & CaseStyle & (".jpg")
End If

But now I get

run-time error '2001':
"You canceled the previous operation"
( i think this was because I had 'CaseStyle' as the name of the field when it was actually 'Case Style')
Now I get run-time error '3075'
"Syntax error (missing operator) in query expression 'Case Style'

Trying to get the CaseID=>Case Style into CaseStyle (as text) so I can create a text string that will be the link in Image Control.
Is there a way to do this simply?
Thank you,
Kevin

[June7]

There is if you upgrade to Access 2007 or up. Image control was given ControlSource property. No VBA is needed.

Perhaps using . instead of ! to reference the image control name will work.

Me.ItemImage.Picture

I didn't have 2003 long enough to ever need this code so have no direct experience with it.

[BlarneyFarneyBegoneyee]

There is if you upgrade to Access 2007 or up. Image control was given ControlSource property. No VBA is needed.

Yes, I could upgrade to 2007 but I use Mac now. This is being used in Parallels for my junk box parts database which I created in ~2000.
In any case this works...

If IsNull(Me![CaseID]) Then
Me.CaseImage.Visible = False
Else
CaseStyle = DLookup("[Case Style]", "[Case Style]", "CaseID = " & Me!CaseID)
Me!CaseImage.Picture = ("\\Mac\JunkboxDB\Images\") & CaseStyle & (".jpg")
Me.CaseImage.Visible = True
End If

Notice the [] around Case Style (the form field). Images change dynamically when switching records. It's all good.

Thank you for all your help June7.

[June7]

Advise not to use spaces and special characters/punctuation (underscore is exception) in naming convention.