Display a picture based on a selection in a ComboBox

[Mariner81]

I am trying to display a picture based upon the selection of a combobox.

I have a table with several fields the last of which is called SeaStatePics which is an attachment field containing pictures (.png files). I have tried using the Combo Box AfterUpdate event as such to set the Picture property using DLookUp as follows.

Private Sub BeaufortID_AfterUpdate()
    Me.imgBeaufort.Picture = DLookup("SeaStatePics", "tblBeauFort", "BeaufortID = " & BeaufortID)
End Sub

I end up with Run Time Error 2220 telling me that Access was unable to open the specified file. The file is being properly identified by DLookup.

Any thoughts?

[June7]

I would set Image control ControlSource instead of Picture property. Review https://www.accessforums.net/showthread.php?t=73766

Include SeaStatePics as a column of combobox and DLookup is not needed. Can have expression in ControlSource and no VBA needed. Reference combobox column by index. Column 3 is index 2.

=BeaufortID.Column(2)

[Mariner81]

I would set Image control ControlSource instead of Picture property. Review https://www.accessforums.net/showthread.php?t=73766

Include SeaStatePics as a column of combobox and DLookup is not needed. Can have expression in ControlSource and no VBA needed. Reference combobox column by index. Column 3 is index 2.

=BeaufortID.Column(2)

I Played around with things and got to work using the code below. I was hoping for something more simple and eloquent but it works. It requires the pictures stay in a folder in the Application folder and that the names of the image files don't change. They shouldn't there is only 13 of them. I may in the future play with this some more and see if I can devise something more robust but this works for now.

Thanks for your suggestions I will play around with them.

'------------------------------------------------------------
' NAME: cboBeaufortID_AfterUpdate()
' PORPOSE: Shows pictures in an image control based on ComboBox selection.
' REQUIRES: A foler called Beaufort to be olcated in the applications folder
' CREATED BY: Andrew Dulavitz   (adulavitz@hotmail.com)
' DATE: August 2023
'-------------------------------------------------------------
Private Sub cboBeaufortID_AfterUpdate()
On Error GoTo cboBeaufortID_AfterUpdate_Err


Dim strImagePath As String
Dim strDatabasePath As String
Dim intSlashLocation As Integer
Dim strResult As String
Dim strFolderName As String
Dim strForce As String


    ' Get the value of the 2nd field in the Combobox
    ' it contains the same Force No as the picture names,
    strForce = Me.cboBeaufortID.Column(1)
    
    ' Exit if we have  a null value
    If IsNull(strForce) Then
        Exit Sub
    End If
    
    ' Set the folder name
    strFolderName = "Beaufort"
    
    'Build the Path to the image file
    strDatabasePath = CurrentProject.FullName
    intSlashLocation = InStrRev(strDatabasePath, "\", Len(strDatabasePath))
    strDatabasePath = Left(strDatabasePath, intSlashLocation)
    strImagePath = strDatabasePath & strFolderName & "\Force " & strForce & ".png"
    
    Me.imgBeaufort.Picture = strImagePath


cboBeaufortID_AfterUpdate_Exit:
    Exit Sub


cboBeaufortID_AfterUpdate_Err:
    MsgBox Err.Number & " " & Err.Description
    Resume cboBeaufortID_AfterUpdate_Exit


End Sub

[June7]

What would you think could be more 'robust'? If number of images will not grow much, could save them in table Attachment field and pull them from there when needed.

[moke123]

If all your images are in the Beaufort folder in the Application folder,
you could do something like this with an image control and a value list combo box.

Private Sub Form_Load()

     Me.imgBeaufort.Picture = ""
     Me.cboBeaufortID = ""

    sGetImages
    
End Sub



Private Sub cboBeaufortID_AfterUpdate()


    Me.imgBeaufort.Picture = Me.cboBeaufortID.Column(1)
    
End Sub



Private Sub sGetImages()
   'add image names and paths to the combo box

    Dim fol As Object, fil As Object
    Dim fso As Object

    Set fso = CreateObject("Scripting.FileSystemObject")
    
    Me.cboBeaufortID.RowSource = ""

    Dim strBaseFolder As String

    strBaseFolder = CurrentProject.Path & "\Beaufort"

    Set fol = fso.GetFolder(strBaseFolder)

    For Each fil In fol.Files
        Me.cboBeaufortID.AddItem fil.Name & ";" & fil.Path
    Next
    
    Set fso = Nothing

End Sub

[Mariner81]

What would you think could be more 'robust'? If number of images will not grow much, could save them in table Attachment field and pull them from there when needed.

I tried the Attachment field and was getting run time error 2220. Not sure what was going on because the file name was correctly identified but would not open.

Thank you though for responding

[Mariner81]

If all your images are in the Beaufort folder in the Application folder,
you could do something like this with an image control and a value list combo box.

Private Sub Form_Load()

     Me.imgBeaufort.Picture = ""
     Me.cboBeaufortID = ""

    sGetImages
    
End Sub



Private Sub cboBeaufortID_AfterUpdate()


    Me.imgBeaufort.Picture = Me.cboBeaufortID.Column(1)
    
End Sub



Private Sub sGetImages()
   'add image names and paths to the combo box

    Dim fol As Object, fil As Object
    Dim fso As Object

    Set fso = CreateObject("Scripting.FileSystemObject")
    
    Me.cboBeaufortID.RowSource = ""

    Dim strBaseFolder As String

    strBaseFolder = CurrentProject.Path & "\Beaufort"

    Set fol = fso.GetFolder(strBaseFolder)

    For Each fil In fol.Files
        Me.cboBeaufortID.AddItem fil.Name & ";" & fil.Path
    Next
    
    Set fso = Nothing

End Sub

Thank you for the detailed response. You have given me some more ideas to work with. I Appreciate it.

[June7]

If you want to save images in Attachment field, using them can be done with two methods:

1) joining tables in query

2) VBA extracts image to folder and then sets ControlSource or Picture property to that file