Restricting SOP Lookups with VBA

A request to restrict SOP Lookup Widnow to only return Documents for which the User is responsible came up yesterday on the http://groups.google.com/group/microsoft.public.greatplains/topics. This is a somewhat common request that is easily addressed using VBA. This example only works for v10 but you could easily convert it to previous version by following the steps in http://mbsguru.blogspot.com/2006/04/ado-connections-through-vba-in.html.

This example assumes that the UserIDs are stored in the Sales Person ID field in Customer Maintenance and that relationship is used to determine Customer Ownership during order entry.

1. Add "Sales Document Numbers" window to Visual Basic.
2. Add the "Document Number" field to the project.
3. Open the SmartList Project in the Visual Basic Editor.
4. Set a Reference in the SmartList Project to "Microsoft ActiveX Data Objects 2.x Library"
5. Copy and paste the following code behind the SaleDocumentNumbersDetail (Grid) window in the SmartList Project:

Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command

Private Sub Grid_BeforeLinePopulate(RejectLine As Boolean)

On Error GoTo Proc_Error:

'Get_NewConnection
Set cn = UserInfoGet.CreateADOConnection
cn.DefaultDatabase = UserInfoGet.IntercompanyID
cmd.ActiveConnection = cn

'Query the database to get check the document
cmd.CommandText = "select h.CUSTNMBR from SOP10100 h inner join
RM00101 c on h.CUSTNMBR = c.CUSTNMBR where c.SLPRSNID = '" &
UserInfoGet.UserID & "' and SOPNUMBE = '" & SOPNumber & "'"
Set rst = cmd.Execute

If rst.EOF = True Then
RejectLine = True
End If

GoTo Procedure_Exit:

Proc_Error:

MsgBox Error$ & " " & Error, vbOKOnly, "Grid_BeforeLinePopulate"

Procedure_Exit:

cmd.ActiveConnection.Close

End Sub