This has probably been done a few thousand times by a few hundred people over the years, but I don't recall ever doing it before.
A customer wants to automatically generate batch IDs in Dynamics GP. They don't want to have to type the batch ID or create batches when entering transactions. They want to be able to open the Payables Transaction Entry window, have the Batch ID automatically generated and just starting entering a transaction.
Here is a link to a Word document with the instructions and the script.
Once the code is installed, if you open the Payables Transaction Entry window, a new batch is automatically created and the batch ID value is populated in the Batch ID field.
The customer didn't want to use the date, but wanted a sequential numeric batch ID that would be unique. I used the username, plus the two digit year, plus the day of the year. This ensures a unique batch for each day, and it's a way of using the date in the batch ID without using the full date.
Make sure to following the instructions in the Word doc and add a VBA Reference to Microsoft ActiveX Data Objects 2.8 Library in the VBA Editor.
Dim conn As ADODB.Connection
Dim cmd As New ADODB.Command
Private Sub Window_BeforeOpen(OpenVisible As Boolean)
Set conn = UserInfoGet.CreateADOConnection
conn.DefaultDatabase = UserInfoGet.IntercompanyID
cmd.ActiveConnection = conn
cmd.CommandType = 1 'adCmdText
Private Sub Window_AfterOpen()
Dim interID As String
Dim userID As String
Dim shortYear As String
Dim dayNumber As String
Dim currentDate As Date
Dim sqlDate As String
Dim batch As String
Dim sql As String
interID = UserInfoGet.IntercompanyID 'Get the database name (INTERID)
userID = UCase(UserInfoGet.userID) 'Get the GP user ID
currentDate = DateTime.Now
sqlDate = Format(currentDate, "yyyy-MM-dd")
shortYear = Right(DateTime.year(currentDate), 2) '2 digit year
dayNumber = DatePart("y", currentDate) 'Get day number (out of 365 / 366)
batch = userID & "-" & shortYear & dayNumber 'Ex: jdoe-17134
'Create the batch if it does not already exist
sql = "EXEC " & interID & "..taCreateUpdateBatchHeaderRcd '" & batch & "', '', 4, '" & sqlDate & "', 'PM_Trxent', 0, 1, 0, '', 0, 0, 1, 0, 0, 0, 0, '" & userID & "', 1, 0, 0, 0, '1900-01-01', '', '', '', '', '', '', '', 0, ''"
cmd.CommandText = sql
'Assign the new batch ID to the batch field on the window
batchID.Value = batch