Changing the Ship To Address and Ship Dates via VBA.

Over the years, one of the issues I’ve repeatedly encountered is the wasted time in changing Ship To Addresses and Ship Dates for multiple line item orders. My last employer made a product commonly sold with new building construction projects. Therefore, they often did not have a Ship To Address because the building was still under construction and may not be able to accept deliveries.

When you have a multiple line item order, you must change every line item manually to match. When dealing with 50 line item orders, as my last employer regularly had, someone has to spend 15 minutes changing each line individually. The code provided does that automatically. Now would be a good time for the standard disclaimer.

Important: All information on this site is provided “as is” for informational purposes only.
In particular, all code samples are provided “as is” without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.

If you should decide to use any of the code from this site, make sure you test it thoroughly on a test company (or better yet a test server) before you ever use it on your live data.

First, you must initialize the form and create the control in the INIT Event of the SO Form.


Right Click on SO and choose components.


Choose the INIT Event

The code below creates a new connection string to the database if M2M has dropped it for some reason, and it draws a button on the form.

Public Function INIT() As Boolean

    If Trim(connstr) = "" Then
        connstr = GetCs1()
    End If
    With ThisForm.m2mpageframe1.page1
    .AddObject "cmdRollDate", "M2MButton"
    .cmdRollDate.Top = .txtFduedate.Top + 20
    .cmdRollDate.Left = 370
    .cmdRollDate.Width = 100
    .cmdRollDate.Caption = "Roll Date & Addr"
    .cmdRollDate.Visible = True
    End With
    INIT = True
End Function

What follows is the rest of the code necessary for this project. I’ll briefly explain it and if anyone has any questions feel free to ask in the comments section.

First, you must assign the code to an event, which in this case is the click event of your button. Please remember that when changing the button attributes you use something like M2mpageframe1.Page1.cmdRollDate.blahblah, however when creating the function for the event you replace those periods with underscores as seen in the code below. The next line is a simple error handler which will display any errors in a message box. Following this are the object declarations. A message box pops up verifying that the user really wants to change the shipping information because it’s entirely possible that the user clicked the button accidentally.

Next, variables are assigned values derived from the form. The code checks to make sure that there is a valid sales order number and due date before continuing. A database connection and a recordset object are then created. The recordset is set to use the database connection and the two update statements are performed. One updates the SOITEM table with the Due Date from the first tab of the Sales Order screen, and the second updates the Ship To Addresses in the SORELS to match the Sales Order default as well.

Afterward, the database connection is closed, and both the recordset and connection are released. The user is notified via message box that the process was successful and the screen is refreshed to show the changes.

Public Function M2mpageframe1_Page1_cmdRollDate_CLICK() As Boolean
    On Error GoTo errCode

    Dim Value As Single
    Dim DueDate As String
    Dim ShipAddress As String
    Dim SoNo As String
    Dim SoRev As String
    Dim CN As ADODB.Connection
    Dim RS As ADODB.Recordset
    Dim SelectStr As String

    If MsgBox("Roll Default Due Date & Addr to all items on this order?", vbYesNo) <> vbYes Then Exit Function
    DueDate = ThisForm.m2mpageframe1.page1.txtFduedate.Value
    ShipAddress = ThisForm.m2mpageframe1.page2.txtfshptoaddr.Value
    SoNo = ThisForm.txtFsono.Text
    SoRev = ThisForm.txtFsorev.Text

    If Trim(SoNo) = "" Then Exit Function
    If Trim(DueDate) <> "" Then

Set CN = New ADODB.Connection
        CN.Open connstr
        Set RS = New ADODB.Recordset
        Set RS.ActiveConnection = CN

        RS.Open ("UPDATE SoItem Set fDueDate = '" & DueDate & "' WHERE soitem.fsono = '" & _
                        SoNo & "'")

        RS.Open ("UPDATE SoRels Set fDueDate = '" & DueDate & "', fshptoaddr = '" & ShipAddress & "' WHERE sorels.fsono = '" & _
                        SoNo & "'")
        Set RS = Nothing
        Set CN = Nothing
    End If
    M2mpageframe1_Page1_cmdRollDate_CLICK = True
    MsgBox "Date Rolled"
    Exit Function
    MsgBox "ERROR: " & Err.Number & " " & Err.Description
End Function

This is a fairly simple example of M2M Database manipulation via VBA for you to use and customize to fit your needs. Please remember that whenever you are writing to your M2M Database directly, you risk data corruption should you make a mistake and this could cost you a great deal to have corrected.

I’m interested to read people’s opinions of my code and any improvement suggestions offered.

2 comments to Changing the Ship To Address and Ship Dates via VBA.

  • Scott

    Thanks for the code. I had a user request this a while back and I had not gotten to it yet (other priorities).

    I do have a few suggestions.

    Currently anyone with “View” rights can change the info. This is a major security risk and can easily be solved. Simply check the cmode of the form and only let let the update statement run if it is in modify mode. This will also prevent the user from changing the info once the item(s) have shipped.

    My other thought is the way your SQL statement is done. I prefer to use a string variable called “SQL” and set my statment to it. I then do I have found this makes error detection and correction easier.

  • Scott, you’re right about the view vs edit rights. I had to write this from scratch on my personal machine and simply forgot that.

    Also, you could control the status of the button during M2M events such as Add, Add & Carry, by changing the enabled property.

    Thanks again for the catch. This is one of the primary reasons why I started this blog.

Leave a Reply




You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>