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:
