Search the GP Tables
Search by TABLE NAME
Search by FIELD NAME
These are our SOP scripts. All of our scripts for Dynamics GP can be found here
DynDeveloper.com

DynDeveloper.com

Table Edits Forum Articles
Table Edits 0 Table Edits 0 Table Edits 0
Sign Up Now!Log In
rmorgan 1/11/2017 1:33:21 PM

Adding a Date field with VBA to the Item Price List Maintenance screen

UPDATE: Here is the code. I have it working, but do think it needs some tweaking.  Using the scroll buttons on the bottom of the screen results in the "Do you want to Save..." popup window occurring each time. Not sure if there's a way around this, but I'm willing to live with it as is.

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

Private Sub EffectiveDate_AfterGotFocus()

End Sub
Sub openConnection()
    Set cn = UserInfoGet.CreateADOConnection
    cn.DefaultDatabase = UserInfoGet.IntercompanyID
End Sub

Private Sub EffectiveDate_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)
 If cn.State = 0 Then
    openConnection
 End If
 If Me.ItemNumber <> "" Then
    Dim cmd As New ADODB.Command
    cmd.ActiveConnection = cn
    cmd.CommandType = adCmdText
    cmd.CommandText = "select ITEMNMBR from IV00101EXT where ITEMNMBR = ?"
    cmd.Parameters.Append cmd.CreateParameter("@itemNumber", adVarChar, adParamInput, 21, Me.ItemNumber)
    Set rst = cmd.Execute
    If Not rst.EOF Then
        Dim cmd2 As New ADODB.Command
        cmd2.ActiveConnection = cn
        cmd2.CommandType = adCmdText
        cmd2.CommandText = "UPDATE IV00101EXT SET PricingEffectiveDate = ? where ITEMNMBR = ?"
        cmd2.Parameters.Append cmd2.CreateParameter("@EffectiveDate", adDBDate, adParamInput, , Me.EffectiveDate)
        cmd2.Parameters.Append cmd2.CreateParameter("@itemNumber", adVarChar, adParamInput, 21, Me.ItemNumber)
        Set rst = cmd2.Execute
    Else
        If Me.EffectiveDate <> "0/0/0000" Then
            Dim cmd3 As New ADODB.Command
            cmd3.ActiveConnection = cn
            cmd3.CommandType = adCmdText
   
            cmd3.CommandText = "INSERT INTO IV00101EXT (ITEMNMBR,PricingEffectiveDate) VALUES(?,?)"
            cmd3.Parameters.Append cmd3.CreateParameter("@itemNumber", adVarChar, adParamInput, 21, Me.ItemNumber)
            cmd3.Parameters.Append cmd3.CreateParameter("@EffectiveDate", adDBDate, adParamInput, , Me.EffectiveDate)

            Set rst = cmd3.Execute
        End If
    End If
  End If
End Sub

Private Sub EffectiveDate_Changed()

End Sub

Private Sub ItemNumber_AfterUserChanged()

End Sub

Private Sub ItemNumber_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)
If cn.State = 0 Then
    openConnection
 End If

 Dim cmd As New ADODB.Command
    cmd.ActiveConnection = cn
    cmd.CommandType = adCmdText
    cmd.CommandText = "SELECT ITEMNMBR,PricingEffectiveDate FROM IV00101EXT where ITEMNMBR = ?"
    cmd.Parameters.Append cmd.CreateParameter("@itemNumber", adVarChar, adParamInput, 21, Me.ItemNumber)
    Set rst = cmd.Execute
    If Not rst.EOF Then
        Me.EffectiveDate = rst("PricingEffectiveDate")
    End If
End Sub

Private Sub ItemNumber_Changed()
 If cn.State = 0 Then
    openConnection
 End If
    Dim cmd As New ADODB.Command
    cmd.ActiveConnection = cn
    cmd.CommandType = adCmdText
    cmd.CommandText = "SELECT ITEMNMBR,PricingEffectiveDate FROM IV00101EXT where ITEMNMBR = ?"
    cmd.Parameters.Append cmd.CreateParameter("@itemNumber", adVarChar, adParamInput, 21, Me.ItemNumber)
    Set rst = cmd.Execute
    If Not rst.EOF Then
        Me.EffectiveDate = rst("PricingEffectiveDate")
    Else
        Me.EffectiveDate.Empty = True
    End If
End Sub

Private Sub Window_AfterOpen()

End Sub

Private Sub Window_BeforeOpen(OpenVisible As Boolean)

End Sub

ORIGINAL QUESTION:

I have a customer that needs a date field (Effective Date) added to the Item Price List Maintenance screen. I'm new to developing in GP and have gotten as far as adding the field to the screen through Modifier and adding the fields to VBA, but I do not know how to add the necessary code. First, I think DUOS is fine for what I need. Second, all that needs to happen is a) bring up the date that is already stored with the item (if there is one) and b) save the date entered back to the DUOS table. There is no need to make it a required field. I couldn't find any articles that had an example I could work with.  Can you help?

Screenshots:

 

 

Version: GP 2013
Section: Dynamics GP, VBA








Table Definition Quick Links
All Tables
SOP Tables
RM Tables
GL Tables
POP Tables
HR Tables
PM Tables
UPR Tables
IV Tables
Olympic Tables
site