GP Self Service - Locked Transactions

I neglected to mention another potential good reason to Customize GP; control your support costs, increase your IT resource capacity, and increase user satisfaction. There are routine problems that are easily resolved but often require the assistance of a system administrator. For example, records locked by other users, orphaned TempDB.dbo.DEX_LOCK records, can be a nuisance. This typically grows more common as the concurrent user count increases in Citrix environments. These records are necessary to prevent multiple users from accessing the same transactions at the same time. However, when they are not properly cleared they prevent users from accessing transactions even though another user may not be.

In this example, I added a Push Button to Sales Transaction Entry using Modifier.

Then, I created 2 stored procedures; one to return the userid that has locked the transaction so the user can attempt to resolve this problem on their own ...

... and the other to remove the locking record.


Next, add the Sales Transaction Entry Window, the Document No. field, and the Unlock Trx button to your VBA Project.

Open the VBA Editor to set a Reference in the Microsoft_Dynamics_GP Project to the Microsoft Active X Data Objects 2.x Library. This is required to access the database directly through VBA.

Finally, paste the following code behind the Sales Transaction Entry Window. If some pieces of this are foreign to you try to take advantage of Mariano's VBA workshop this week to learn more and check back as I take a deeper dive in the future into some of these concepts. My apologies for the formatting of this code. I'll work on that.

Private Sub UnlockTrx_AfterUserChanged()

'Declare the variables for the message boxes
Dim Msg, Style, Title, Help, Ctxt, Response, MyString, Default

'Declare the variables to make the connection to the database
Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command

On Error GoTo Proc_Error:

'Prompt the user for the Document Number they want to unlock
Msg = "Enter the Document Number you want unlocked." ' Set prompt.
Title = "Unlock SOP Trx" ' Set title.
MyString = InputBox(Msg, Title, Default)

'Set the variables required to make the connection
Set cn = UserInfoGet.CreateADOConnection
cn.DefaultDatabase = UserInfoGet.IntercompanyID
cmd.ActiveConnection = cn

'Query the database to get user that has the document locked
cmd.CommandText = "exec uSp_SELLockedDocUser '" & Microsoft_Dynamics_GP.SalesTransactionEntry.DocumentNo & _
"', '" & cn.DefaultDatabase & "', 'SOP10100'"
Set rst = cmd.Execute
If rst.EOF = False Then
'Inform the user and give them the option to unlock the document or cancel
Msg = "This document is locked by " & (RTrim(rst.Fields("userid"))) & ". Continue Unlocking?"
Style = vbOKCancel + vbCritical ' Define buttons.
Title = "Dynamics GP" ' Define title.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
'Unlock the document if requested by the user
If Response = vbOK Then
cmd.CommandText = "exec uSp_DELClearLockedDoc '" & Microsoft_Dynamics_GP.SalesTransactionEntry.DocumentNo & "', '" & _
cn.DefaultDatabase & "', 'SOP10100'"
Set rst = cmd.Execute
End If
Else
'Inform the user that the record is not locked
Msg = "This document is not currently locked by another user."
Style = vbOKOnly + vbInformation ' Define buttons.
Title = "Dynamics GP" ' Define title.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
End If

GoTo Procedure_Exit:

Proc_Error:

MsgBox Error$ & " " & Error, vbOKOnly, "UnlockTrx_AfterUserChanged"

Procedure_Exit:

End Sub