VBA & ADO – Restricting Non-IV Repetition on SOP Documents


This one is another interesting query from a User on GP Community Forum (original link: Restrict Duplicate Non Inventory Items on SOP Invoice). The query is as follows:

Has anyone ever come across a good way to restrict dupliate non inventory items from being entered on a SOP Invoice? For example if I enter 12345 as the non inventory item number on an invoice and post that I don’t ever want to be able to enter 12345 again.

While couple of replies were indicating Dex involvement, I was thinking otherwise. Though, I am an ardent believer in Dexterity, this one does not need a separate chunk as such. The immediate alternate is of course VBA.

This time I am going to use VBA & ADO to incorporate this restriction on SOP Entry form. How? Read the below steps:

1. Open GP and start entering an SOP Document, in order to activate the SOP Line Grid (Scrolling Window) and its fields.

2. Add SOP Entry form to VBA Editor and add the field “Item Number” to the VBA Editor.

3. Open VBA Editor and copy paste the following code on to the “SalesTransactionEntryDetail” code editor (Forgive the format and alignment of the code piece.):


Private Sub ItemNumber_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)
Dim sItemNo As String, sQuery As String
Dim adoConn As New ADODB.Connection, _
adoComm As New ADODB.Command, _
adoRecSet As New ADODB.Recordset

sItemNo = ItemNumber.Value
Set adoConn = UserInfoGet.CreateADOConnection
adoConn.CursorLocation = adUseClient
adoConn.CommandTimeout = 1000
adoConn.DefaultDatabase = UserInfoGet.IntercompanyID

adoComm.ActiveConnection = adoConn
adoComm.CommandType = adCmdText

sQuery = “SELECT COUNT(*) AS RECCOUNT FROM SOP30300 WHERE ITEMNMBR = ‘” & sItemNo & “‘ AND NONINVEN = 1”
adoComm.CommandText = sQuery
Set adoRecSet = adoComm.Execute
adoRecSet.MoveFirst
If adoRecSet.EOF True Then
If adoRecSet(0) > 0 Then
MsgBox “This Non-Inventory Item Number has already been entered in an SOP Document.”, vbCritical, “Microsoft Dynamics GP”
KeepFocus = True
CancelLogic = True
adoConn.Close
Exit Sub
End If
End If

adoRecSet.Close

sQuery = “SELECT COUNT(*) AS RECCOUNT FROM SOP10200 WHERE ITEMNMBR = ‘” & sItemNo & “‘ AND NONINVEN = 1”
adoComm.CommandText = sQuery
Set adoRecSet = adoComm.Execute
adoRecSet.MoveFirst
If adoRecSet.EOF True Then
If adoRecSet(0) > 0 Then
MsgBox “This Non-Inventory Item Number has already been entered in an SOP Document.”, vbCritical, “Microsoft Dynamics GP”
KeepFocus = True
CancelLogic = True
adoConn.Close
Exit Sub
End If
End If

adoConn.Close
End Sub

4. Compile your VBA Code and check this on SOP Entry form.

Now, what’s the logic? The code checks whether the entered Non-IV Item Number has already been entered on any of the Open Invoices (SOP10200) or on any of the History Invoices (SOP30300). If so, prompt the user with relevant message and keep the focus on the Item Number field, as long as the user either deletes that line or change the Item Number from that of already been entered.

You can download the package from this link: VBA & ADO – Retrict Duplicate Non-IV Entry on SOP Documents.

Note:

1. This package will work only on Dynamics GP 10.0.

2. Importing this package will override any existing VBA customizations done on SOP Entry form. Make sure that you take a backup of any such customizations.

VAIDY

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s