Dynamically Switch Images on a Microsoft Access Form

My example database – a recipe database – allows the user to rate each recipe with one to five stars. The stars are displayed in large format near the top of the recipe for easy viewing. If the user changes the rating of a recipe, the number of stars displayed immediately changes to match. Here’s what the form looks like:

A Recipe with a four-star rating in Microsoft Access

If you’ve used Microsoft Access, you probably know how to insert an image onto a form. What if the image needs to change dynamically? In this post, I’m going to explain how I change the image displayed on the recipe’s detail form when a user either changes the recipe’s rating or a new recipe is loaded.

By now you will probably have guessed from my description that the image itself does not change. Instead, a completely different image is displayed when the number of stars changes. For this, you’ll need to create separate image files – one for each number of stars. I used the .bmp format to save my images after creating them with an image editing program. (Use a set of star-shaped outlines in a row of five. Then fill in as many star shapes as needed to create your image files.) I saved all my star images in the folder containing my database.

Now use the Access Form Design Menu to place one of your images where you want it on your form. It doesn’t matter which one you select. Size it to your preference. Then on the form’s property sheet, erase the name of the image in the picture property. This won’t matter, because you will insert the value using VBA code. Place the following procedure in the form's code module. Call it when the form’s On Current event fires, and when the value of the rating field changes.

Private Sub DoStars()
'
' Display stars to go with the rating of this recipe. Note that  ' you will need to specify the path to the star image files.
' The path (location) will be assumed to be the same location
' as the database file.  If image file is not found when needed, ' error will be jumped over and the procedure will end.
' 
' Code by Jennifer Neighbors
'

Dim strPath As String
strPath = CurrentProject.Path

On Error GoTo ErrHandler
If Not IsNull(Me.RecipeName) And Me.CategoryID > 0 Then
    Select Case (Me.Rating)
        Case 1
            Me.imgStars.Picture = strPath & "/" & _ "sm_one_star_rated.bmp"
        Case 2
            Me.imgStars.Picture = strPath & "/" & _ "sm_two_star_rated.bmp"
        Case 3
            Me.imgStars.Picture = strPath & "/" & _ "sm_three_star_rated.bmp"
        Case 4
            Me.imgStars.Picture = strPath & "/" & _ "sm_four_star_rated.bmp"
        Case 5
            Me.imgStars.Picture = strPath & "/" & _ "sm_five_star_rated.bmp"
    End Select
    If Me.imgStars.Picture = "" Then
        Me.imgStars.Visible = False
        Exit Sub
    Else
        Me.imgStars.Visible = True
    End If
Else
    Me.imgStars.Visible = False
End If

exitHere: Exit Sub
   
ErrHandler:
    '
    ' if file not found (error 2220) Then jump over.
    '
    If Err.Number = 2220 Then
        Exit Sub
    Else
        MsgBox "Oops! It looks like there has been an error. " _
    & "The error is number " & Err.Number & ": " & _     
    Err.Description & " in " & VBE.ActiveCodePane.CodeModule & _ 
    & ". The program will now attempt to resume normally.", _ 
    vbOKOnly, "Error"
    Resume exitHere

    End If
    
End Sub
Here’s what the form looks like:

To get your copy of this recipe database, go to the download page of my site. Are you surprised at how easily this was done? What uses might you find for this technique, other than for displaying five-star ratings? Share your ideas in the comments.