How to display icons in results of query? RRS feed

  • Question

  • Is this possible at all?

    Essentially I have a query set up which returns order information (order number, date, total etc) as well as 2 status fields.

    1) Purchases Status: Status of supplier purchases for this order. Value: none, partial, complete. 

    2) Shipping Status: Values: none, partial, complete.

    Since I am still in the development phase, I still only have text for these statuses. Then I had the idea of color coding using conditional formatting, but you end up with a table full of colors and it actually becomes too much.

    My next idea was having a set of 6 little icons: 3 representing the 3 Purchase Statuses, and 3 representing the 3 Shipping statuses. Each row (ie record) would always display 2 icons depending on the current status.

    So is there any way to display little icons on a datasheet form? I know this is easily done in Excel.....


    Thursday, November 17, 2016 2:48 AM

All replies

  • Not in datasheets as far as I know, but in continuous forms - which are much more flexible and can be designed to look like datasheets...

    Image Controls can be bound to Text Fields containing paths to images or to functions returning those paths, through their control source property. 

    Try writing a function in a standard module to return the path to the image you need to see, based on status. 

    For example, this returns image path according to regionID with odd IDs returning one image and even IDs returning another.

    Function GetImage(lngRegionID As Long) As String Dim s As String

    ' This assumes the images are in the same folder as the database.

    ' Full path is needed otherwise. If (lngRegionID Mod 2) = 0 Then s = "Cake.jpg" Else s = "Capture.png" End If GetImage = s End Function

    The control source property of the textbox would be (the = sign is needed when using functions for properties):


    Miriam Bizup Access MVP

    Thursday, November 17, 2016 12:18 PM
  • Hi SL02,

    I agree with the suggestion given by the mbizup MVP.

    you can store the data as status in the table.

    then in the image box you can display the image as per the status.

    for that I had created a simple demo for you. with the help of that you can get an idea.

    I had created a table like yours.

    then I created a form and add the code on Form_Current Event().

    Option Compare Database
    Option Explicit
    Private Sub Form_Current()
    If Me.p_status.Text = "none" Then
    Me.Image20.Picture = "none.png"
    ElseIf Me.p_status.Text = "partial" Then
    Me.Image20.Picture = "partial.png"
    ElseIf Me.p_status.Text = "complete" Then
    Me.Image20.Picture = "complete.png"
    Me.Image20.Picture = ""
    End If
    If Me.s_status.Text = "none" Then
    Me.Image36.Picture = "none.png"
    ElseIf Me.s_status.Text = "partial" Then
    Me.Image36.Picture = "partial.png"
    ElseIf Me.s_status.Text = "complete" Then
    Me.Image36.Picture = "complete.png"
    Me.Image36.Picture = ""
    End If
    End Sub

    when I run the code it will display the image as per record data.

    you can see that images are different as per status.



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact

    Friday, November 18, 2016 6:27 AM
  • You can do this very easily in a form, without any code, by storing the icons in a separate SupplierStatuses and ShippingStatuses tables, each of which has a Status column as its primary key and the icon images in a separate OLE Object column.  These tables can then be joined to the Orders table in a query as the RecordSource property of a form and the images from the two referenced tables returned in bound object frame controls in the form.  The same can be done in a report.

    You'll find a simple example of this technique as in my public databases folder at:!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file each transaction's Boolean Cleared value is shown as a colour patch at the right of each row in a form in continuous forms view.  In your case each image would not be a plain bitmap image as in my case, but an icon image.

    Ken Sheridan, Stafford, England

    • Edited by Ken Sheridan Friday, November 18, 2016 6:29 PM Grammar corrected.
    Friday, November 18, 2016 6:26 PM
  • I do what Ken does. I store the icons in a table. As stated, you have to use a form or report to display them. I've done this several times for things like KPI's, milestones and statuses. It is also very easy to maintain with all of them in a single table. You never have to worry about files getting moved.

    Bill Mosca

    Friday, November 18, 2016 10:11 PM