Need some advanced help on this one!
in [General]
|
From: Earl.AKA J.Alladien in access forum!! on 21 Nov 2009 16:47 Hi guys, I found a sample Access program that takes data from its database to generate an EDI EDIFACT ORDERS file (OrdersS93a.edi). Here is the code: Private Sub Command0_Click() Dim oEdiDoc As Fredi.ediDocument Dim oSchema As Fredi.ediSchema Dim oInterchange As Fredi.ediInterchange Dim oGroup As Fredi.ediGroup Dim oTransactionset As Fredi.ediTransactionSet Dim oSegment As Fredi.ediDataSegment Dim oConn As ADODB.Connection Dim oRsInterchange As ADODB.Recordset Dim oRsGroup As ADODB.Recordset Dim oRsTransactionSet As ADODB.Recordset Dim oRsPOMaster As ADODB.Recordset Dim oRsPODetail As ADODB.Recordset Dim sConn As String Dim sSefFile As String Dim sEdiFile As String Dim sPath As String Dim i As Integer Dim nCount As Integer Dim sEntity As String Dim nIndex As Integer Dim iItemCount As Integer Dim nInstance As Integer Dim mInstance As String sPath = CurrentProject.Path & "\" sConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & sPath & "genOrders.mdb" Set oConn = New ADODB.Connection oConn.Open sConn 'The InterchangeIndex table contains information of Interchange. Set oRsInterchange = New ADODB.Recordset 'The TransactionSetIndex table contains information of the Transaction Sets in the 'Interchanges Set oRsTransactionSet = New ADODB.Recordset 'The POMaster table contains information of the Purchase Order in the Transaction Set Set oRsPOMaster = New ADODB.Recordset 'The PODetail table contains information of the detailed items of Purchase Orders Set oRsPODetail = New ADODB.Recordset sSefFile = sPath & "ORDERS_S93A.sef" sEdiFile = sPath & "ordersS93a.edi" Set oEdiDoc = CreateObject("Fredi.ediDocument") Set oSchema = oEdiDoc.LoadSchema(sSefFile, 0) oEdiDoc.SegmentTerminator = "'" oEdiDoc.ElementTerminator = "+" oEdiDoc.CompositeTerminator = ":" oEdiDoc.ReleaseIndicator = "?" oRsInterchange.Open "Select * from InterchangeIndex", oConn, adOpenDynamic, adLockOptimistic Do While Not oRsInterchange.EOF Set oInterchange = oEdiDoc.CreateInterchange("UN", "S93A") Set oSegment = oInterchange.GetDataSegmentHeader oSegment.DataElementValue(1, 1) = "UNOB" oSegment.DataElementValue(1, 2) = "1" oSegment.DataElementValue(2, 1) = oRsInterchange("SenderID").Value oSegment.DataElementValue(2, 2) = oRsInterchange("SenderID_Qlfr").Value oSegment.DataElementValue(2, 3) = "MFGB" oSegment.DataElementValue(3, 1) = oRsInterchange("ReceiverID").Value oSegment.DataElementValue(3, 2) = oRsInterchange("ReceiverID_Qlfr").Value oSegment.DataElementValue(3, 3) = "ROUTE ADDR" oSegment.DataElementValue(4, 1) = "970101" oSegment.DataElementValue(4, 2) = "1230" oSegment.DataElementValue(5) = oRsInterchange("InterchangeControlNo").Value oSegment.DataElementValue(7) = oRsInterchange("Application").Value oSegment.DataElementValue(11) = "1" oRsTransactionSet.Open "Select * from TransactionSetIndex where InterchangeKey = " & oRsInterchange("InterchangeKey").Value, oConn, adOpenDynamic, adLockOptimistic Do While Not oRsTransactionSet.EOF Set oTransactionset = oInterchange.CreateTransactionSet("ORDERS") Set oSegment = oTransactionset.GetDataSegmentHeader oSegment.DataElementValue(1) = oRsTransactionSet("MessageRefNo").Value oSegment.DataElementValue(2, 1) = oRsTransactionSet("MessageType").Value oSegment.DataElementValue(2, 2) = oRsTransactionSet("MessageVersion").Value oSegment.DataElementValue(2, 3) = oRsTransactionSet("MessageRelease").Value oSegment.DataElementValue(2, 4) = "UN" oSegment.Last oRsPOMaster.Open "Select * from POMaster where TsKey = " & oRsTransactionSet("TsKey").Value, oConn, adOpenDynamic, adLockOptimistic Do While Not oRsPOMaster.EOF Set oSegment = oTransactionset.CreateDataSegment("BGM") oSegment.DataElementValue(1, 1) = "221" oSegment.DataElementValue(2) = oRsPOMaster("PONumber").Value oSegment.DataElementValue(3) = "9" Set oSegment = oTransactionset.CreateDataSegment("DTM") oSegment.DataElementValue(1, 1) = "4" oSegment.DataElementValue(1, 2) = oRsPOMaster("PODate").Value oSegment.DataElementValue(1, 3) = "102" Set oSegment = oTransactionset.CreateDataSegment("DTM(2)") oSegment.DataElementValue(1, 1) = "3" oSegment.DataElementValue(1, 2) = oRsPOMaster("InvDate").Value oSegment.DataElementValue(1, 3) = "102" Set oSegment = oTransactionset.CreateDataSegment("NAD(1)\NAD") oSegment.DataElementValue(1) = "BY" oSegment.DataElementValue(2, 1) = oRsPOMaster("BuyerId").Value oSegment.DataElementValue(2, 3) = "92" oSegment.DataElementValue(4, 1) = oRsPOMaster("BuyerName").Value Set oSegment = oTransactionset.CreateDataSegment("NAD(2)\NAD") oSegment.DataElementValue(1) = "BT" oSegment.DataElementValue(2, 1) = oRsPOMaster("BillToID").Value oSegment.DataElementValue(2, 3) = "92" oSegment.DataElementValue(4, 1) = oRsPOMaster("BillToName").Value oSegment.DataElementValue(5, 1) = oRsPOMaster("BillToAddress").Value oSegment.DataElementValue(6) = oRsPOMaster("BillToCity").Value oSegment.DataElementValue(7) = oRsPOMaster("BillToState").Value oSegment.DataElementValue(8) = oRsPOMaster("BillToZip").Value Set oSegment = oTransactionset.CreateDataSegment("NAD(2)\CTA\CTA") oSegment.DataElementValue(1) = "PD" Set oSegment = oTransactionset.CreateDataSegment("NAD(2)\CTA\COM") oSegment.DataElementValue(1, 1) = oRsPOMaster("BillToPhone").Value oSegment.DataElementValue(1, 2) = "TE" Set oSegment = oTransactionset.CreateDataSegment("NAD(3)\NAD") oSegment.DataElementValue(1) = "ST" oSegment.DataElementValue(2, 1) = oRsPOMaster("ShipToID").Value oSegment.DataElementValue(2, 3) = "92" oSegment.DataElementValue(4, 1) = oRsPOMaster("ShipToName").Value oSegment.DataElementValue(5, 1) = oRsPOMaster("ShipToAddress").Value oSegment.DataElementValue(6) = oRsPOMaster("ShipToCity").Value oSegment.DataElementValue(7) = oRsPOMaster("ShipToState").Value oSegment.DataElementValue(8) = oRsPOMaster("ShipToZip").Value Set oSegment = oTransactionset.CreateDataSegment("NAD(3)\CTA\CTA") oSegment.DataElementValue(1) = "DL" Set oSegment = oTransactionset.CreateDataSegment("NAD(3)\CTA\COM") oSegment.DataElementValue(1, 1) = oRsPOMaster("ShipToPhone").Value oSegment.DataElementValue(1, 2) = "TE" oRsPODetail.Open "Select * from PODetail where PoMasterKey = " & oRsPOMaster("PoMasterKey").Value, oConn, adOpenDynamic, adLockOptimistic nInstance = 0 Do While Not oRsPODetail.EOF nInstance = nInstance + 1 mInstance = Trim(Str(nInstance)) Set oSegment = oTransactionset.CreateDataSegment("LIN(" & mInstance & ")\LIN") oSegment.DataElementValue(1) = mInstance oSegment.DataElementValue(3, 1) = oRsPODetail.Fields("LineNo") oSegment.DataElementValue(3, 2) = "IN" Set oSegment = oTransactionset.CreateDataSegment("LIN(" & mInstance & ")\IMD") oSegment.DataElementValue(1) = "F" oSegment.DataElementValue(2) = "8" oSegment.DataElementValue(3, 3) = oRsPODetail.Fields("Description") Set oSegment = oTransactionset.CreateDataSegment("LIN(" & mInstance & ")\QTY") oSegment.DataElementValue(1, 1) = "21" oSegment.DataElementValue(1, 2) = oRsPODetail.Fields("Quantity") oSegment.DataElementValue(1, 3) = "EA" Set oSegment = oTransactionset.CreateDataSegment("LIN(" & mInstance & ")\MOA") oSegment.DataElementValue(1, 1) = "146" oSegment.DataElementValue(1, 2) = oRsPODetail.Fields("UnitPrice") mInstance = mInstance + 1 oRsPODetail.MoveNext Loop oRsPODetail.Close Set oSegment = oTransactionset.CreateDataSegment("UNS") oSegment.DataElementValue(1) = "S" 'Total lines amount Set oSegment = oTransactionset.CreateDataSegment("MOA") oSegment.DataElementValue(1, 1) = "79" oSegment.DataElementValue(1, 2) = oRsPOMaster("ItemsAmount").Value 'Other charges Set oSegment = oTransactionset.CreateDataSegment("MOA(2)") oSegment.DataElementValue(1, 1) = "104" oSegment.DataElementValue(1, 2) = oRsPOMaster("ShippingCharges").Value 'Tax amount Set oSegment = oTransactionset.CreateDataSegment("MOA(3)") oSegment.DataElementValue(1, 1) = "124" oSegment.DataElementValue(1, 2) = oRsPOMaster("Taxes").Value 'Total amount Set oSegment = oTransactionset.CreateDataSegment("MOA(4)") oSegment.DataElementValue(1, 1) = "128" oSegment.DataElementValue(1, 2) = oRsPOMaster("TotalAmount").Value oRsPOMaster.MoveNext Loop oRsPOMaster.Close oRsTransactionSet.MoveNext Loop oRsTransactionSet.Close oRsInterchange.MoveNext Loop oRsInterchange.Close oEdiDoc.Save sEdiFile MsgBox ("Done") End Sub I get following error though when I run it : Run-time error '13': Type mismatch and the following line gets highlighted: Set oEdiDoc = CreateObject("Fredi.ediDocument") Thanks in advance for the help!
From: Sylvain Lafontaine on 21 Nov 2009 18:29 Fredi is probably an ActiveX control (or COM/DCOM component, they are closely related) that must be installed on your computer if you want to instanciate it. A quick search with Google indicate that this is probably part of the EDI Framework but I'm not sure: http://www.edidev.com/menuevaluate.htm -- Sylvain Lafontaine, ing. MVP - Windows Live Platform Blog/web site: http://coding-paparazzi.sylvainlafontaine.com Independent consultant and remote programming for Access and SQL-Server (French) "Earl.AKA J.Alladien in access forum!!" <EarlAKAJAlladieninaccessforum(a)discussions.microsoft.com> wrote in message news:5806DA1B-CC49-4911-B107-B091B0EFCEBC(a)microsoft.com... > Hi guys, > > I found a sample Access program that takes data from its database to > generate an EDI EDIFACT ORDERS file (OrdersS93a.edi). > > Here is the code: > > Private Sub Command0_Click() > Dim oEdiDoc As Fredi.ediDocument > Dim oSchema As Fredi.ediSchema > Dim oInterchange As Fredi.ediInterchange > Dim oGroup As Fredi.ediGroup > Dim oTransactionset As Fredi.ediTransactionSet > Dim oSegment As Fredi.ediDataSegment > > Dim oConn As ADODB.Connection > Dim oRsInterchange As ADODB.Recordset > Dim oRsGroup As ADODB.Recordset > Dim oRsTransactionSet As ADODB.Recordset > Dim oRsPOMaster As ADODB.Recordset > Dim oRsPODetail As ADODB.Recordset > Dim sConn As String > > Dim sSefFile As String > Dim sEdiFile As String > Dim sPath As String > > Dim i As Integer > Dim nCount As Integer > Dim sEntity As String > Dim nIndex As Integer > Dim iItemCount As Integer > Dim nInstance As Integer > Dim mInstance As String > > sPath = CurrentProject.Path & "\" > > sConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & sPath & > "genOrders.mdb" > Set oConn = New ADODB.Connection > oConn.Open sConn > > 'The InterchangeIndex table contains information of Interchange. > Set oRsInterchange = New ADODB.Recordset > > 'The TransactionSetIndex table contains information of the Transaction > Sets in the > 'Interchanges > Set oRsTransactionSet = New ADODB.Recordset > > 'The POMaster table contains information of the Purchase Order in the > Transaction Set > Set oRsPOMaster = New ADODB.Recordset > > 'The PODetail table contains information of the detailed items of > Purchase Orders > Set oRsPODetail = New ADODB.Recordset > > sSefFile = sPath & "ORDERS_S93A.sef" > sEdiFile = sPath & "ordersS93a.edi" > > Set oEdiDoc = CreateObject("Fredi.ediDocument") > Set oSchema = oEdiDoc.LoadSchema(sSefFile, 0) > > oEdiDoc.SegmentTerminator = "'" > oEdiDoc.ElementTerminator = "+" > oEdiDoc.CompositeTerminator = ":" > oEdiDoc.ReleaseIndicator = "?" > > oRsInterchange.Open "Select * from InterchangeIndex", oConn, > adOpenDynamic, adLockOptimistic > Do While Not oRsInterchange.EOF > Set oInterchange = oEdiDoc.CreateInterchange("UN", "S93A") > Set oSegment = oInterchange.GetDataSegmentHeader > oSegment.DataElementValue(1, 1) = "UNOB" > oSegment.DataElementValue(1, 2) = "1" > oSegment.DataElementValue(2, 1) = oRsInterchange("SenderID").Value > oSegment.DataElementValue(2, 2) = > oRsInterchange("SenderID_Qlfr").Value > oSegment.DataElementValue(2, 3) = "MFGB" > oSegment.DataElementValue(3, 1) = > oRsInterchange("ReceiverID").Value > oSegment.DataElementValue(3, 2) = > oRsInterchange("ReceiverID_Qlfr").Value > oSegment.DataElementValue(3, 3) = "ROUTE ADDR" > oSegment.DataElementValue(4, 1) = "970101" > oSegment.DataElementValue(4, 2) = "1230" > oSegment.DataElementValue(5) = > oRsInterchange("InterchangeControlNo").Value > oSegment.DataElementValue(7) = oRsInterchange("Application").Value > oSegment.DataElementValue(11) = "1" > > oRsTransactionSet.Open "Select * from TransactionSetIndex where > InterchangeKey = " & oRsInterchange("InterchangeKey").Value, oConn, > adOpenDynamic, adLockOptimistic > Do While Not oRsTransactionSet.EOF > Set oTransactionset = > oInterchange.CreateTransactionSet("ORDERS") > Set oSegment = oTransactionset.GetDataSegmentHeader > oSegment.DataElementValue(1) = > oRsTransactionSet("MessageRefNo").Value > oSegment.DataElementValue(2, 1) = > oRsTransactionSet("MessageType").Value > oSegment.DataElementValue(2, 2) = > oRsTransactionSet("MessageVersion").Value > oSegment.DataElementValue(2, 3) = > oRsTransactionSet("MessageRelease").Value > oSegment.DataElementValue(2, 4) = "UN" > oSegment.Last > > oRsPOMaster.Open "Select * from POMaster where TsKey = " & > oRsTransactionSet("TsKey").Value, oConn, adOpenDynamic, adLockOptimistic > Do While Not oRsPOMaster.EOF > Set oSegment = oTransactionset.CreateDataSegment("BGM") > oSegment.DataElementValue(1, 1) = "221" > oSegment.DataElementValue(2) = > oRsPOMaster("PONumber").Value > oSegment.DataElementValue(3) = "9" > > Set oSegment = oTransactionset.CreateDataSegment("DTM") > oSegment.DataElementValue(1, 1) = "4" > oSegment.DataElementValue(1, 2) = > oRsPOMaster("PODate").Value > oSegment.DataElementValue(1, 3) = "102" > > Set oSegment = oTransactionset.CreateDataSegment("DTM(2)") > oSegment.DataElementValue(1, 1) = "3" > oSegment.DataElementValue(1, 2) = > oRsPOMaster("InvDate").Value > oSegment.DataElementValue(1, 3) = "102" > > Set oSegment = > oTransactionset.CreateDataSegment("NAD(1)\NAD") > oSegment.DataElementValue(1) = "BY" > oSegment.DataElementValue(2, 1) = > oRsPOMaster("BuyerId").Value > oSegment.DataElementValue(2, 3) = "92" > oSegment.DataElementValue(4, 1) = > oRsPOMaster("BuyerName").Value > > Set oSegment = > oTransactionset.CreateDataSegment("NAD(2)\NAD") > oSegment.DataElementValue(1) = "BT" > oSegment.DataElementValue(2, 1) = > oRsPOMaster("BillToID").Value > oSegment.DataElementValue(2, 3) = "92" > oSegment.DataElementValue(4, 1) = > oRsPOMaster("BillToName").Value > oSegment.DataElementValue(5, 1) = > oRsPOMaster("BillToAddress").Value > oSegment.DataElementValue(6) = > oRsPOMaster("BillToCity").Value > oSegment.DataElementValue(7) = > oRsPOMaster("BillToState").Value > oSegment.DataElementValue(8) = > oRsPOMaster("BillToZip").Value > > Set oSegment = > oTransactionset.CreateDataSegment("NAD(2)\CTA\CTA") > oSegment.DataElementValue(1) = "PD" > > Set oSegment = > oTransactionset.CreateDataSegment("NAD(2)\CTA\COM") > oSegment.DataElementValue(1, 1) = > oRsPOMaster("BillToPhone").Value > oSegment.DataElementValue(1, 2) = "TE" > > Set oSegment = > oTransactionset.CreateDataSegment("NAD(3)\NAD") > oSegment.DataElementValue(1) = "ST" > oSegment.DataElementValue(2, 1) = > oRsPOMaster("ShipToID").Value > oSegment.DataElementValue(2, 3) = "92" > oSegment.DataElementValue(4, 1) = > oRsPOMaster("ShipToName").Value > oSegment.DataElementValue(5, 1) = > oRsPOMaster("ShipToAddress").Value > oSegment.DataElementValue(6) = > oRsPOMaster("ShipToCity").Value > oSegment.DataElementValue(7) = > oRsPOMaster("ShipToState").Value > oSegment.DataElementValue(8) = > oRsPOMaster("ShipToZip").Value > > Set oSegment = > oTransactionset.CreateDataSegment("NAD(3)\CTA\CTA") > oSegment.DataElementValue(1) = "DL" > > Set oSegment = > oTransactionset.CreateDataSegment("NAD(3)\CTA\COM") > oSegment.DataElementValue(1, 1) = > oRsPOMaster("ShipToPhone").Value > oSegment.DataElementValue(1, 2) = "TE" > > oRsPODetail.Open "Select * from PODetail where PoMasterKey > = > " & oRsPOMaster("PoMasterKey").Value, oConn, adOpenDynamic, > adLockOptimistic > nInstance = 0 > Do While Not oRsPODetail.EOF > nInstance = nInstance + 1 > mInstance = Trim(Str(nInstance)) > Set oSegment = oTransactionset.CreateDataSegment("LIN(" > & mInstance & ")\LIN") > oSegment.DataElementValue(1) = mInstance > oSegment.DataElementValue(3, 1) = > oRsPODetail.Fields("LineNo") > oSegment.DataElementValue(3, 2) = "IN" > > Set oSegment = oTransactionset.CreateDataSegment("LIN(" > & mInstance & ")\IMD") > oSegment.DataElementValue(1) = "F" > oSegment.DataElementValue(2) = "8" > oSegment.DataElementValue(3, 3) = > oRsPODetail.Fields("Description") > > Set oSegment = oTransactionset.CreateDataSegment("LIN(" > & mInstance & ")\QTY") > oSegment.DataElementValue(1, 1) = "21" > oSegment.DataElementValue(1, 2) = > oRsPODetail.Fields("Quantity") > oSegment.DataElementValue(1, 3) = "EA" > > Set oSegment = oTransactionset.CreateDataSegment("LIN(" > & mInstance & ")\MOA") > oSegment.DataElementValue(1, 1) = "146" > oSegment.DataElementValue(1, 2) = > oRsPODetail.Fields("UnitPrice") > > mInstance = mInstance + 1 > oRsPODetail.MoveNext > Loop > oRsPODetail.Close > > Set oSegment = oTransactionset.CreateDataSegment("UNS") > oSegment.DataElementValue(1) = "S" > > 'Total lines amount > Set oSegment = oTransactionset.CreateDataSegment("MOA") > oSegment.DataElementValue(1, 1) = "79" > oSegment.DataElementValue(1, 2) = > oRsPOMaster("ItemsAmount").Value > > 'Other charges > Set oSegment = oTransactionset.CreateDataSegment("MOA(2)") > oSegment.DataElementValue(1, 1) = "104" > oSegment.DataElementValue(1, 2) = > oRsPOMaster("ShippingCharges").Value > > 'Tax amount > Set oSegment = oTransactionset.CreateDataSegment("MOA(3)") > oSegment.DataElementValue(1, 1) = "124" > oSegment.DataElementValue(1, 2) = > oRsPOMaster("Taxes").Value > > 'Total amount > Set oSegment = oTransactionset.CreateDataSegment("MOA(4)") > oSegment.DataElementValue(1, 1) = "128" > oSegment.DataElementValue(1, 2) = > oRsPOMaster("TotalAmount").Value > > oRsPOMaster.MoveNext > Loop > oRsPOMaster.Close > > oRsTransactionSet.MoveNext > Loop > oRsTransactionSet.Close > > oRsInterchange.MoveNext > Loop > oRsInterchange.Close > > oEdiDoc.Save sEdiFile > > MsgBox ("Done") > > End Sub > > > I get following error though when I run it : > > Run-time error '13': > Type mismatch > > and the following line gets highlighted: > > Set oEdiDoc = CreateObject("Fredi.ediDocument") > > Thanks in advance for the help! > > > > > >
From: Earl.AKA J.Alladien in access forum!! on 22 Nov 2009 06:41 Hi Sylvain, Just so you know I did install the EDI framework, and the DB I downloaded comes in 2 files one that generates from ACCESS to an EDI, and one that translates an EDI into ACCESS, the last one works fine,...al references are also set,so there is no problem there! "Sylvain Lafontaine" wrote: > Fredi is probably an ActiveX control (or COM/DCOM component, they are > closely related) that must be installed on your computer if you want to > instanciate it. A quick search with Google indicate that this is probably > part of the EDI Framework but I'm not sure: > > http://www.edidev.com/menuevaluate.htm > > -- > Sylvain Lafontaine, ing. > MVP - Windows Live Platform > Blog/web site: http://coding-paparazzi.sylvainlafontaine.com > Independent consultant and remote programming for Access and SQL-Server > (French) > > > "Earl.AKA J.Alladien in access forum!!" > <EarlAKAJAlladieninaccessforum(a)discussions.microsoft.com> wrote in message > news:5806DA1B-CC49-4911-B107-B091B0EFCEBC(a)microsoft.com... > > Hi guys, > > > > I found a sample Access program that takes data from its database to > > generate an EDI EDIFACT ORDERS file (OrdersS93a.edi). > > > > Here is the code: > > > > Private Sub Command0_Click() > > Dim oEdiDoc As Fredi.ediDocument > > Dim oSchema As Fredi.ediSchema > > Dim oInterchange As Fredi.ediInterchange > > Dim oGroup As Fredi.ediGroup > > Dim oTransactionset As Fredi.ediTransactionSet > > Dim oSegment As Fredi.ediDataSegment > > > > Dim oConn As ADODB.Connection > > Dim oRsInterchange As ADODB.Recordset > > Dim oRsGroup As ADODB.Recordset > > Dim oRsTransactionSet As ADODB.Recordset > > Dim oRsPOMaster As ADODB.Recordset > > Dim oRsPODetail As ADODB.Recordset > > Dim sConn As String > > > > Dim sSefFile As String > > Dim sEdiFile As String > > Dim sPath As String > > > > Dim i As Integer > > Dim nCount As Integer > > Dim sEntity As String > > Dim nIndex As Integer > > Dim iItemCount As Integer > > Dim nInstance As Integer > > Dim mInstance As String > > > > sPath = CurrentProject.Path & "\" > > > > sConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & sPath & > > "genOrders.mdb" > > Set oConn = New ADODB.Connection > > oConn.Open sConn > > > > 'The InterchangeIndex table contains information of Interchange. > > Set oRsInterchange = New ADODB.Recordset > > > > 'The TransactionSetIndex table contains information of the Transaction > > Sets in the > > 'Interchanges > > Set oRsTransactionSet = New ADODB.Recordset > > > > 'The POMaster table contains information of the Purchase Order in the > > Transaction Set > > Set oRsPOMaster = New ADODB.Recordset > > > > 'The PODetail table contains information of the detailed items of > > Purchase Orders > > Set oRsPODetail = New ADODB.Recordset > > > > sSefFile = sPath & "ORDERS_S93A.sef" > > sEdiFile = sPath & "ordersS93a.edi" > > > > Set oEdiDoc = CreateObject("Fredi.ediDocument") > > Set oSchema = oEdiDoc.LoadSchema(sSefFile, 0) > > > > oEdiDoc.SegmentTerminator = "'" > > oEdiDoc.ElementTerminator = "+" > > oEdiDoc.CompositeTerminator = ":" > > oEdiDoc.ReleaseIndicator = "?" > > > > oRsInterchange.Open "Select * from InterchangeIndex", oConn, > > adOpenDynamic, adLockOptimistic > > Do While Not oRsInterchange.EOF > > Set oInterchange = oEdiDoc.CreateInterchange("UN", "S93A") > > Set oSegment = oInterchange.GetDataSegmentHeader > > oSegment.DataElementValue(1, 1) = "UNOB" > > oSegment.DataElementValue(1, 2) = "1" > > oSegment.DataElementValue(2, 1) = oRsInterchange("SenderID").Value > > oSegment.DataElementValue(2, 2) = > > oRsInterchange("SenderID_Qlfr").Value > > oSegment.DataElementValue(2, 3) = "MFGB" > > oSegment.DataElementValue(3, 1) = > > oRsInterchange("ReceiverID").Value > > oSegment.DataElementValue(3, 2) = > > oRsInterchange("ReceiverID_Qlfr").Value > > oSegment.DataElementValue(3, 3) = "ROUTE ADDR" > > oSegment.DataElementValue(4, 1) = "970101" > > oSegment.DataElementValue(4, 2) = "1230" > > oSegment.DataElementValue(5) = > > oRsInterchange("InterchangeControlNo").Value > > oSegment.DataElementValue(7) = oRsInterchange("Application").Value > > oSegment.DataElementValue(11) = "1" > > > > oRsTransactionSet.Open "Select * from TransactionSetIndex where > > InterchangeKey = " & oRsInterchange("InterchangeKey").Value, oConn, > > adOpenDynamic, adLockOptimistic > > Do While Not oRsTransactionSet.EOF > > Set oTransactionset = > > oInterchange.CreateTransactionSet("ORDERS") > > Set oSegment = oTransactionset.GetDataSegmentHeader > > oSegment.DataElementValue(1) = > > oRsTransactionSet("MessageRefNo").Value > > oSegment.DataElementValue(2, 1) = > > oRsTransactionSet("MessageType").Value > > oSegment.DataElementValue(2, 2) = > > oRsTransactionSet("MessageVersion").Value > > oSegment.DataElementValue(2, 3) = > > oRsTransactionSet("MessageRelease").Value > > oSegment.DataElementValue(2, 4) = "UN" > > oSegment.Last > > > > oRsPOMaster.Open "Select * from POMaster where TsKey = " & > > oRsTransactionSet("TsKey").Value, oConn, adOpenDynamic, adLockOptimistic > > Do While Not oRsPOMaster.EOF > > Set oSegment = oTransactionset.CreateDataSegment("BGM") > > oSegment.DataElementValue(1, 1) = "221" > > oSegment.DataElementValue(2) = > > oRsPOMaster("PONumber").Value > > oSegment.DataElementValue(3) = "9" > > > > Set oSegment = oTransactionset.CreateDataSegment("DTM") > > oSegment.DataElementValue(1, 1) = "4" > > oSegment.DataElementValue(1, 2) = > > oRsPOMaster("PODate").Value > > oSegment.DataElementValue(1, 3) = "102" > > > > Set oSegment = oTransactionset.CreateDataSegment("DTM(2)") > > oSegment.DataElementValue(1, 1) = "3" > > oSegment.DataElementValue(1, 2) = > > oRsPOMaster("InvDate").Value > > oSegment.DataElementValue(1, 3) = "102" > > > > Set oSegment = > > oTransactionset.CreateDataSegment("NAD(1)\NAD") > > oSegment.DataElementValue(1) = "BY" > > oSegment.DataElementValue(2, 1) = > > oRsPOMaster("BuyerId").Value > > oSegment.DataElementValue(2, 3) = "92" > > oSegment.DataElementValue(4, 1) = > > oRsPOMaster("BuyerName").Value > > > > Set oSegment = > > oTransactionset.CreateDataSegment("NAD(2)\NAD") > > oSegment.DataElementValue(1) = "BT" > > oSegment.DataElementValue(2, 1) = > > oRsPOMaster("BillToID").Value > > oSegment.DataElementValue(2, 3) = "92" > > oSegment.DataElementValue(4, 1) = > > oRsPOMaster("BillToName").Value > > oSegment.DataElementValue(5, 1) = > > oRsPOMaster("BillToAddress").Value > > oSegment.DataElementValue(6) = > > oRsPOMaster("BillToCity").Value > > oSegment.DataElementValue(7) = > > oRsPOMaster("BillToState").Value > > oSegment.DataElementValue(8) = > > oRsPOMaster("BillToZip").Value > > > > Set oSegment = > > oTransactionset.CreateDataSegment("NAD(2)\CTA\CTA") > > oSegment.DataElementValue(1) = "PD" > > > > Set oSegment = > > oTransactionset.CreateDataSegment("NAD(2)\CTA\COM") > > oSegment.DataElementValue(1, 1) = > > oRsPOMaster("BillToPhone").Value > > oSegment.DataElementValue(1, 2) = "TE" > > > > Set oSegment = > > oTransactionset.CreateDataSegment("NAD(3)\NAD") > > oSegment.DataElementValue(1) = "ST" > > oSegment.DataElementValue(2, 1) = > > oRsPOMaster("ShipToID").Value > > oSegment.DataElementValue(2, 3) = "92" > > oSegment.DataElementValue(4, 1) = > > oRsPOMaster("ShipToName").Value > > oSegment.DataElementValue(5, 1) = > > oRsPOMaster("ShipToAddress").Value > > oSegment.DataElementValue(6) = > > oRsPOMaster("ShipToCity").Value > > oSegment.DataElementValue(7) = > > oRsPOMaster("ShipToState").Value > > oSegment.DataElementValue(8) = > > oRsPOMaster("ShipToZip").Value > > > > Set oSegment = > > oTransactionset.CreateDataSegment("NAD(3)\CTA\CTA") > > oSegment.DataElementValue(1) = "DL" > > > > Set oSegment = > > oTransactionset.CreateDataSegment("NAD(3)\CTA\COM") > > oSegment.DataElementValue(1, 1) = > > oRsPOMaster("ShipToPhone").Value > > oSegment.DataElementValue(1, 2) = "TE" > > > > oRsPODetail.Open "Select * from PODetail where PoMasterKey > > = > > " & oRsPOMaster("PoMasterKey").Value, oConn, adOpenDynamic, > > adLockOptimistic > > nInstance = 0 > > Do While Not oRsPODetail.EOF > > nInstance = nInstance + 1 > > mInstance = Trim(Str(nInstance)) > > Set oSegment = oTransactionset.CreateDataSegment("LIN(" > > & mInstance & ")\LIN") > > oSegment.DataElementValue(1) = mInstance > > oSegment.DataElementValue(3, 1) = > > oRsPODetail.Fields("LineNo") > > oSegment.DataElementValue(3, 2) = "IN" > > > > Set oSegment = oTransactionset.CreateDataSegment("LIN(" > > & mInstance & ")\IMD") > > oSegment.DataElementValue(1) = "F" > > oSegment.DataElementValue(2) = "8" > > oSegment.DataElementValue(3, 3) = > > oRsPODetail.Fields("Description") > > > > Set oSegment = oTransactionset.CreateDataSegment("LIN(" > > & mInstance & ")\QTY") > > oSegment.DataElementValue(1, 1) = "21" > > oSegment.DataElementValue(1, 2) = > > oRsPODetail.Fields("Quantity") > > oSegment.DataElementValue(1, 3) = "EA" > > > > Set oSegment = oTransactionset.CreateDataSegment("LIN(" > > & mInstance & ")\MOA") > > oSegment.DataElementValue(1, 1) = "146" > > oSegment.DataElementValue(1, 2) = > > oRsPODetail.Fields("UnitPrice") > > > > mInstance = mInstance + 1 > > oRsPODetail.MoveNext > > Loop > > oRsPODetail.Close > > > > Set oSegment = oTransactionset.CreateDataSegment("UNS") > > oSegment.DataElementValue(1) = "S" > > > > 'Total lines amount > > Set oSegment = oTransactionset.CreateDataSegment("MOA") > > oSegment.DataElementValue(1, 1) = "79" > > oSegment.DataElementValue(1, 2) = > > oRsPOMaster("ItemsAmount").Value > > > > 'Other charges > > Set oSegment = oTransactionset.CreateDataSegment("MOA(2)") > > oSegment.DataElementValue(1, 1) = "104" > > oSegment.DataElementValue(1, 2) = > > oRsPOMaster("ShippingCharges").Value > > > > 'Tax amount > > Set oSegment = oTransactionset.CreateDataSegment("MOA(3)") > > oSegment.DataElementValue(1, 1) = "124" > > oSegment.DataElementValue(1, 2) = > > oRsPOMaster("Taxes").Value > > > > 'Total amount > > Set oSegment = oTransactionset.CreateDataSegment("MOA(4)") > > oSegment.DataElementValue(1, 1) = "128" > > oSegment.DataElementValue(1, 2) = > > oRsPOMaster("TotalAmount").Value > > > > oRsPOMaster.MoveNext > > Loop > > oRsPOMaster.Close > > > > oRsTransactionSet.MoveNext > > Loop > > oRsTransactionSet.Close > > > > oRsInterchange.MoveNext > > Loop > > oRsInterchange.Close > > > > oEdiDoc.Save sEdiFile > > > > MsgBox ("Done") > > > > End Sub > > > > > > I get following error though when I run it : > >
From: Sylvain Lafontaine on 22 Nov 2009 12:49 "Earl.AKA J.Alladien in access forum!!" <EarlAKAJAlladieninaccessforum(a)discussions.microsoft.com> wrote in message news:3B935416-3132-4450-9940-4BDE9F5B4DD3(a)microsoft.com... > Hi Sylvain, > > Just so you know I did install the EDI framework, and the DB I downloaded > comes in 2 files one that generates from ACCESS to an EDI, and one that > translates an EDI into ACCESS, the last one works fine,...al references > are > also set,so there is no problem there! First, you should have given this information in the first place. You should also explain if in the second file, the � Set oEdiDoc = CreateObject("Fredi.ediDocument") � is working properly or if they isn't any of them. If you are in the situation where the instruction � Set oEdiDoc = CreateObject("Fredi.ediDocument") � works in one file but not in another, then maybe the second file is corrupted or is partially wrongly compiled or the references are not set correctly. The first thing to do would be to fully decompile the faulty file and then launch a full compilation from the Debug menu: 1- Open the References window from the VBA editor. 2- Add a dummy reference. 3- Close the References windows (Important!) 4- Reopen the References windows and remove the dummy reference (optional). Now that the references has changed, Access will flag all modules as beeing of need to be recompiled. Launch the Recompile All Modules from the Debug menu of the VBA editor. Alternatively, create a windows shortcut with the following startup options: /decompile /compact /repair Another thing to try would be to use late binding instead of early binding: replace the instruction � Dim oEdiDoc As Fredi.ediDocument � with � Dim oEdiDoc As Object � or maybe even simply as a variant: � Dim oEdiDoc � -- Sylvain Lafontaine, ing. MVP - Windows Live Platform Blog/web site: http://coding-paparazzi.sylvainlafontaine.com Independent consultant and remote programming for Access and SQL-Server (French)
From: Earl.AKA J.Alladien in access forum!! on 23 Nov 2009 13:02
Hi Sylvain, First of all I'd like to thank you for helping me ofcourse, And after performing the "dummy" procedure it now works, so thanks a lot !! Thanks again for your wisdom and advise! "Sylvain Lafontaine" wrote: > "Earl.AKA J.Alladien in access forum!!" > <EarlAKAJAlladieninaccessforum(a)discussions.microsoft.com> wrote in message > news:3B935416-3132-4450-9940-4BDE9F5B4DD3(a)microsoft.com... > > Hi Sylvain, > > > > Just so you know I did install the EDI framework, and the DB I downloaded > > comes in 2 files one that generates from ACCESS to an EDI, and one that > > translates an EDI into ACCESS, the last one works fine,...al references > > are > > also set,so there is no problem there! > > First, you should have given this information in the first place. > > You should also explain if in the second file, the « Set oEdiDoc = > CreateObject("Fredi.ediDocument") » is working properly or if they isn't any > of them. > > If you are in the situation where the instruction « Set oEdiDoc = > CreateObject("Fredi.ediDocument") » works in one file but not in another, > then maybe the second file is corrupted or is partially wrongly compiled or > the references are not set correctly. > > The first thing to do would be to fully decompile the faulty file and then > launch a full compilation from the Debug menu: > > 1- Open the References window from the VBA editor. > > 2- Add a dummy reference. > > 3- Close the References windows (Important!) > > 4- Reopen the References windows and remove the dummy reference (optional). > > Now that the references has changed, Access will flag all modules as beeing > of need to be recompiled. Launch the Recompile All Modules from the Debug > menu of the VBA editor. > > Alternatively, create a windows shortcut with the following startup options: > > /decompile /compact /repair > > Another thing to try would be to use late binding instead of early binding: > replace the instruction « Dim oEdiDoc As Fredi.ediDocument » with « Dim > oEdiDoc As Object » or maybe even simply as a variant: « Dim oEdiDoc » > > -- > Sylvain Lafontaine, ing. > MVP - Windows Live Platform > Blog/web site: http://coding-paparazzi.sylvainlafontaine.com > Independent consultant and remote programming for Access and SQL-Server > (French) > > > > . > |