Macro Mondays: Retrieving the batch id of a posted transaction

Macro Mondays: Retrieving the batch id of a posted transactionEver scour through pages (and pages) of posted transactions to find the specific batch id that a record was posted in?  I have on many occasions and it drives me bonkers.

The details of posted transactions (including the batch ids) are kept in the database tables with names ending with “_DIST” – with the exception of INV_TRANS_DIST (which maintains the FIFO layers).

I’ve created macros (for select VISUAL modules) that allow the user to lookup the batch id used for the posted record/document they’re in.

The following example will lookup the PAYABLE_DIST table to retrieve the Batch ID of an AP Voucher that was entered and posted.


‘Insert this macro in Payables Invoice Entry Module
.
‘****** RETRIEVE BATCH ID OF POSTED AP VOUCHER
‘****** VFAPIENT
‘****** Mind the line wrap!!!

Dim x
Dim strCnn
Dim SQL
Dim rs

Dim strMsg

Dim strServerName
Dim strDatabase
Dim strUserName
Dim strPassword

strServerName=”E N T E R    N A M E    O F    D A T A B A S E    S E R V E R”
strDatabase=”E N T E R    N A M E    O F    D A T A B A S E”
strUserName=”E N T E R    U S E R N A M E”
strPassword=”E N T E R    P A S S W O R D”

strcnn=”Driver={SQL Server};Server=” & strServerName & “;Database=” & strDatabase & “;Uid=” & strUsername & “;Pwd=” & strPassword & “;”

set rs = CreateObject(“ADODB.Recordset”)

SQL=”SELECT BATCH_ID FROM PAYABLE_DIST WHERE VOUCHER_ID='” & VOUCHER_ID & “‘ AND POSTING_STATUS=’P’ GROUP BY BATCH_ID ORDER BY BATCH_ID”

rs.open SQL, strcnn

if not rs.eof then
    rs.movefirst
    
    do until rs.eof
        if len(strMsg & “”)>0 then
            strMsg=strMsg & chr(13)
        end if
        strMsg=strMsg & rs(“BATCH_ID”) 
        rs.movenext
    loop

end if

rs.close
set rs=nothing

if strMsg<>”” then
    Msgbox “Voucher ID ” & VOUCHER_ID & ” was posted in the following batch(es):” & vbcrlf & vbcrlf & strMsg
else
    Msgbox “This AP Voucher has not been posted.”
end if

‘End of MACRO


Macro Mondays is a new series where I plan on posting macros that I use to validate, automate and enforce business processes within the VISUAL application.  If you have any macros that you wish to share, please feel free to send them to me at rich@visualnuggets.com.  I’ll be sure to post them on your behalf. 

Copyright 2019 visualnuggets © All Rights Reserved