VBA Mod - Disable the post button in the GL Batches window

This article describes the code needed to create a VBA mod in GP 10 to keep a user from posting a General Ledger batch if that user is the one that created the batch.

We take several steps to accomplish this. The first step is a SQL trigger that records the user id in the SY00500 table. Dynamics does not correctly record the user, so we fix that.

Second, we trap the Origin_Changed and BatchID_Changed events and we look up the batch in SY00500. Based on the userID there, we enable/disable the post button on the form.

Related Articles

... and you 'll find more on the VBA Menu

VBA Code:

Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strItemClass As String
Dim strBatchNumber As String
Dim strOrigin As String
  
Private Sub Window_BeforeClose(AbortClose As Boolean)
    strBatchNumber = ""
End Sub
  
Private Sub BatchID_Changed()
      
    If Me.BatchID > "" And Me.Origin > 0 Then
        setPostButtonEnabled
    Else
        Me.PostButton.Enabled = True
    End If
      
End Sub
  
Private Sub Origin_Changed()
    If Me.BatchID > "" And Me.Origin > 0 Then
        setPostButtonEnabled
    Else
        Me.PostButton.Enabled = True
    End If
  
End Sub
  
Sub setPostButtonEnabled()
    If cn.State = 0 Then
        openConnection
    End If
       
    If Me.Origin = 1 Then
        strOrigin = "GL_Normal"
    Else
        strOrigin = "GL_Clearing"
    End If
      
    'get an item
    Dim cmd As New ADODB.Command
    cmd.CommandText = "FP_SY00500_SEL_byBatchOrig"
    cmd.ActiveConnection = cn
    cmd.CommandType = adCmdStoredProc
    cmd.Parameters.Append cmd.CreateParameter("@BACHNUMB", adVarChar, adParamInput, 15, Me.BatchID)
    cmd.Parameters.Append cmd.CreateParameter("@BCHSOURC", adVarChar, adParamInput, 15, strOrigin)
    Set rst = cmd.Execute
      
    If Not rst.EOF Then
        If rst("userid") = UserInfoGet.UserID Then
            Me.PostButton.Enabled = False
        Else
            Me.PostButton.Enabled = True
        End If
    End If
  
End Sub
  
  
   
Sub openConnection()
    Set cn = UserInfoGet.CreateADOConnection
    cn.DefaultDatabase = UserInfoGet.IntercompanyID
End Sub
   
   
Private Sub Window_AfterOpen()
    openConnection
End Sub

 

SQL Trigger:

IF OBJECT_ID ('t_SY00500_INSUPD','TR') IS NOT NULL
   DROP TRIGGER t_SY00500_INSUPD 
GO
  
CREATE TRIGGER t_SY00500_INSUPD 
   ON SY00500
   for insert, update
     
AS 
  
set transaction isolation level read uncommitted
    
update SY00500 set USERID = ba.userid
    from SY00500 b
        join DYNAMICS..sy00800 ba on ba.bachnumb = b.bachnumb and ba.BCHSOURC = b.BCHSOURC
    where b.USERID = ''
GO

RealWorldCode gives developers practical, real‑world solutions with clean, working code — no fluff, no theory, just answers.
Links
Home
Knowledge Areas
Sitemap
Contact
Et cetera
Privacy Policy
Terms and Conditions
Cookie Preferences