VBA - Adding Extender Windows to GL Transaction Entry Lines

David Meego - Click for blog homepageLast week I had an interesting support case that I thought I would share with you.

The customer had created an Extender window to capture additional information for distribution lines on the General Ledger Journal Entry (GL_Transaction_Entry) window. The unique identifier for a distribution line is the Journal Entry field from the main window and the Sequence Line field from the scrolling window. These are the fields used when creating the Extender settings for the additional information to be stored.

See the example Extender Window setup below:

 

The issue we were seeing is that the value of the Sequence Line field for new lines remains as zero (0) until the user tabs off the line and the line is saved.  If information was added for the new line before it was saved, that information would be saved with a value of zero for the Sequence Line field and would not be correctly linked to the line once the actual Sequence Line value was assigned.  The work around solution without customization is to make sure the line is saved and then come back to it to add the additional information.... not a very user friendly solution.

Many windows in the application with scrolling windows (aka grids) do not populate the Sequence Line (or equivalent) field until the line is saved.  Often this is one way that the code knows that the line is a new line and not an already saved "existing" line. Windows built using this method will not work well for Extender information linked to the lines until the line is saved.

The partner asked why the Sales Transaction Entry (SOP_Entry) window works with Extender as it populates the Line Item Sequence field as the line is created, before it is saved.  The reason that this window behaves differently is that there is additional data linked to a line for a SOP transaction (such as taxes, serial/lot numbers, etc.). To allow the table relationships to be created for the additional child data, the Line Item Sequence value must be assigned earlier.  In the case of the Journal Entry window, there is no additional child data and so no need to populate the Sequence Line field in advance.


The customer was licensed for the Customization Site License, so it meant that Visual Basic for Applications (VBA) code created by the support team or the partner could be installed and used on the customer's system.  So I set to work finding a solution using Modifier & VBA to create a customization to allow the Extender window to work.

My initial thoughts were to take the focus to a field on the header after the Account Number field had been entered, using the AfterUserChanged() event.  This would force the line change script to run to populate the Sequence Line field and save the line.  Then I could return the focus to Debit Amount field. This sounded great in theory, but failed in practice due to the following error You may not enter a transaction without an amount. dialog.

I did think about entering some dummy value into the Debit Amount field to allow the save to work, but that was just getting too messy.  A different approach was needed.

My next idea was to populate the Sequence Line field earlier, when the line is created. This is the same approach that the Sales Transaction Entry window uses.  Looking at the source code for the window I could see where the Sequence Line field is populated (in the Scrolling window Line change script). I could also see that there was not any other code that was looking to see if the Sequence Line field was empty() or equal to 0.  It was important to check for any code looking for the zero value as I did not want to break any existing functionality by populating the Sequence Line field early.

So using VBA code, I tried to replicate the Dexterity code with the following script:

Private Sub AccountNumber_AfterUserChanged()
If SequenceLine = 0 Then
SequenceLine = TransactionEntry.NextSequenceDLR
TransactionEntry.NextSequenceDLR = TransactionEntry.NextSequenceDLR + 500
End If
End Sub

Both the 'Sequence Line' and '(L) Next Sequence DLR' fields on the windows are hidden fields. This means you cannot add them to VBA easily.  Well, you could modify the window to make the fields visible and place them in the visible area of the window, change the security to show the modified window, change the VBA Eventmode property to the modified window, add the fields to VBA, change the security back to the original, delete the modified window and change the VBA Eventmode property back to the original..... or you can do something completely unsupported, but much quicker and easier.  So, I added "place holder" fields that I could see from the main window and the grid, exported the package using Customization Maintenance, edited the package in Notepad to change the fields to the hidden fields and imported the package back. 

See the package excerpt below.  WindowField is the Dexterity Field name (including "(L) " for local fields).  DisplayName is the VBA Name without spaces (without "(L) " for local fields), this can be the prompt text for fields linked to a prompt.  ProgrammerName is the name with spaces (but without "(L) " for local fields).

    WindowField "(L) Next Sequence DLR"
{
ArrayIndex "0"
DisplayName "NextSequenceDLR"
Local "true"
ProgrammerName "Next Sequence DLR"
}

While editing the package file works really well if you are careful, it is really easy to corrupt the file.  Make sure that you have a backup file and if you mess it up, don't ask for help from Microsoft Support.

Anyhow, after all that work, the code fails because the fields are not editable. VBA generates a Run-time error '1008': Unsafe Operation. An attempt was made to set a value into an application field which is disabled. This operation could compromise the integrity of the application. error. Doh.

 

Again, while this idea was good in theory, it failed in practice. Yet another approach was needed.

To bypass the VBA "protection" logic that prevents non editable fields from being changed by VBA, we will have to use Dexterity to make the change for us instead. 

So, using another unsupported method, I created a script to execute Dexterity sanScript code via the Continuum Integration Library. The following VBA code uses Dexterity sanScript to achieve the same results:

Hybrid VBA and Dexterity sanScript code

 Private Sub AccountNumber_AfterUserChanged()
'    If SequenceLine = 0 Then
'        SequenceLine = TransactionEntry.NextSequenceDLR
'        TransactionEntry.NextSequenceDLR = TransactionEntry.NextSequenceDLR + 500
'    End If
    
    'Dim CompilerApp As New Dynamics.Application
    Dim CompilerApp As Object
    Dim CompilerMessage As String
    Dim CompilerError As Integer
    Dim CompilerCommand As String

    ' Create link without having reference marked
    Set CompilerApp = CreateObject("Dynamics.Application")
    
    CompilerCommand = ""
    CompilerCommand = CompilerCommand & "{-- Check to see if this is a new line --} " & vbCrLf
    CompilerCommand = CompilerCommand & "if 'Sequence Line' of window Transaction_Scroll of form GL_Transaction_Entry = 0.0 then " & vbCrLf
    CompilerCommand = CompilerCommand & "    {-- Assign the next available sequence number to the new line --} " & vbCrLf
    CompilerCommand = CompilerCommand & "    set 'Sequence Line' of window Transaction_Scroll of form GL_Transaction_Entry to '(L) Next Sequence DLR' of window GL_Transaction_Entry of form GL_Transaction_Entry; " & vbCrLf
    CompilerCommand = CompilerCommand & "    {-- Increment the next available sequence number by 500 --} " & vbCrLf
    CompilerCommand = CompilerCommand & "    set '(L) Next Sequence DLR' of window GL_Transaction_Entry of form GL_Transaction_Entry to '(L) Next Sequence DLR' of window GL_Transaction_Entry of form GL_Transaction_Entry + 500; " & vbCrLf
    CompilerCommand = CompilerCommand & "end if;"
    
    ' Execute SanScript
    CompilerApp.CurrentProductID = 0 ' DYNAMICS
    'CompilerApp.CurrentProduct = CompilerApp.CurrentProduct & "!Modified"
    CompilerError = CompilerApp.ExecuteSanscript(CompilerCommand, CompilerMessage)
    If CompilerError <> 0 Then
        MsgBox CompilerMessage
    End If
End Sub

I based the Dexterity code on the actual script from the source code, just adding the window and form qualification to make it work. This is because pass through Dexterity sanScript executes as though it is a global procedure and so resources need to be fully qualified. 

The user needs to tab off the Account Number field for this code to run.  So they must press the Tab key before pressing opening the Extender window, using menus or the Ctrl-J shortcut key.

I changed the Extender Settings on my test window to Auto Open on Field Exit from the Account Number field and to Auto Close.  This avoids the use of Ctrl-J entirely and makes sure that we have tabbed out of the field before opening the Extender window. See settings below:

 

Note: The VBA Package and the Extender Settings file are attached as an archive to the bottom of this post.

I hope you find this technique useful and can see that it often takes more than one attempt to get to a working solution.

David

20-Aug-2010: Make sure you look at the follow up post: VBA - Displaying Extender Data for GL Transaction Lines on the GL Inquiry window.

// Copyright © Microsoft Corporation. All Rights Reserved.
// This code released under the terms of the
// Microsoft Public License (MS-PL, https://opensource.org/licenses/ms-pl.html.)

GL_Transaction_Entry.zip