Developing for Dynamics GP

by David Musgrave (Perth, Australia) and Microsoft Dynamics GP Developer Support Team (Fargo, ND, USA)

VBA - Default Lookup Sort Order Example

VBA - Default Lookup Sort Order Example

  • Comments 2

The following VBA example shows how you can set a lookup window to open with a default sort order.  The reason that this is normally difficult is that the usual methods that people use to set defaults with VBA do not work on lookup windows.

Normally, you could use the Window_AfterOpen() event to set a default value.  This is because the Window_AfterOpen() event is usually the last script run when a window is opened and your script will run after the window is populated and just before control is returned to the user.

This does not work for lookups because lookup windows are opened as one step and then populated as a second step.  If you used the method above, your VBA code would be overridden when the window is populated.  The timing of the execution of the VBA code is wrong.

So, how can we get the timing right.... The last thing that happens when a lookup window is opened and populated is that the focus is set to the Find field.  So using the ASILUFindString_AfterGotFocus() event, we can get the timing to run our VBA code after the lookup is populated.  To ensure that we only change the Sort order once, we use a boolean OpenFlag variable which set when the window is opened and cleared after the first time we change the Sort order.

You need to add the lookup window to VBA and then the Find field and the Column Button to press to change the sort order to VBA. This example changes the Vendor Lookup to sort by Vendor Name by default. The script below refers to Creditor Name as it was built on an international English system. This will not stop it working for you. 

Option Explicit

Dim OpenFlag As Boolean

Private Sub ASILUFindString_AfterGotFocus()
    If OpenFlag Then
        OpenFlag = False
        CreditorName = 1
    End If
End Sub

Private Sub Window_AfterOpen()
   OpenFlag = True
End Sub

NOTE: If there is no Column Button for the additional sort you want to use, you can also use the SendKeys VBA command to send the key presses to open the View menu and select a sort order.

Example code for v8.0, v9.0 & v10.0 is attached at the bottom of the article.

Please see the "Installation Instructions.txt" file in each version's archive for more information.

Attachment: Default Lookup Sort
Page 1 of 1 (2 items)
Comments Information


Please only post comments relating to the topic of this page.

If you wish to ask a technical question, please use the links in the links section (scroll down, on right hand side) to ask on the Newsgroups or Forums. If you ask on the Newsgroups or Forums, others in the community can respond and the answers are available for everyone in the future.

Leave a Comment
  • Please add 1 and 6 and type the answer here:
  • Post