Changing Image Based on Field Option

[Dancinrain]

I am rather new to Access, and I have previewed other forums for guidance (Like this one) in attempt to fulfill my idea. Unfortunately, I have been receiving errors regardless of my approach.

I have renovated the Asset Inventory Access 2010 template to track assets, each record having a combo listing with that item's [Country]. I have a continuous form that neatly lists all the records as it seems that the image won't display on a datasheet form. This is the code in the Form_Current().

Private Sub Form_Current()
On Error GoTo ErrHandler
    If Me![Country] = "AS" Then
        Me![ImageFrame].Picture = "\\FullPath\Australia.jpg"
     ElseIf Me![Country] = "DK" Then
        Me![ImageFrame].Picture = "\\FullPath\Denmark.jpg"
     Else
         Me![ImageFrame].Picture = "\\FullPath\Other.jpg"
 End If
ErrHandler:
     MsgBox "Error detected, error # " & Err.Number & ", " & Err.Description, vbOKOnly, "Error"
End Sub

[ImageFrame] has the following properties
PictureType=Lin ked
Picture=(none)


This code results in the image control populating the Other.jpg for all records and an error message "Error Detected, error #0," Would anyone be able to point out to me the possible error that is occurring?

Additional Information:If it helps. I do have a tbl_Countries that contains the fields [Country] and [CountryAbbrevia tion]. I use this to populate the [tbl_Assets].[Country] field. If it is better to add an additional field in tbl_Countries to hold the full path, please let me know how to successfully implement this alternative. Thanks in advance to whomever helps!

[NeoPa]

Does the code stop at any point? When it errors for instance?

The code logic seems fine so I expect something isn't behaving the way you expect it to. We'd need to look deeper to determine what that is.

[Dancinrain]

Well, since the code appeared to be fine, I decided to delete and re-add all the coding under the On Current event in my frm_AssetList, and for some reason the error isn't showing up anymore; however, the same Other.jpg is appearing for every record. I know that at least five different countries should be appearing within the list of assets.

On a whim I also added the same code to On Current in the frm_AssetDetail s subform with its own ImageFrame. Surprisingly, the image changes like it should when the subform is opened according to the asset's country. In addition, when I save and close the frm_AssetDetail s subform, all the images in the frm_AssetList switch to the latest previewed asset's country flag. Seems like I still need to figure out why all the records displayed in frm_AssetList change to the same flag like this??

Thank you, by the way, lending support/guidance!

[NeoPa]

OK. I assume frm_AssetList is the form we were talking about at the start.

If it's showing as a continuous form, as would be indicated by the name, then what you're seeing is standard behaviour (See Why Values in Unbound Form Controls do not Persist).

Now I know that your form is probably used as a continuous form it makes sense that any individual record wouldn't reflect the flag correctly. Do you see the correct flag for the record you click on though? Or is that always Other.Jpg?

[Dancinrain]

Sigh, you are correct in that the flag changes based on which record I have clicked. I've read your linked post and I believe I understand why this is occurring. Do you, by chance, have an alternate suggestion I could attempt? Perhaps storing a bitmap image (or the relative path??) in the tbl_Counties then using a bound object frame?

[NeoPa]

You're clearly not stupid are you. Some very sensible ideas there. Unfortunately, I don't know of a control or field type that allows you to do that.

You've read the linked article so you know about using the Format event if you're in a Report, but that concept doesn't work with forms. Forms have no Format event as such. A shame, but that's what we're working with I'm afraid.

If I do ever find a way around it you can be sure that I'll update the linked article with it.

[jforbes]

This looked like fun, so I mocked up something similar to what you are doing.
I created a table and added a column called [Country] and populated it with a couple records and included the Countries "AU" and "DE". Then I downloaded some map images (.png) of the Countries. I stored the images here:

C:\Users\Desktop\Bytes\ImageTest\AU.png
C:\Users\Desktop\Bytes\ImageTest\DE.png

I then added the following code to a Code Module:

Option Compare Database
Option Explicit

Public Function getCountryImagePath(ByRef vCountry As Variant) As String

    Dim sPath As String
    Dim sImagePath As String
    
    If Len(vCountry) > 0 Then
        sPath = "C:\Users\Desktop\Bytes\ImageTest\"
        sImagePath = sPath & vCountry & ".png"
        
        If fileExists(sImagePath) Then getCountryImagePath = sImagePath
    End If
    
End Function

    'fileExists
Public Function fileExists(ByVal strFile As String, Optional bFindFolders As Boolean = False) As Boolean
    'Purpose:   Return True if the file exists, even if it is hidden.
    'Arguments: strFile: File name to look for. Current directory searched if no path included.
    '           bFindFolders. If strFile is a folder, FileExists() returns False unless this argument is True.
    'Note:      Does not look inside subdirectories for the file.
    'Author:    Allen Browne. http://allenbrowne.com June, 2006.
    Dim lngAttributes As Long

    'Include read-only files, hidden files, system files.
    lngAttributes = (vbReadOnly Or vbHidden Or vbSystem)

    If bFindFolders Then
        lngAttributes = (lngAttributes Or vbDirectory) 'Include folders as well.
    Else
        'Strip any trailing slash, so Dir does not look inside the folder.
        Do While Right$(strFile, 1) = "\"
            strFile = Left$(strFile, Len(strFile) - 1)
        Loop
    End If

    'If Dir() returns something, the file exists.
    On Error Resume Next
    fileExists = (Len(Dir(strFile, lngAttributes)) > 0)
End Function

I then created a Continuous Form based of the New Table. On the New Form I placed an ImageControl and set it's ControlSource to:

=getCountryImagePath([Country])

Then ran the Form and it worked great. The correct Image for each Country was displayed on each row.


Maybe this approach will work for you.

[NeoPa]

Great idea JForbes. I think I'll add something of the sort into my article when I get the chance/time.

[Dancinrain]

Wow! I've added the module and I am very pleased with the results. I am especially happy that that your code allows for easy additions of new countries instead of nesting if/then statements for each country.

Thank you very much NeoPa and JForbes for your assistance! =)