nav-left cat-right
cat-right

Authenticate Reprinting of Sales Documents in Microsoft Dynamics GP

Recently, a question was posted in the community about requiring users to enter a password before reprinting the sales document. I thought I ‘ll document in detail for future references. Steps for accomplishing this are

  1. Get the number of times invoice has already been printed
    1. In Sales Order Processing, you can use the following query to get number of times document was printed

      Select TIMESPRT from SOP10100 where SOPNUMBE=’XXX’

    2. In Invoicing Module, query to get number of times document was printed is

      Select TIMESPRT from IVC10100 where INVCNMBR=’xxx’

    3. In Receivables module, use DocPrinted bit to check whether invoice was printed or not

      Select DocPrinted from RM10301 where DOCNUMBR=’XXX’

  2. Prompt the user for the password, if invoice was already printed: I have added a simple form to prompt user for password
    1. If correct password is entered, invoice is printed successfully
    2. If incorrect password is entered, invoice printing is canceled and a message is displayed
      For sake of this example, I have hardcoded the password in the code. ***However, password should be encrypted/stored in database or other secure location the production environment.
  3. In case of batch printing in SOP , user is prompted for password when he tries to check on “Reprint Previously Printed/Sent”
    1. If correct password is entered, user is allowed to check on the checkbox.
    2. If incorrect password is entered, checkbox is disabled

I am using the Sales Transaction entry window in the example below. Similar prompt can be easily added in the Invoice Entry or Transaction Entry window

Code for checking if invoice was already printed

Option Explicit

Dim cn As New ADODB.Connection

Dim rst As New ADODB.Recordset

Dim cmd As New ADODB.Command

Dim sqlstring As String

Private Sub WindowPrint_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)

Dim timesPrinted As Integer

Set cn = UserInfoGet.CreateADOConnection

‘Use a client-side cursor so that a recordset count can be obtained later.

cn.CursorLocation = 3

‘set the database to the currently logged in db

cn.DefaultDatabase = UserInfoGet.IntercompanyID

sqlstring = “Select TIMESPRT from SOP10100 where SOPNUMBE=’” & RTrim(DocumentNo.Value) & “‘”

‘ ADO Command

cmd.ActiveConnection = cn

‘ adCmdText

cmd.CommandType = 1

‘ Command

cmd.CommandText = sqlstring

Set rst = cmd.Execute

If Not (rst.EOF And rst.BOF) Then

timesPrinted = RTrim(rst!TIMESPRT)

End If

rst.Close

‘ Close ADO Connection

If rst.State = adStateOpen Then rst.Close

If cn.State = adStateOpen Then cn.Close

Set cn = Nothing

Set rst = Nothing

Set cmd = Nothing

‘If already printed, display password prompt

If timesPrinted > 0 Then

Dim enterPass As New EnterPassword

enterPass.Show vbModal

If Not enterPass.isAuthorised Then

CancelLogic = True

MsgBox “You are not authorised to re print the invoice”

End If

End If

End Sub

Code behind of the custom form EnterPassword

Public isAuthorised As Boolean

Private Sub btnCancel_Click()

isAuthorised = False

Unload Me

End Sub

Private Sub btnOK_Click()

If txtPassword.Value = “XXXXXX” Then   ‘Save password in secure location

isAuthorised = True

Else

isAuthorised = False

End If

Unload Me

End Sub

Code to enable/disable checkbox on “Reprint Previously Printed/Sent” in Print Sales Documents window (Transactions >> Sales >> Print Sales Documents)

Option Explicit

Private Sub Documents_BeforeGotFocus(CancelLogic As Boolean)

Documents.Enabled = False

Dim enterPass As New EnterPassword

enterPass.Show vbModal

If Not enterPass.isAuthorised Then

CancelLogic = True

MsgBox “You are not authorised to re-print the invoice”

Documents.Enabled = False

Else

Documents.Enabled = True

End If

End Sub

Downloads:

VBA  Package

Leave a Reply