Microsoft Dynamics GP

Dynamics GP – Copy address button using VBA & Modifier

Tim Wappat

Nov 25, 2010 • 8 min read

Introduction - why add a copy button?

Dynamics GP Sales Order Processing (SOP) allows orders to be placed against debtors (customers). For each customer you can have any number of addresses stored, this address book can then be used by sales staff when an order is placed to avoid rekeying the information.

We needed to start storing an address per contact as the contact’s name is captured within the address block. Some delivery sites for national organisations are huge, many thousands of people are fed from one central goods in delivery point. This means that each contact email address is essentially the same bar for the contact name.

Requirement

To speed up order entry and address capture and accuracy, a requirement was identified to allow SOP users to copy a master address for these larger site. This would then merely require sales to add a contact name to the account master template address they copy, rather than manually copying and pasting the address each time.

Add a copy button & combo box to address windows

Using Microsoft Dynamics Modifier, select the address entry screens for GP. You might choose

DebtorAddressMaintenance & SalesShipToAddressEntry, for example. If you use 3rd party dictionaries, watch for alternatives to these in those dictionaries.

Drop a new button onto each form, rename the button btnCopy for this example.
Drop a drop down combobox to the form, name it ddlAddress

When the form is loaded with data, the combobox will be filled with the list of address ID’s available for this customer. By default the default address ID will select itself, and the selected address ID will be used as the source for the copy address function. The copy address functionality will be invoked by the pressing of the btnCopy.

Save the modified form, if you do not currently have permissions set up for the modified form, please ensure your sers are pointed by security at the new form.

VBA behind the form

Now it must be remembered to add the window and fields to VBA that were created. Those are the button and the combobox, also needed is to add the customer number field and each of the address fields that are to be copied to VBA.

For this article I will assume we are working with the DebtorAddressMaintenance window. For this window when a new debtor number is entered, then the combobox that was added must be populated with the available AddressID’s for that customer. The combobox is also required to default to the customer address ID selected in the main customer maintenance card as the default ship address.

SQL

We must query the Dynamics GP database for all the address IDs for this customer stored in the “address book”.

 ALTER PROCEDURE [yourschema].[SOP_GetCustomerAddressCodes]
   @CUSTNMBR  varchar(15)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT     RM00102.ADRSCODE, ISNULL(RM00101.PRSTADCD,'') 
        as SHIPPINGADRSCODE
    FROM RM00102
    LEFT JOIN 
         RM00101 
    ON RM00102.CUSTNMBR=RM00101.CUSTNMBR
    WHERE  
      (RM00102.CUSTNMBR = @CUSTNMBR) 
  
END

And to get a specific address

''' sql
ALTER PROCEDURE [yourschema].[SOP_GetCustomerAddress]
@CUSTNMBR varchar(15),
@ADRSCODE varchar(15)
AS
BEGIN
SET NOCOUNT ON;
SELECT CUSTNMBR, ADRSCODE, SLPRSNID, UPSZONE, SHIPMTHD,
TAXSCHID, CNTCPRSN, ADDRESS1, ADDRESS2, ADDRESS3, COUNTRY, CITY, STATE,
ZIP, PHONE1, PHONE2, PHONE3, FAX, MODIFDT, CREATDDT, GPSFOINTEGRATIONID,
INTEGRATIONSOURCE, INTEGRATIONID, CCode, DECLID,
LOCNCODE, SALSTERR, USERDEF1, USERDEF2, DEX_ROW_ID
FROM RM00102
WHERE (CUSTNMBR = @CUSTNMBR)
AND (ADRSCODE = @ADRSCODE)

END
'''

Address Class

A class called clsSOPAddress is made in the class modules section of your VBA project as follows;

Option Explicit
Private m_CustomerNumber As String
Private m_AddressID As String
Private m_Contact As String
Private m_Address1 As String
Private m_Address2 As String
Private m_Address3 As String
Private m_City As String
Private m_County As String
Private m_PostCode As String
Private m_CountryCode As String
Private m_Country As String
Private m_Phone1 As String
Private m_Phone2 As String
Private m_Phone3 As String
Private m_Fax As String
Private m_CarrierZone As String
Private m_ShippingMethod As String
Private m_TaxScheduleID As String
Private m_SiteID As String
Private m_SalesPersonID As String
Private m_TerritoryID As String
Private m_UserDefined1 As String
Private m_UserDefined2 As String



Public Property Let CustomerNumber(CustNumber As String)
   m_CustomerNumber = CustNumber
End Property
Public Property Get CustomerNumber() As String
   CustomerNumber = m_CustomerNumber
End Property

Public Property Let AddressID(Adrid As String)
  m_AddressID = Adrid
End Property
Public Property Get AddressID() As String
  AddressID = m_AddressID
End Property

Public Property Get Contact() As String
  Contact = m_Contact
End Property
Public Property Get Address1() As String
  Address1 = m_Address1
End Property
Public Property Get Address2() As String
  Address2 = m_Address2
End Property
Public Property Get Address3() As String
  Address3 = m_Address3
End Property
Public Property Get City() As String
  City = m_City
End Property
Public Property Get County() As String
  County = m_County
End Property
Public Property Get Postcode() As String
  Postcode = m_PostCode
End Property
Public Property Get CountryCode() As String
  CountryCode = m_CountryCode
End Property
Public Property Get Country() As String
  Country = m_Country
End Property
Public Property Get Phone1() As String
  Phone1 = m_Phone1
End Property
Public Property Get Phone2() As String
  Phone2 = m_Phone2
End Property
Public Property Get Phone3() As String
  Phone3 = m_Phone3
End Property
Public Property Get Fax() As String
  Fax = m_Fax
End Property
Public Property Get CarrierZone() As String
  CarrierZone = m_CarrierZone
End Property
Public Property Get ShippingMethod() As String
  ShippingMethod = m_ShippingMethod
End Property
Public Property Get TaxScheduleID() As String
  TaxScheduleID = m_TaxScheduleID
End Property
Public Property Get SiteID() As String
  SiteID = m_SiteID
End Property
Public Property Get SalespersonID() As String
  SalespersonID = m_SalesPersonID
End Property
Public Property Get TerritoryID() As String
  TerritoryID = m_TerritoryID
End Property
Public Property Get UserDefined1() As String
 UserDefined1 = m_UserDefined1
End Property
Public Property Get UserDefined2() As String
 UserDefined2 = m_UserDefined2
End Property

Public Sub LoadAddress()
    On Error GoTo ErrorHandler
    Dim SQLRec As New ADODB.Recordset   'holds recordset SQL
    
    Dim Cmd1 As ADODB.Command
    Set Cmd1 = New ADODB.Command
    
    Dim SQLCon As ADODB.Connection
    Set SQLCon = New ADODB.Connection
    
    If SQLCon.State <> adStateOpen Then SQLCon.Open Microsoft_Dynamics_GP.GetConnectionStr
    Cmd1.ActiveConnection = SQLCon
    Cmd1.CommandText = "SOP_GetCustomerAddress"
    Cmd1.CommandType = adCmdStoredProc
    Dim prm1 As New ADODB.Parameter
    Dim prm2 As New ADODB.Parameter
            
    Set prm1 = Cmd1.CreateParameter("@CUSTNMBR", adVarChar, adParamInput, 15, Trim(Me.CustomerNumber))
    Set prm2 = Cmd1.CreateParameter("@SOPNUMBE", adVarChar, adParamInput, 15, Trim(Me.AddressID))
    With Cmd1
        .Parameters.Append prm1
        .Parameters.Append prm2
    End With
        
    SQLRec.Open Cmd1.Execute
           If Not SQLRec.BOF And Not SQLRec.EOF Then
              m_Contact = Trim(SQLRec.Fields("CNTCPRSN").Value)
              m_Address1 = Trim(SQLRec.Fields("ADDRESS1").Value)
              m_Address2 = Trim(SQLRec.Fields("ADDRESS2").Value)
              m_Address3 = Trim(SQLRec.Fields("ADDRESS3").Value)
              m_City = Trim(SQLRec.Fields("CITY").Value)
              m_County = Trim(SQLRec.Fields("STATE").Value)
              m_PostCode = Trim(SQLRec.Fields("ZIP").Value)
              m_Phone1 = Trim(SQLRec.Fields("PHONE1").Value)
              m_Phone2 = Trim(SQLRec.Fields("PHONE2").Value)
              m_Phone3 = Trim(SQLRec.Fields("PHONE3").Value)
              m_Fax = Trim(SQLRec.Fields("FAX").Value)
              m_CountryCode = Trim(SQLRec.Fields("CCODE").Value)
              m_Country = Trim(SQLRec.Fields("COUNTRY").Value)
              m_CarrierZone = Trim(SQLRec.Fields("UPSZONE").Value)
              m_ShippingMethod = Trim(SQLRec.Fields("SHIPMTHD").Value)
              m_TaxScheduleID = Trim(SQLRec.Fields("TAXSCHID").Value)
              m_SiteID = Trim(SQLRec.Fields("LOCNCODE").Value)
              m_SalesPersonID = Trim(SQLRec.Fields("SLPRSNID").Value)
              m_TerritoryID = Trim(SQLRec.Fields("SALSTERR").Value)
              m_UserDefined1 = Trim(SQLRec.Fields("USERDEF1").Value)
              m_UserDefined2 = Trim(SQLRec.Fields("USERDEF2").Value)
                     
                
           Else
              'Address not found...
           End If
 
         
    'Close database connection and clean up
    If CBool(SQLRec.State And adStateOpen) = True Then SQLRec.Close
    Set SQLRec = Nothing
     
    If CBool(SQLCon.State And adStateOpen) = True Then SQLCon.Close
    Set SQLCon = Nothing

    Set prm1 = Nothing
    Set prm2 = Nothing
    Set Cmd1 = Nothing
    
    'Break before error hander
Exit Sub
ErrorHandler:
  MsgBox "VBA Error trying to get address" + vbCrLf _
      & vbCrLf & Err.Description, vbCritical + vbOKOnly, "Error in get address"
End Sub

Public Function GetAvailableAddressCodes() As String()
     On Error GoTo ErrorHandler
    Dim Results() As String
    ReDim Results(0 To 0)
    Dim SQLRec As New ADODB.Recordset   'holds recordset SQL
    
    Dim Cmd1 As ADODB.Command
    Set Cmd1 = New ADODB.Command
    
    Dim SQLCon As ADODB.Connection
    Set SQLCon = New ADODB.Connection
    
    If SQLCon.State <> adStateOpen Then SQLCon.Open Microsoft_Dynamics_GP.GetConnectionStr
    Cmd1.ActiveConnection = SQLCon
    Cmd1.CommandText = "SOP_GetCustomerAddressCodes"
    Cmd1.CommandType = adCmdStoredProc
    Dim prm1 As New ADODB.Parameter
             
    Set prm1 = Cmd1.CreateParameter("@CUSTNMBR", adVarChar, adParamInput, 15, Trim(Me.CustomerNumber))
     With Cmd1
        .Parameters.Append prm1
     End With
        
    SQLRec.Open Cmd1.Execute
    
    Dim RecordCount As Integer
    RecordCount = 0
 
    Do While Not SQLRec.EOF
    
     ReDim Preserve Results(RecordCount)
     If SQLRec.Fields("ADRSCODE").Value = SQLRec.Fields("SHIPPINGADRSCODE").Value Then
      Results(RecordCount) = "%" & SQLRec.Fields("ADRSCODE").Value
     Else
      Results(RecordCount) = SQLRec.Fields("ADRSCODE").Value
     End If
    
     RecordCount = RecordCount + 1
     SQLRec.MoveNext
    Loop
      
    'Close database connection and clean up
    If CBool(SQLRec.State And adStateOpen) = True Then SQLRec.Close
    Set SQLRec = Nothing
     
    If CBool(SQLCon.State And adStateOpen) = True Then SQLCon.Close
    Set SQLCon = Nothing

    Set prm1 = Nothing
    Set Cmd1 = Nothing
    GetAvailableAddressCodes = Results
    'Break before error hander
Exit Function
ErrorHandler:
  MsgBox "VBA Error trying to get address" + vbCrLf _
      & vbCrLf & Err.Description, vbCritical + vbOKOnly, "Error in get address"
End Function

This class will handle fetching of the addresses. The default address is marked by a % that allows the combo box to by default select the address that contains that symbol and use it as default selection.

Now introduce a new module, utils, in the VBA project, into it add the following function for getting the list of addresses;

Public Function GetAddresses(DebtorID As String) As String()
On Error GoTo ErrorHandler

Dim osopAddress As New clsSOPAddress
  osopAddress.CustomerNumber = DebtorID
  GetAddresses = osopAddress.GetAvailableAddressCodes
Exit Function
ErrorHandler:
    MsgBox "VBA Error trying to get available address ids." + vbCrLf _
    + " " + vbCrLf _
       & vbCrLf & Err.Description, vbCritical + vbOKOnly, _
      "Error in GetAddress"
End Function

We can now call this function from our address form in order to fill our combobox, We do this by using a unsupported (but fully working) method of calling dex code from VBA. In this case we are running this form from a 3rd party credit control form, hence the referenced dictionary.

Private Sub DebtorID_Changed()
On Error GoTo ErrorHandler
    Dim CompilerApp As Object
    Dim CompilerMessage As String
    Dim CompilerError As Integer
    Dim CompilerCommand As String
  
    Set CompilerApp = CreateObject("Dynamics.Application")
    CompilerCommand = ""
    CompilerCommand = CompilerCommand & _
        "clear '(L) ddlAddresses' of window RM_Customer_Address of form RM_Customer_Address;"_
         & vbCrLf
    
    ReDim oAddressList(0 To 0) 'initialise to zero for ubound checking
    If Not DebtorID.Empty Then
    oAddressList = utils.GetAddresses(DebtorID.Value)
    Dim currentIndex As Integer
    Dim defaultIndex As Integer
    defaultIndex = -1
        For currentIndex = LBound(oAddressList) To UBound(oAddressList)
            If Left(oAddressList(currentIndex), 1) = "%" Then
                CompilerCommand = CompilerCommand & "add item "" " & _
                Mid(oAddressList(currentIndex), 2) & """, " & CStr(currentIndex) & vbCrLf
                defaultIndex = currentIndex
            Else
                CompilerCommand = CompilerCommand & "add item "" " & oAddressList(currentIndex) _
                & """, " & CStr(currentIndex) & vbCrLf
            End If
             CompilerCommand = CompilerCommand &_
                 "   to '(L) ddlAddresses' of window RM_Customer_Address of form RM_Customer_Address;" _
                  & vbCrLf
        Next currentIndex
    End If
   If defaultIndex = -1 Then defaultIndex = 0
    CompilerCommand = CompilerCommand & _
        "'(L) ddlAddresses' of window RM_Customer_Address of form RM_Customer_Address = "_
         & Str(defaultIndex + 1) & ";" & vbCrLf

    ' Execute SanScript
    CompilerApp.CurrentProductID = 2422 ' Nolans Credit Control
    CompilerApp.CurrentProduct = CompilerApp.CurrentProduct & "!Modified"
    CompilerError = CompilerApp.ExecuteSanscript(CompilerCommand, CompilerMessage)
    If CompilerError <> 0 Then
        MsgBox CompilerMessage
    End If
'exit sub before the error handling kicks in
Exit Sub
ErrorHandler:
  MsgBox "An Error occured on debtorid change." + vbCrLf + "" & vbCrLf & Err.Description,_
         vbCritical + vbOKOnly, "Error in DebtorID.Changed"
End Sub

Finally the copy itself

When the uses presses btncopy the address associated with the addresscode shown by the combobox is got and it is used to populate the form fields.

''' vbnet
Private oAddressList() As String ' holds addresslist for lookup

Private Sub Copy_BeforeUserChanged(KeepFocus As Boolean, _
CancelLogic As Boolean)
On Error GoTo ErrorHandler
If Not DebtorID.Empty Then
Dim oAddress As clsSOPAddress
If Left(oAddressList(ddlAddresses.Value - 1), 1) = "%" Then
Set oAddress = utils.GetCustomerAddress(Me.DebtorID, _
Mid(oAddressList(ddlAddresses.Value - 1), 2))
Else
Set oAddress = utils.GetCustomerAddress(Me.DebtorID, _
oAddressList(ddlAddresses.Value - 1))
End If

  Contact.Value = oAddress.Contact
  Address.Value = oAddress.Address1
  Address1.Value = oAddress.Address2
  Address2.Value = oAddress.Address3
  City.Value = oAddress.City
  County.Value = oAddress.County
  Postcode.Value = oAddress.Postcode
  Country.Value = oAddress.Country
  CountryCode.Value = oAddress.CountryCode
  Phone1.Value = oAddress.Phone1
  Phone2.Value = oAddress.Phone2
  Phone3.Value = oAddress.Phone3
  Fax.Value = oAddress.Fax
  CarrierZone.Value = oAddress.CarrierZone
  ShippingMethod.Value = oAddress.ShippingMethod
  TaxScheduleID.Value = oAddress.TaxScheduleID
  SiteID.Value = oAddress.SiteID
  SalespersonID.Value = oAddress.SalespersonID
  TerritoryID.Value = oAddress.TerritoryID
  UserDefined1.Value = oAddress.UserDefined1

End If
'exit sub before the error handling kicks in
Exit Sub