VBA Code:
Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strItemClass As String
Dim strOrigin As String
Dim intLastOrigin As Integer
'this mod is designed to not allow the same user that created the batch to post it.
'We take several steps to accomplish this
'the first step is a SQL trigger that records the user id in the SY00500 table
'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.
'
'There is a bug in this form, the BatchID and Origin_Changed events don't fire correctly
'when the user looks up a '2' document. We get around that by trapping the PostingDate_Changed
'event.
'Also, an error occurs if you try to enable the post button when the last document was
'a type '2'. We get around that by using an empty error handler.
Private Sub PostingDate_Changed()
intLastOrigin = Me.Origin
End Sub
Private Sub BatchID_Changed()
On Error GoTo HandleError
If Me.BatchID > "" And Me.Origin > 0 Then
setPostButtonEnabled
intLastOrigin = Me.Origin
Else
If intLastOrigin <> 2 Then
Me.PostButton.Enabled = True
End If
End If
Exit Sub
HandleError:
End Sub
Private Sub Origin_Changed()
On Error GoTo HandleError
If Me.BatchID > "" And Me.Origin > 0 Then
setPostButtonEnabled
intLastOrigin = Me.Origin
Else
If intLastOrigin <> 2 Then
Me.PostButton.Enabled = True
End If
End If
Exit Sub
HandleError:
End Sub
Sub setPostButtonEnabled()
If cn.State = 0 Then
openConnection
End If
Select Case Me.Origin
Case 1
strOrigin = "PM_Trxent"
Case 2
strOrigin = "XPM_Cchecks"
Exit Sub
Case 3
strOrigin = "PM_Payment"
End Select
intLastOrigin = Me.Origin
'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
SQL Batch Select statement:
-- =============================================
-- =============================================
IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'dbo'
AND SPECIFIC_NAME = N'FP_SY00500_SEL_byBatchOrig'
)
DROP PROCEDURE dbo.FP_SY00500_SEL_byBatchOrig
GO
CREATE PROCEDURE dbo.FP_SY00500_SEL_byBatchOrig
-- FP_SY00500_SEL_byBatchOrig 'CMXFR00000001 ','GL_Normal '
@BACHNUMB VARCHAR(15),
@BCHSOURC VARCHAR(15)
AS
set transaction isolation level read uncommitted
select BACHNUMB,BCHSOURC,rtrim(userid) as USERID,GLPOSTDT,SERIES,MKDTOPST,NUMOFTRX,RECPSTGS,DELBACH,MSCBDINC,BACHFREQ,RCLPSTDT,NOFPSTGS,BCHCOMNT,BRKDNALL,
CHKSPRTD,RVRSBACH,CHEKBKID,BCHTOTAL,BCHEMSG1,BCHEMSG2,BACHDATE,BCHSTRG1,BCHSTRG2,POSTTOGL,MODIFDT,CREATDDT,NOTEINDX,
CURNCYID,BCHSTTUS,CNTRLTRX,CNTRLTOT,PETRXCNT,APPROVL,APPRVLDT,APRVLUSERID,ORIGIN,ERRSTATE,GLBCHVAL,Computer_Check_Doc_Date,
Sort_Checks_By,SEPRMTNC,REPRNTED,CHKFRMTS,TRXSORCE,PmtMethod,EFTFileFormat,Workflow_Approval_Status,Workflow_Priority,
TIME1,DEX_ROW_ID
from sy00500 B
where BCHSOURC = @BCHSOURC
and BACHNUMB = @BACHNUMB
GO
grant exec on FP_SY00500_SEL_byBatchOrig to public