Use ADO to Display Created Date on Customer Maintenance

Here's some weirdness and how to solve it.
If you modify the Customer Maintenance window by dragging out the Created Date field to display on the window, and then create a new customer, the Created Date field will not update, no kidding. I don't know why this happens, it doesn't work this way on the other windows I've checked, but on the Customer Maintenance window, you don't get a new date.
In order to display the Created Date AND have the system update the field when a new record is added is a job for VBA. First, you need to created a local 'Date' field and drag it out onto the window. Create and Link a prompt to the new field. I used 'Date Created' for my prompt.

Add the Customer Maintenance window, the Customer ID and the local Date Created field to your VBA project. Next you will write some VBA code to connect to the database table, look up the value of the Created Date field, and write that value to the date field you created in Modifier. The date will both display to your user and be updated when new records are added.

The scripts I used in my VBA project are below:

-----------------------------------------------------------------------------------------------------------------------------

Option Explicit

Dim cn As New ADODB.Connection

------------------------------------------------------

Private Sub Window_BeforeOpen(OpenVisible As Boolean)

 ' ADO Connection

    Set cn = UserInfoGet.CreateADOConnection

    'set the database to the currently logged in db

    cn.DefaultDatabase = UserInfoGet.IntercompanyID

End Sub

------------------------------------------------------

Private Sub CustomerID_Changed()

    Dim rst As New ADODB.Recordset

    Dim cmd As New ADODB.Command

    Dim sqlstring As String

    If Me.CustomerID <> "" Then

    sqlstring = "select creatddt from rm00101 where CUSTNMBR ='" & Me.CustomerID & "'"

        ' ADO Command

        cmd.ActiveConnection = cn

        ' adCmdText

        cmd.CommandType = 1

        ' Command

        cmd.CommandText = sqlstring

        ' Pass through SQL

        Set rst = cmd.Execute

        If Not (rst.EOF And rst.BOF) Then

          DateCreated = rst!CREATDDT

        End If

        rst.Close

    End If

End Sub

------------------------------------------------------

Private Sub Window_BeforeClose(AbortClose As Boolean)

    ' Close ADO Connection

    cn.Close

    Set cn = Nothing

End Sub

Until next time!
Leslie