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/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: