Quick Lookups with Modifier & VBA

It seems that the last few times I have implemented Contract Administration I've had to work around the inherent restrictions on the Contract Number field by using another field, in this case User Defined 1, to track an internal Customer Contract Number; in this example "Job Number". Maybe I'm missing something here but this is how I was able to work around this.

It's nice that you can move the User Defined 1 field onto the Contract Entry/Update window and add it to the Contract Lookup with Modifier in just seconds. This isn't as simple on some other windows in GP:




























The problem was that Contracts needed to be retrieved using this Job Number value. Depending on how you address that, it could have a much more significant impact on the project budget. In this case, the customer was just as happy entering the Job Number to retrieve the Contract Record as they would be if the field were added to the Find Function in the Contract Lookup window.

It's important to note in this example that Job Number uniqueness is forced across Contracts.

To do this in GP 10; first, create some Sub Procedures to make and close a connection to the database:

Option Explicit

'Declare variables for the ADO Connection
Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command

Public Sub Get_ADOConnection()

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

End Sub

Public Sub Close_ADOConnection()

cmd.ActiveConnection.Close

End Sub

Then, add some code behind UserDefined1 on the Before User Changed event to force uniqueness of Job Number across Contracts:

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

On Error GoTo Proc_Error:

If ContractNumber.Empty = False Then

'Get_NewConnection
Get_ADOConnection

'Make sure the job number is not already assigned to a contract

cmd.CommandText = "select top 1 CONTNBR from SVC00600 where USERDEF1 = '" & Me.UserDefined1 & "'"
Set rst = cmd.Execute

'If a contact exists for this job notify the user and force them to enter a unique job number
If rst.EOF = False Then
MsgBox ("This Job Number has already been assigned to contract " & RTrim(rst.Fields("CONTNBR")) & ".")
CancelLogic = True
KeepFocus = True
End If

Close_ADOConnection

End If

GoTo Procedure_Exit:

Proc_Error:

MsgBox Error$ & " " & Error, vbOKOnly, "UserDefined1_BeforeUserChanged"

Procedure_Exit:

End Sub

Finally, add some code behind User Defined 1 on the After User Changed Event to query the Contract Master Table and retrieve the Contract to which the Job Number has previously been assigned:

Private Sub UserDefined1_AfterUserChanged()

On Error GoTo Proc_Error:

If ContractNumber.Empty = True Then

'Get_NewConnection
Get_ADOConnection

'Lookup the Contract for this Job
cmd.CommandText = "select top 1 CONTNBR from SVC00600 where USERDEF1 = '" & Me.UserDefined1 & "'"
Set rst = cmd.Execute

If rst.EOF = False Then
Clear = 1
ContractNumber = rst.Fields("CONTNBR")
Else
MsgBox ("This Job Number has not been assigned to a contract.")
End If

Close_ADOConnection

End If

GoTo Procedure_Exit:

Proc_Error:

MsgBox Error$ & " " & Error, vbOKOnly, "UserDefined1_BeforeUserChanged"

Procedure_Exit:

End Sub

The end result of this not only can you easily assign the Job Number (User Defined 1) to the Contract and view the Job Number in the Contract Lookup Window but, most importantly, you can pull the Contract into the Contract Entry/Update Window by keying into the Job Number field.

Simple, quick, cost effective solution that could be applied to a variety of windows in GP.