Monday, May 24, 2010

VBA - Opening a lookup window in expanded mode

Just recently, I came across a question from a partner asking if it was possible to have the scrolling window on a lookup form open in expanded mode, this is, showing information in detail. Traditionally, lookup windows in Microsoft Dynamics GP have always opened showing summary information, take for example the Item Lookup window shown below.

The partner wanted to see the window as shown below when first opened. Note that the scrolling window displays the additional fields added to the big line. This is accomplished by clicking on the switch arrow button that expands the scrolling window.

Of course, this is possible. The following VBA code can be used to open a Dexterity scrolling window in expanded mode by using pass-through Dexterity to set the switch value and run the expand window Dexterity statement.


Private Sub Window_BeforeOpen(OpenVisible As Boolean)
Dim CompilerApp As Object
Dim CompilerMessage As String
Dim CompilerError As Integer
Dim Commands As String

' Create link without having reference marked
Set CompilerApp = CreateObject("Dynamics.Application")

Commands = ""
Commands = Commands & "if 'ASI_LU_Shrink_Expand_Switch' of window IV_Item_Number_Lookup of form IV_Item_Number_Lookup = 2 then "
Commands = Commands & " abort script;"
Commands = Commands & "end if;"
Commands = Commands & "{set shrink switch out}"
Commands = Commands & "set 'ASI_LU_Shrink_Expand_Switch' of window IV_Item_Number_Lookup of form IV_Item_Number_Lookup to 2;"
Commands = Commands & "expand window IV_Item_Number_Scroll of form IV_Item_Number_Lookup, true;"

' Execute SanScript
CompilerApp.CurrentProductID = 1493 ' SmartList
CompilerApp.CurrentProduct = CompilerApp.CurrentProduct & ""

CompilerError = CompilerApp.ExecuteSanscript(Commands, CompilerMessage)
If CompilerError <> 0 Then
MsgBox CompilerMessage
End If

End Sub

NOTE: This script uses a method of calling Dexterity from VBA that is not supported by Microsoft.

Finally, this script can be enhanced by creating a generic function that would allow you to pass the name of the expansion button, the name of the form, main window, and scrolling window, which would then allow you to use it across almost all lookup windows throughout the system.

Until next post!

Mariano Gomez, MVP
Maximum Global Business, LLC


Arthur said...

Hi Mariano,
wouldn't this do the same thing?

Private Sub Window_BeforeOpen(OpenVisible As Boolean)
End Sub

Anonymous said...

Exactly! Much simplified.

Mariano Gomez said...

Much more simplified indeed! Good catch.

Anonymous said...

Would you recommend using simple code as above for opening an Extender Form or Dynamics Window from another window by the command button click or Dexterity would do a better job? Will the following VBA work for it?

Dim MyWindow As Object
Dim MyButton As CommandButton

Set MyWindow = ItemVendorsMaintenance
MyButton.Enabled = True

End Sub

Private Sub MyButton_Click()
Dim ItemMaintenance As Object
Dim MyWindow As Object

Set MyWindow = ItemVendorsMaintenance

End Sub