Monday, December 13, 2010

How to display an image at the User Interface level

One of the questions I often get is, can you dynamically display an image on a Microsoft Dynamics GP form or report. The answer is No, it's not possible. The reason for this is, Microsoft Dexterity, the tool used to developed your beloved Microsoft Dynamics GP, is only capable of displaying images stored within the dictionary itself. Since the picture control in Dexterity is binded to these pre-stored images, this obviously would indicate that there is no room for dynamic images to be added. In addition, the Dexterity picture control cannot be referenced or manipulated programatically as Dexterity does not treat this object like it does with any other fields.

One *exception* may be the adding these pictures via Modifier, but even so, this is not a dynamic manipulation of a picture control. In summary, not possible!

Of course, you can always resort to VBA. The objective of this post is to see how you can use VBA to aid in displaying images where needed. One typical application is displaying product images or employee pictures. Today's example will look at displaying product images. For this customization we will add a button to the Item Maintenance window, which in turn will call a VBA user form. Our user form contains a picture control which will display the product image once the window opens. We will use the image link on the Item Internet Information window to store the path to the actual image file.

NOTE: The Item Internet Information window can certainly display a picture, but it calls the default application associated with the extension of the file being loaded. Not practical if you want to avoid users damaging the actual image file.

1. Modify the Item Maintenance window to add a button control. To accomplish this, we will use Modifier to add the button control. Once the control has been added, we will return to Microsoft Dynamics GP to grant security to the modified window.


Item Maintenance window in Modifier
Note the button control added next to the other buttons (click image to enlarge)
Upon returning to Microsoft Dynamics GP, we grant security to the modified window and the result should be as follows:

Item Maintenance window

2. For the next step, we will add the window, the Item Number field, and the newly added Image button to Visual Basic for Applications. We can then proceed to create a simple user form with a picture control as shown below:



For this example, we have renamed the picture control object to ctrlImage and the user form to frmImage.

3. The following piece of code is added to the Image button' BeforeUserChanged event on the ItemMaintenance object:

Image_BeforeUserChanged()
' Created by Mariano Gomez, MVP
'  This code is licensed under the Creative Commons 
'  Attribution-NonCommercial-ShareAlike 2.5 Generic license.
Private Sub Image_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)
    If ItemNumber.Empty = False Then
        frmImage.Show
    Else
        MsgBox "Please select an Item Number to continue"
    End If
End Sub

The above code opens the frmImage user form if the ItemNumber field is not empty.

4. We can now add the code to load the image based on the path stored in the Internet Addresses table (dbo.SY01200) in the company database. For this, we will use the UserInfoGet object to open an ADO connection to the company database and use a recordset to retrieve the path stored for the specific item. In addition, we will use the LoadPicture() function to read the file containing the actual image into the picture control.

UserForm_Activate()
' Created by Mariano Gomez, MVP
'  This code is licensed under the Creative Commons 
'  Attribution-NonCommercial-ShareAlike 2.5 Generic license.
Private Sub UserForm_Activate()
    Dim oCn As New ADODB.Connection
    Dim oCmd As New ADODB.Command
    Dim oRst As New ADODB.Recordset, picRst As New ADODB.Recordset
    Dim oStream As New ADODB.Stream
    
    ' Establish connection to company database
    Set oCn = UserInfoGet.CreateADOConnection()
    With oCn
        .CursorLocation = adUseClient
        .DefaultDatabase = UserInfoGet.IntercompanyID
    End With
    
    If oCn.State = adStateOpen Then
        With oCmd
            .ActiveConnection = oCn
            .CommandType = adCmdText
                        
            ' Command to retrieve image path
            .CommandText = "SELECT INET4 FROM SY01200 WHERE Master_ID = '" & ItemMaintenance.ItemNumber & "' AND Master_Type = 'ITM'"
                    
            Set oRst = .Execute
                    
            If Not oRst.EOF Then
                ctrlImage.Picture = LoadPicture(oRst!INET4)
                ctrlImage.PictureSizeMode = fmPictureSizeModeStretch
                
            Else
                MsgBox "Could not find an image file for this item"
            End If
                    
            oRst.Close
        End With
    Else
        MsgBox "Could not connect to company database " & UserInfoGet.IntercompanyID, vbCritical, "Connection Error"
        Set oCn = Nothing
    End If
End Sub

5. Finally, some sample data... I will be using a picture of a processor, stored in the path indicated by the window field:

Internet Information window
Once we are done, we can test our customization by clicking on the Image button on the Item Maintenance window:

Item Maintenance window with Item Image user form window

It's great to see how VBA can help with everyday tasks that may otherwise seem complex. Hope you enjoyed the techniques used in this customization and provide your feedback. There are a few enhancements I can think of, but this should get you started in the right direction.

Downloads:

Item Maintenance and User Form package and Image zip file - Click here

Notes:

a) To test, change the path of the image on the item Internet Information card.
b) You may require a reference to Microsoft ActiveX Data Objects under the Visual Basic Editor to have this code execute properly.
c) This customization is compatible with Microsoft Dynamics GP versions 10.0 and 2010

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

8 comments:

Anonymous said...

Hello Mariano,

Thanks for the posting and looks like when I give the path in the Internet information window for the image to pop up, this does not happen any reason this is the path.
C:\SS\Pic1.jpg

Anonymous said...

I think it should be in the user path which is

C:\Users\User\SImages\Product.jpg

Abdullah Shawkat said...

Can the picture (image) display without a frame?

Mariano Gomez said...

Abdullah,

The frame is added by Visual Basic, so I am not sure you can control this.

MG.-
Mariano Gomez

go away said...

Hey Mariano, awesome post but would these images added to reports be rendered in the wordtemplates?

Cheers
Arthur

Mariano Gomez said...

@Arthur,

No. Word Template images need to be loaded under the Template Configuration window. These images in Template Configuration only apply to logos.

I am sure there's ways to modify the template with Word VBA or VST for Office to display an image dynamically, but I haven't looked into that.

MG.-

jiyaul huk said...

Mariano Gomez thanks for your post ! i have one doubt without VBA we can show the item images?
its possible.

Mariano Gomez said...

Yes, you can, but they are static images. I already mentioned that in the article. The images must already exist in the dictionary.
MG.-