Optional Fields update

[SMI168]

Hi there, I need to update the optional fileds for existing customers on AccPac. The optional fields have already been defined on AccPac and if I insert data manually, it accepts it, but I'm struggling to get my code to do the same. The Error that I get is "Invalid Input. Optional field cannot be blank." The dataset isn't blank and i am passing valid data to each optional field. My code is as follows :

Imports System
Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Drawing
Imports System.Linq
Imports System.Text
Imports System.Windows.Forms
Imports ACCPAC.Advantage
Public Class Form1
'Initialise session to ACCPAC...
Public session As Session = Nothing
Public mDBLinkCmpRW As DBLink = Nothing

'Customers Insert/Update Views...
Dim ARCUSTOMER1header As ACCPAC.Advantage.View
Dim ARCUSTOMER1headerFields As ACCPAC.Advantage.ViewFields
Dim ARCUSTOMER1detail As ACCPAC.Advantage.View
Dim ARCUSTOMER1detailFields As ACCPAC.Advantage.ViewFields

Dim ARCUSTOMER20detail As ACCPAC.Advantage.View
Dim ARCUSTOMER20detailFields As ACCPAC.Advantage.ViewFields


Dim ARCUSTSTAT2 As ACCPAC.Advantage.View
Dim ARCUSTSTAT2Fields As ACCPAC.Advantage.ViewFields
Dim ARCUSTCMT3 As ACCPAC.Advantage.View
Dim ARCUSTCMT3Fields As ACCPAC.Advantage.ViewFields
Public Sub InitializeDBLink()
mDBLinkCmpRW = session.OpenDBLink(DBLinkType.Company, DBLinkFlags.ReadWrite)
End Sub
Public Sub OpenCustomer()

'Open Customers View...
ARCUSTOMER1header = mDBLinkCmpRW.OpenView("AR0024")
ARCUSTOMER1headerFields = ARCUSTOMER1header.Fields

ARCUSTOMER1detail = mDBLinkCmpRW.OpenView("AR0400")
ARCUSTOMER1detailFields = ARCUSTOMER1detail.Fields

ARCUSTOMER20detail = mDBLinkCmpRW.OpenView("AR0400")
ARCUSTOMER20detailFields = ARCUSTOMER1detail.Fields

ARCUSTSTAT2 = mDBLinkCmpRW.OpenView("AR0022")
ARCUSTSTAT2Fields = ARCUSTSTAT2.Fields

ARCUSTCMT3 = mDBLinkCmpRW.OpenView("AR0021")
ARCUSTCMT3Fields = ARCUSTCMT3.Fields

End Sub

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

lblDetails.Refresh()
lblDetails.BackColor = Color.AliceBlue
lblDetails.Text = "Updating Customer Optional Fields: "

'Create session to accpac...
session = New Session()

'Initialise session and login...
session.Init("", "", "", "")
session.Open("", "", "", System.DateTime.Now, 0)

InitializeDBLink()
OpenCustomer()

Dim temp As Boolean
temp = ARCUSTOMER1header.Exists
ARCUSTOMER1header.Init()

temp = ARCUSTOMER20detail.Exists
ARCUSTOMER20detail.Init()

temp = ARCUSTSTAT2.Exists
ARCUSTSTAT2.Init()

Dim objconnection As New SqlConnection("server=;database=;user id=;password=")

Dim SQL As String

SQL = "select * from AI_Customers_Optional order by AccNumber"

objconnection.Open()

Dim cmd As New SqlCommand(SQL, objconnection)

Dim ds As New DataSet
Dim SqlDataAdapter = New SqlDataAdapter(cmd).Fill(ds, "AI_Customers_Optional")

On Error GoTo ACCPACErrorHandler

For Each dr As DataRow In ds.Tables(0).Rows

Application.DoEvents()
txtDetail3.Refresh()
txtDetail4.Refresh()
txtDetail3.Text = (dr("AccNumber"))
txtDetail4.Text = (dr("StoreGLN"))

Dim CurrentCust As String
CurrentCust = dr("AccNumber").ToString()

ARCUSTOMER1header.Browse("(IDCUST = " + CurrentCust + ")", True)
ARCUSTOMER1header.Fetch("0")
If (ARCUSTOMER1headerFields.FieldByName("IDCUST").Value) = CurrentCust Then

ARCUSTOMER20detail.GoBottom()
ARCUSTOMER20detail.RecordClear()
ARCUSTOMER20detail.RecordGenerate(False)

ARCUSTOMER1headerFields.FieldByName("IDCUST").SetValue(dr("AccNumber").ToString(), False)

ARCUSTOMER20detailFields.FieldByName("OPTFIELD").SetValue("GRVACCOFFINV", False)
ARCUSTOMER20detailFields.FieldByName("VALIFTEXT").SetValue(dr("GrvAccOffInv").ToString(), False)

ARCUSTOMER20detailFields.FieldByName("OPTFIELD").SetValue("GrvAccOnInv", False)
ARCUSTOMER20detailFields.FieldByName("VALIFTEXT").SetValue(dr("GrvAccOnInv").ToString(), False)

ARCUSTOMER20detailFields.FieldByName("OPTFIELD").SetValue("GrvAccount", False)
ARCUSTOMER20detailFields.FieldByName("VALIFTEXT").SetValue(dr("GrvAccount").ToString(), False)

ARCUSTOMER20detailFields.FieldByName("OPTFIELD").SetValue("StoreBranch", False)
ARCUSTOMER20detailFields.FieldByName("VALIFTEXT").SetValue(dr("StoreBranch").ToString(), False)

ARCUSTOMER20detailFields.FieldByName("OPTFIELD").SetValue("StoreGLN", False)
ARCUSTOMER20detailFields.FieldByName("VALIFTEXT").SetValue(dr("StoreGLN").ToString(), False)

ARCUSTOMER20detail.Insert()
ARCUSTOMER1header.Update()

End If

Next

'Display a message that the record was added...
MessageBox.Show("Customers Updated", "Click OK to continue", MessageBoxButtons.OK, _
MessageBoxIcon.Information, MessageBoxDefaultButton.Button1)

'lblDetails.Text = ""
'txtDetail3.Text = ""
'txtDetail4.Text = ""

ACCPACErrorHandler:
Dim lCount As Long
Dim lIndex As Long
Dim Errors As ACCPAC.Advantage.Errors

If session.Errors Is Nothing Then
'MsgBox(Err.Description)
Else
lCount = session.Errors.Count

If lCount = 0 Then
'MsgBox(Err.Description)
Else
For lIndex = 0 To lCount - 1
MsgBox(session.Errors.Item(lIndex).Message)
Next
session.Errors.Clear()
End If
Exit Sub
Resume Next
End If

DisposeConnection()

End Sub
Public Sub DisposeConnection()

'End session to ACCPAC...
mDBLinkCmpRW.Dispose()
session.Dispose()

End Sub
Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
Me.Close()
End Sub
End Class

 

[ettienne]

You need to compose the views.

 

[SMI168]

Hi Ettienne, thanks for the reply. I have composed the views but i still get the same error. i've composed the views like this :

Public Sub OpenCustomer()

'Open Customers View...
ARCUSTOMER1header = mDBLinkCmpRW.OpenView("AR0024")
ARCUSTOMER1headerFields = ARCUSTOMER1header.Fields

ARCUSTOMER1detail = mDBLinkCmpRW.OpenView("AR0400")
ARCUSTOMER1detailFields = ARCUSTOMER1detail.Fields

ARCUSTOMER20detail = mDBLinkCmpRW.OpenView("AR0400")
ARCUSTOMER20detailFields = ARCUSTOMER1detail.Fields

ARCUSTSTAT2 = mDBLinkCmpRW.OpenView("AR0022")
ARCUSTSTAT2Fields = ARCUSTSTAT2.Fields

ARCUSTCMT3 = mDBLinkCmpRW.OpenView("AR0021")
ARCUSTCMT3Fields = ARCUSTCMT3.Fields


Dim vd1 As ACCPAC.Advantage.View() = {ARCUSTOMER1header}
ARCUSTOMER20detail.Compose(vd1)

Dim vd2 As ACCPAC.Advantage.View() = {ARCUSTOMER1detail, ARCUSTOMER20detail}
ARCUSTOMER1header.Compose(vd2)


End Sub

What could i be doing wrong here?

 

[ettienne]

1. Record a macro in Accpac and open the AR Customers screen, it will be clear what you need to do.
2. You don't need the ARCUSTSTAT and ARCUSTCMT views.
3. Get rid of the noise that is recorded, like the Temp= lines.
4. Why are you opening AR0400 twice?

 

[ettienne]

AR0024 only has one view in the composition:
ARCUSTOMER1header.Compose(ARCUSTOMER1detail)

and AR0400:
ARCUSTOMER1detail.Compose(ARCUSTOMER1header)