Need some advanced help on this one!

[Earl.AKA J.Alladien in access forum!!]

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!

 

[Sylvain Lafontaine]

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!!"

 

[Earl.AKA J.Alladien in access forum!!]

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]

"Earl.AKA J.Alladien in access forum!!"

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)

 

[Earl.AKA J.Alladien in access forum!!]

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]

You should perform this step everytime there is a big change in Windows (new
service pack, new version of Office, etc.) or when you are copying the MDFB
file to another machine; especially if the target machine doesn't have the
same version of Windows and Office.

--
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!!"