Private Sub Command0_Click() 'PROCEDURE: TO TRANSLATE AN EDI X12 X096 WITH MULTIPLE CLAIMS FILE. 'THIS IS ONLY TO DEMONSTRATE HOW TO TRANSLATE AN EDI FILE INTO A DATABASE. 'NOTE SOME ELEMENTS HAVE BEEN IGNORED, AND ARE NOT MAPPED INTO THE DATABASE. 'FREDI objects Dim oEdiDoc As Fredi.ediDocument Dim oSchema As Fredi.ediSchema Dim oSchemas As Fredi.ediSchemas Dim oSegment As Fredi.ediDataSegment 'ADO objects Dim oConn As ADODB.Connection Dim oRsInterchange As ADODB.Recordset Dim oRsFuncGroup As ADODB.Recordset Dim oRsX096Header As ADODB.Recordset Dim oRsX096ProviderInfo As ADODB.Recordset Dim oRsX096SubscriberInfo As ADODB.Recordset Dim oRsX096Claims As ADODB.Recordset Dim oRsX096OtherSubscriberInfo As ADODB.Recordset Dim oRsX096ServiceInfo As ADODB.Recordset Dim sSegmentID As String Dim sLoopSection As String Dim nArea As Integer Dim sValue As String Dim sPath As String Dim sEdiFile As String Dim sSefFile As String Dim sConn As String Dim sEntityCode As String Dim sLoopHLQlfr As String sPath = CurrentProject.Path & "\" sEdiFile = "837_X096.X12" sSefFile = "837_X096.SEF" 'CREATES EDI DOCUMENT OBJECT Set oEdiDoc = New Fredi.ediDocument 'THIS MAKES CERTAIN THAT FREDI ONLY USES THE SEF FILE PROVIDED, AND THAT IT DOES 'NOT USE ITS BUILT-IN STANDARD REFERENCE TABLE TO TRANSLATE THE EDI FILE. Set oSchemas = oEdiDoc.GetSchemas oSchemas.EnableStandardReference = False 'THIS OPTIONS STOPS FREDI FROM KEEPING ALL THE SEGMENTS IN MEMORY oEdiDoc.CursorType = Cursor_ForwardOnly 'LOADS THE SEF FILE Set oSchema = oEdiDoc.ImportSchema(sPath & sSefFile, 0) 'LOADS THE EDI FILE oEdiDoc.LoadEdi sPath & sEdiFile 'OPENS DATABASE CONNECTION sConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & sPath & "TranX096.mdb" Set oConn = New ADODB.Connection oConn.Open sConn 'OPENS INTERCHANGE TABLE Set oRsInterchange = New ADODB.Recordset oRsInterchange.Open "Interchange", oConn, adOpenDynamic, adLockOptimistic 'OPENS FUNCTIONAL GROUP TABLE Set oRsFuncGroup = New ADODB.Recordset oRsFuncGroup.Open "FuncGroup", oConn, adOpenDynamic, adLockOptimistic 'OPENS X096Header TABLE Set oRsX096Header = New ADODB.Recordset oRsX096Header.Open "X096Header", oConn, adOpenDynamic, adLockOptimistic 'OPENS X096ProviderInfo TABLE Set oRsX096ProviderInfo = New ADODB.Recordset oRsX096ProviderInfo.Open "X096ProviderInfo", oConn, adOpenDynamic, adLockOptimistic 'OPENS X096SubscriberInfo TABLE Set oRsX096SubscriberInfo = New ADODB.Recordset oRsX096SubscriberInfo.Open "X096SubscriberInfo", oConn, adOpenDynamic, adLockOptimistic 'OPENS X096Claims TABLE Set oRsX096Claims = New ADODB.Recordset oRsX096Claims.Open "X096Claims", oConn, adOpenDynamic, adLockOptimistic 'OPENS X096OtherSubscriberInfo TABLE Set oRsX096OtherSubscriberInfo = New ADODB.Recordset oRsX096OtherSubscriberInfo.Open "X096OtherSubscriberInfo", oConn, adOpenDynamic, adLockOptimistic 'OPENS X096DependentInfo TABLE Set oRsX096DependentInfo = New ADODB.Recordset oRsX096DependentInfo.Open "X096DependentInfo", oConn, adOpenDynamic, adLockOptimistic 'OPENS X096ServiceInfo TABLE Set oRsX096ServiceInfo = New ADODB.Recordset oRsX096ServiceInfo.Open "X096ServiceInfo", oConn, adOpenDynamic, adLockOptimistic 'GETS THE FIRST DATA SEGMENT Set oSegment = oEdiDoc.FirstDataSegment 'LOOP THAT WILL TRAVERSE THRU EDI FILE FROM TOP TO BOTTOM Do While Not oSegment Is Nothing 'DATA SEGMENTS ARE REFERENCED BY THEIR ID, THE LOOP SECTION AND AREA '(OR TABLE) NUMBER THAT THEY ARE IN. sSegmentID = oSegment.ID sLoopSection = oSegment.LoopSection nArea = oSegment.Area If nArea = 0 Then If sLoopSection = "" Then If sSegmentID = "ISA" Then oRsInterchange.AddNew ' sValue = oSegment.DataElementValue(1) 'Authorization Information Qualifier ' sValue = oSegment.DataElementValue(2) 'Authorization Information ' sValue = oSegment.DataElementValue(3) 'Security Information Qualifier ' sValue = oSegment.DataElementValue(4) 'Security Information oRsInterchange("SenderQlfr").Value = oSegment.DataElementValue(5) 'Interchange ID Qualifier oRsInterchange("SenderID").Value = oSegment.DataElementValue(6) 'Interchange Sender ID oRsInterchange("ReceiverQlfr").Value = oSegment.DataElementValue(7) 'Interchange ID Qualifier oRsInterchange("ReceiverID").Value = oSegment.DataElementValue(8) 'Interchange Receiver ID oRsInterchange("InterDate").Value = oSegment.DataElementValue(9) 'Interchange Date ' sValue = oSegment.DataElementValue(10) 'Interchange Time ' sValue = oSegment.DataElementValue(11) 'Interchange Control Standards Identifier ' sValue = oSegment.DataElementValue(12) 'Interchange Control Version Number oRsInterchange("ControlNo").Value = oSegment.DataElementValue(13) 'Interchange Control Number ' sValue = oSegment.DataElementValue(14) 'Acknowledgment Requested ' sValue = oSegment.DataElementValue(15) 'Usage Indicator ' sValue = oSegment.DataElementValue(16) 'Component Element Separator ElseIf sSegmentID = "IEA" Then oRsInterchange.Update ElseIf sSegmentID = "GS" Then oRsFuncGroup.AddNew oRsFuncGroup("InterchangeKey").Value = oRsInterchange("InterchangeKey").Value oRsFuncGroup("FuncID").Value = oSegment.DataElementValue(1) 'Functional Identifier Code ' sValue = oSegment.DataElementValue(2) 'Application Sender's Code ' sValue = oSegment.DataElementValue(3) 'Application Receiver's Code ' sValue = oSegment.DataElementValue(4) 'Date ' sValue = oSegment.DataElementValue(5) 'Time oRsFuncGroup("ControlNo").Value = oSegment.DataElementValue(6) 'Group Control Number ' sValue = oSegment.DataElementValue(7) 'Responsible Agency Code ' sValue = oSegment.DataElementValue(8) 'Version / Release / Industry Identifier Code ElseIf sSegmentID = "GE" Then oRsFuncGroup.Update End If 'sSegmentID End If 'sLoopSection ElseIf nArea = 1 Then If sLoopSection = "" Then If sSegmentID = "ST" Then oRsX096Header.AddNew oRsX096Header("GroupKey").Value = oRsFuncGroup("GroupKey").Value oRsX096Header("MessageNo").Value = oSegment.DataElementValue(1) 'Transaction Set Identifier Code oRsX096Header("ControlNo").Value = oSegment.DataElementValue(2) 'Transaction Set Control Number ElseIf sSegmentID = "BHT" Then ' sValue = oSegment.DataElementValue(1) 'Hierarchical Structure Code ' sValue = oSegment.DataElementValue(2) 'Transaction Set Purpose Code oRsX096Header("ReferenceID").Value = oSegment.DataElementValue(3) 'Reference Identification oRsX096Header("ReferenceDate").Value = oSegment.DataElementValue(4) 'Date ' sValue = oSegment.DataElementValue(5) 'Time ElseIf sSegmentID = "REF" Then ' sValue = oSegment.DataElementValue(1) 'Reference Identification Qualifier ' sValue = oSegment.DataElementValue(2) 'Reference Identification End If 'Segment ID ElseIf sLoopSection = "NM1" Then If sSegmentID = "NM1" Then sEntityCode = oSegment.DataElementValue(1) 'In most cases the loop qualifier is the first element of the first segment in the loop, but not necessarily End If If sEntityCode = "40" Then 'Receiver If sSegmentID = "NM1" Then ' sValue = oSegment.DataElementValue(1) 'Entity Identifier Code ' sValue = oSegment.DataElementValue(2) 'Entity Type Qualifier oRsX096Header("ReceiverCompanyName").Value = oSegment.DataElementValue(3) 'Name Last or Organization Name ' sValue = oSegment.DataElementValue(4) 'Name First ' sValue = oSegment.DataElementValue(5) 'Name Middle ' sValue = oSegment.DataElementValue(6) 'Name Prefix ' sValue = oSegment.DataElementValue(7) 'Name Suffix ' sValue = oSegment.DataElementValue(8) 'Identification Code Qualifier oRsX096Header("ReceiverCode").Value = oSegment.DataElementValue(9) 'Identification Code End If 'sSegmentID ElseIf sEntityCode = "41" Then 'Submitter If sSegmentID = "NM1" Then ' sValue = oSegment.DataElementValue(1) 'Entity Identifier Code ' sValue = oSegment.DataElementValue(2) 'Entity Type Qualifier oRsX096Header("SubmitterCompanyName").Value = oSegment.DataElementValue(3) 'Name Last or Organization Name ' sValue = oSegment.DataElementValue(4) 'Name First ' sValue = oSegment.DataElementValue(5) 'Name Middle ' sValue = oSegment.DataElementValue(6) 'Name Prefix ' sValue = oSegment.DataElementValue(7) 'Name Suffix ' sValue = oSegment.DataElementValue(8) 'Identification Code Qualifier oRsX096Header("SubmitterCode").Value = oSegment.DataElementValue(9) 'Identification Code End If 'sSegmentID End If End If 'sLoopSection ElseIf nArea = 2 Then If sLoopSection = "" Then If sSegmentID = "SE" Then oRsX096Header.Update End If ElseIf sLoopSection = "HL" Then 'Get HL qualifier If sSegmentID = "HL" Then sLoopHLQlfr = oSegment.DataElementValue(3) 'In most cases the loop qualifier is the first element of the first segment in the loop, but not necessarily End If End If If sLoopHLQlfr = "20" Then 'Information If sLoopSection = "HL" Then If sSegmentID = "HL" Then If Not oRsX096ProviderInfo.BOF Then oRsX096ProviderInfo.Update End If oRsX096ProviderInfo.AddNew oRsX096ProviderInfo("TSetKey").Value = oRsX096Header("TSetKey").Value ' sValue = oSegment.DataElementValue(1) 'Hierarchical ID Number ' sValue = oSegment.DataElementValue(2) 'Hierarchical Parent ID Number ' sValue = oSegment.DataElementValue(3) 'Hierarchical Level Code ' sValue = oSegment.DataElementValue(4) 'Hierarchical Child Code ElseIf sSegmentID = "PRV" Then ' sValue = oSegment.DataElementValue(1) 'Provider Code ' sValue = oSegment.DataElementValue(2) 'Reference Identification Qualifier oRsX096ProviderInfo("BillingID").Value = oSegment.DataElementValue(3) 'Reference Identification End If 'Segment ID ElseIf sLoopSection = "HL;NM1" Then If sSegmentID = "NM1" Then ' sValue = oSegment.DataElementValue(1) 'Entity Identifier Code ' sValue = oSegment.DataElementValue(2) 'Entity Type Qualifier oRsX096ProviderInfo("CompanyName").Value = oSegment.DataElementValue(3) 'Name Last or Organization Name ' sValue = oSegment.DataElementValue(4) 'Name First ' sValue = oSegment.DataElementValue(5) 'Name Middle ' sValue = oSegment.DataElementValue(6) 'Name Prefix ' sValue = oSegment.DataElementValue(7) 'Name Suffix ' sValue = oSegment.DataElementValue(8) 'Identification Code Qualifier oRsX096ProviderInfo("NationalID").Value = oSegment.DataElementValue(9) 'Identification Code ElseIf sSegmentID = "N3" Then oRsX096ProviderInfo("Address1").Value = oSegment.DataElementValue(1) 'Address Information ElseIf sSegmentID = "N4" Then oRsX096ProviderInfo("City").Value = oSegment.DataElementValue(1) 'City Name oRsX096ProviderInfo("State").Value = oSegment.DataElementValue(2) 'State or Province Code oRsX096ProviderInfo("Zip").Value = oSegment.DataElementValue(3) 'Postal Code ElseIf sSegmentID = "REF" Then ' sValue = oSegment.DataElementValue(1) 'Reference Identification Qualifier oRsX096ProviderInfo("CommercialNo").Value = oSegment.DataElementValue(2) 'Reference Identification ElseIf sSegmentID = "DMG" Then ' sValue = oSegment.DataElementValue(1) 'Date Time Period Format Qualifier ' sValue = oSegment.DataElementValue(2) 'Date Time Period ' sValue = oSegment.DataElementValue(3) 'Gender Code End If 'Segment ID End If 'sLoopSection ElseIf sLoopHLQlfr = "22" Then 'Subscriber If sLoopSection = "HL" Then If sSegmentID = "HL" Then If Not oRsX096SubscriberInfo.BOF Then oRsX096SubscriberInfo.Update End If oRsX096SubscriberInfo.AddNew oRsX096SubscriberInfo("BillProviderKey").Value = oRsX096ProviderInfo("BillProviderKey").Value ' sValue = oSegment.DataElementValue(1) 'Hierarchical ID Number ' sValue = oSegment.DataElementValue(2) 'Hierarchical Parent ID Number ' sValue = oSegment.DataElementValue(3) 'Hierarchical Level Code ' sValue = oSegment.DataElementValue(4) 'Hierarchical Child Code ' ElseIf sSegmentID = "SBR" Then ' sValue = oSegment.DataElementValue(1) 'Payer Responsibility Sequence Number Code If oSegment.DataElementValue(2) = "18" Then 'Subscriber is the patient oRsX096SubscriberInfo("Patient").Value = "Y" Else oRsX096SubscriberInfo("Patient").Value = "N" End If ' sValue = oSegment.DataElementValue(3) 'Reference Identification ' sValue = oSegment.DataElementValue(4) 'Name ' sValue = oSegment.DataElementValue(5) 'Insurance Type Code ' sValue = oSegment.DataElementValue(6) 'Coordination of Benefits Code ' sValue = oSegment.DataElementValue(7) 'Yes/No Condition or Response Code ' sValue = oSegment.DataElementValue(8) 'Employment Status Code ' sValue = oSegment.DataElementValue(9) 'Claim Filing Indicator Code ElseIf sSefmentId = "PAT" Then End If 'Segment ID ElseIf sLoopSection = "HL;NM1" Then If sSegmentID = "NM1" Then sEntityCode = oSegment.DataElementValue(1) End If If sEntityCode = "IL" Then If sSegmentID = "NM1" Then ' sValue = oSegment.DataElementValue(1) 'Entity Identifier Code ' sValue = oSegment.DataElementValue(2) 'Entity Type Qualifier oRsX096SubscriberInfo("SubscriberLastOrgName").Value = oSegment.DataElementValue(3) 'Name Last or Organization Name oRsX096SubscriberInfo("SubscriberFirstName").Value = oSegment.DataElementValue(4) 'Name First ' sValue = oSegment.DataElementValue(5) 'Name Middle ' sValue = oSegment.DataElementValue(6) 'Name Prefix ' sValue = oSegment.DataElementValue(7) 'Name Suffix ' sValue = oSegment.DataElementValue(8) 'Identification Code Qualifier oRsX096SubscriberInfo("SubscriberMemberID").Value = oSegment.DataElementValue(9) 'Identification Code ElseIf sSegmentID = "N3" Then oRsX096SubscriberInfo("SubscriberAddress").Value = oSegment.DataElementValue(1) 'Address Information ElseIf sSegmentID = "N4" Then oRsX096SubscriberInfo("SubscriberCity").Value = oSegment.DataElementValue(1) 'City Name ' sValue = oSegment.DataElementValue(2) 'State or Province Code oRsX096SubscriberInfo("SubscriberZip").Value = oSegment.DataElementValue(3) 'Postal Code ElseIf sSegmentID = "DMG" Then ' sValue = oSegment.DataElementValue(1) 'Date Time Period Format Qualifier ' sValue = oSegment.DataElementValue(2) 'Date Time Period ' sValue = oSegment.DataElementValue(3) 'Gender Code End If 'Segment ID ElseIf sEntityCode = "PR" Then If sSegmentID = "NM1" Then ' sValue = oSegment.DataElementValue(1) 'Entity Identifier Code ' sValue = oSegment.DataElementValue(2) 'Entity Type Qualifier oRsX096SubscriberInfo("PayerLastOrgName").Value = oSegment.DataElementValue(3) 'Name Last or Organization Name ' sValue = oSegment.DataElementValue(4) 'Name First ' sValue = oSegment.DataElementValue(5) 'Name Middle ' sValue = oSegment.DataElementValue(6) 'Name Prefix ' sValue = oSegment.DataElementValue(7) 'Name Suffix ' sValue = oSegment.DataElementValue(8) 'Identification Code Qualifier oRsX096SubscriberInfo("PayerID").Value = oSegment.DataElementValue(9) 'Identification Code End If End If 'sEntityCode ElseIf sLoopSection = "HL;CLM" Then 'Claims If sSegmentID = "CLM" Then If Not oRsX096Claims.BOF Then oRsX096Claims.Update End If oRsX096Claims.AddNew oRsX096Claims("SubscriberKey").Value = oRsX096SubscriberInfo("SubscriberKey").Value oRsX096Claims("ClaimSubmitID").Value = oSegment.DataElementValue(1) 'Claim Submitter's Identifier oRsX096Claims("ClaimAmount").Value = oSegment.DataElementValue(2) 'Monetary Amount ' sValue = oSegment.DataElementValue(3) 'Claim Filing Indicator Code ' sValue = oSegment.DataElementValue(4) 'Non-Institutional Claim Type Code ' sValue = oSegment.DataElementValue(5, 1) 'Facility Code Value ' sValue = oSegment.DataElementValue(5, 2) 'Facility Code Qualifier ' sValue = oSegment.DataElementValue(5, 3) 'Claim Frequency Type Code ' sValue = oSegment.DataElementValue(6) 'Yes/No Condition or Response Code ' sValue = oSegment.DataElementValue(7) 'Provider Accept Assignment Code ' sValue = oSegment.DataElementValue(8) 'Yes/No Condition or Response Code ' sValue = oSegment.DataElementValue(9) 'Release of Information Code ElseIf sSegmentID = "DTP" Then ' sValue = oSegment.DataElementValue(1) 'Date/Time Qualifier ' sValue = oSegment.DataElementValue(2) 'Date Time Period Format Qualifier oRsX096Claims("ClaimDate").Value = oSegment.DataElementValue(3) 'Date Time Period ElseIf sSegmentID = "CL1" Then ' sValue = oSegment.DataElementValue(1) 'Admission Type Code ' sValue = oSegment.DataElementValue(2) 'Admission Source Code ElseIf sSegmentID = "HI" Then ' sValue = oSegment.DataElementValue(1, 1) 'Code List Qualifier Code ' sValue = oSegment.DataElementValue(1, 2) 'Industry Code ' sValue = oSegment.DataElementValue(1, 3) 'Date Time Period Format Qualifier ' sValue = oSegment.DataElementValue(1, 4) 'Date Time Period ' sValue = oSegment.DataElementValue(1, 5) 'Monetary Amount ' sValue = oSegment.DataElementValue(1, 6) 'Quantity ' sValue = oSegment.DataElementValue(1, 7) 'Version Identifier End If 'Segment ID ElseIf sLoopSection = "HL;CLM;NM1" Then If sSegmentID = "NM1" Then sEntityCode = oSegment.DataElementValue(1) End If If sEntityCode = "71" Then 'Attending Physician If sSegmentID = "NM1" Then ' sValue = oSegment.DataElementValue(1) 'Entity Identifier Code ' sValue = oSegment.DataElementValue(2) 'Entity Type Qualifier oRsX096Claims("AttendingLastname").Value = oSegment.DataElementValue(3) 'Name Last or Organization Name oRsX096Claims("AttendingFirstname").Value = oSegment.DataElementValue(4) 'Name First ' sValue = oSegment.DataElementValue(5) 'Name Middle ' sValue = oSegment.DataElementValue(6) 'Name Prefix ' sValue = oSegment.DataElementValue(7) 'Name Suffix ' sValue = oSegment.DataElementValue(8) 'Identification Code Qualifier oRsX096Claims("AttendingID").Value = oSegment.DataElementValue(9) 'Identification Code ElseIf sSegmentID = "PRV" Then ' sValue = oSegment.DataElementValue(1) 'Provider Code ' sValue = oSegment.DataElementValue(2) 'Reference Identification Qualifier ' sValue = oSegment.DataElementValue(3) 'Reference Identification End If 'Segment ID End If 'sEntityCode ElseIf sLoopSection = "HL;CLM;SBR" Then 'Other Subscribers Info If sSegmentID = "SBR" Then If Not oRsX096OtherSubscriberInfo.BOF Then oRsX096OtherSubscriberInfo.Update End If oRsX096OtherSubscriberInfo.AddNew oRsX096OtherSubscriberInfo("ClaimKey").Value = oRsX096Claims("ClaimKey").Value ' sValue = oSegment.DataElementValue(1) 'Payer Responsibility Sequence Number Code ' sValue = oSegment.DataElementValue(2) 'Individual Relationship Code ' sValue = oSegment.DataElementValue(3) 'Reference Identification ' sValue = oSegment.DataElementValue(4) 'Name ' sValue = oSegment.DataElementValue(5) 'Insurance Type Code ' sValue = oSegment.DataElementValue(6) 'Coordination of Benefits Code ' sValue = oSegment.DataElementValue(7) 'Yes/No Condition or Response Code ' sValue = oSegment.DataElementValue(8) 'Employment Status Code ' sValue = oSegment.DataElementValue(9) 'Claim Filing Indicator Code ElseIf sSegmentID = "DMG" Then ' sValue = oSegment.DataElementValue(1) 'Date Time Period Format Qualifier ' sValue = oSegment.DataElementValue(2) 'Date Time Period ' sValue = oSegment.DataElementValue(3) 'Gender Code ElseIf sSegmentID = "OI" Then ' sValue = oSegment.DataElementValue(1) 'Claim Filing Indicator Code ' sValue = oSegment.DataElementValue(2) 'Claim Submission Reason Code ' sValue = oSegment.DataElementValue(3) 'Yes/No Condition or Response Code ' sValue = oSegment.DataElementValue(4) 'Patient Signature Source Code ' sValue = oSegment.DataElementValue(5) 'Provider Agreement Code ' sValue = oSegment.DataElementValue(6) 'Release of Information Code End If 'Segment ID ElseIf sLoopSection = "HL;CLM;SBR;NM1" Then If sSegmentID = "NM1" Then sEntityCode = oSegment.DataElementValue(1) End If If sEntityCode = "IL" Then 'Insured or Subscriber If sSegmentID = "NM1" Then ' sValue = oSegment.DataElementValue(1) 'Entity Identifier Code ' sValue = oSegment.DataElementValue(2) 'Entity Type Qualifier oRsX096OtherSubscriberInfo("OtherSubLastname").Value = oSegment.DataElementValue(3) 'Name Last or Organization Name ' sValue = oSegment.DataElementValue(4) 'Name First ' sValue = oSegment.DataElementValue(5) 'Name Middle ' sValue = oSegment.DataElementValue(6) 'Name Prefix ' sValue = oSegment.DataElementValue(7) 'Name Suffix ' sValue = oSegment.DataElementValue(8) 'Identification Code Qualifier oRsX096OtherSubscriberInfo("OtherSubMIN").Value = oSegment.DataElementValue(9) 'Identification Code ElseIf sSegmentID = "N3" Then ' sValue = oSegment.DataElementValue(1) 'Address Information ElseIf sSegmentID = "N4" Then ' sValue = oSegment.DataElementValue(1) 'City Name ' sValue = oSegment.DataElementValue(2) 'State or Province Code ' sValue = oSegment.DataElementValue(3) 'Postal Code End If 'Segment ID ElseIf sEntityCode = "PR" Then 'Payer If sSegmentID = "NM1" Then ' sValue = oSegment.DataElementValue(1) 'Entity Identifier Code ' sValue = oSegment.DataElementValue(2) 'Entity Type Qualifier oRsX096OtherSubscriberInfo("OtherPayerLastName").Value = oSegment.DataElementValue(3) 'Name Last or Organization Name ' sValue = oSegment.DataElementValue(4) 'Name First ' sValue = oSegment.DataElementValue(5) 'Name Middle ' sValue = oSegment.DataElementValue(6) 'Name Prefix ' sValue = oSegment.DataElementValue(7) 'Name Suffix ' sValue = oSegment.DataElementValue(8) 'Identification Code Qualifier oRsX096OtherSubscriberInfo("OtherPayerID").Value = oSegment.DataElementValue(9) 'Identification Code ElseIf sSegmentID = "N3" Then ' sValue = oSegment.DataElementValue(1) 'Address Information ElseIf sSegmentID = "N4" Then ' sValue = oSegment.DataElementValue(1) 'City Name ' sValue = oSegment.DataElementValue(2) 'State or Province Code ' sValue = oSegment.DataElementValue(3) 'Postal Code End If 'Segment ID End If 'sEntityCode ElseIf sLoopSection = "HL;CLM;LX" Then 'Service Line Info If sSegmentID = "LX" Then If Not oRsX096ServiceInfo.BOF Then oRsX096ServiceInfo.Update End If oRsX096ServiceInfo.AddNew oRsX096ServiceInfo("ClaimKey").Value = oRsX096Claims("ClaimKey").Value oRsX096ServiceInfo("ServiceLine").Value = oSegment.DataElementValue(1) 'Assigned Number ElseIf sSegmentID = "SV2" Then oRsX096ServiceInfo("ServiceID").Value = oSegment.DataElementValue(1) 'Product/Service ID ' sValue = oSegment.DataElementValue(2, 1) 'Product/Service ID Qualifier ' sValue = oSegment.DataElementValue(2, 2) 'Product/Service ID ' sValue = oSegment.DataElementValue(2, 3) 'Procedure Modifier ' sValue = oSegment.DataElementValue(2, 4) 'Procedure Modifier ' sValue = oSegment.DataElementValue(2, 5) 'Procedure Modifier ' sValue = oSegment.DataElementValue(2, 6) 'Procedure Modifier ' sValue = oSegment.DataElementValue(2, 7) 'Description oRsX096ServiceInfo("ServiceAmount").Value = oSegment.DataElementValue(3) 'Monetary Amount ' sValue = oSegment.DataElementValue(4) 'Unit or Basis for Measurement Code ' sValue = oSegment.DataElementValue(5) 'Quantity ElseIf sSegmentID = "DTP" Then ' sValue = oSegment.DataElementValue(1) 'Date/Time Qualifier ' sValue = oSegment.DataElementValue(2) 'Date Time Period Format Qualifier oRsX096ServiceInfo("ServiceDate").Value = oSegment.DataElementValue(3) 'Date Time Period End If 'sSegmentID End If 'sLoopSection ElseIf sLoopHLQlfr = "23" Then 'Dependent If sLoopSection = "HL" Then If sSegmentID = "HL" Then If Not oRsX096DependentInfo.BOF Then oRsX096DependentInfo.Update End If oRsX096DependentInfo.AddNew oRsX096DependentInfo("SubscriberKey").Value = oRsX096SubscriberInfo("SubscriberKey").Value ' sValue = oSegment.DataElementValue(1) 'Hierarchical ID Number ' sValue = oSegment.DataElementValue(2) 'Hierarchical Parent ID Number ' sValue = oSegment.DataElementValue(3) 'Hierarchical Level Code ' sValue = oSegment.DataElementValue(4) 'Hierarchical Child Code ElseIf sSegmentID = "PAT" Then If oSegment.DataElementValue(1) = "01" Then oRsX096DependentInfo("Relationship").Value = "SPOUSE" ElseIf oSegment.DataElementValue(1) = "19" Then oRsX096DependentInfo("Relationship").Value = "CHILD" Else oRsX096DependentInfo("Relationship").Value = "OTHER" End If End If 'Segment ID ElseIf sLoopSection = "HL;NM1" Then If sSegmentID = "NM1" Then sEntityCode = oSegment.DataElementValue(1) End If If sEntityCode = "QC" Then If sSegmentID = "NM1" Then ' sValue = oSegment.DataElementValue(1) 'Entity Identifier Code ' sValue = oSegment.DataElementValue(2) 'Entity Type Qualifier oRsX096DependentInfo("Lastname").Value = oSegment.DataElementValue(3) 'Name Last or Organization Name oRsX096DependentInfo("Firstname").Value = oSegment.DataElementValue(4) 'Name First ' sValue = oSegment.DataElementValue(5) 'Name Middle ' sValue = oSegment.DataElementValue(6) 'Name Prefix ' sValue = oSegment.DataElementValue(7) 'Name Suffix ' sValue = oSegment.DataElementValue(8) 'Identification Code Qualifier ' sValue = oSegment.DataElementValue(9) 'Identification Code ElseIf sSegmentID = "N3" Then oRsX096DependentInfo("Address").Value = oSegment.DataElementValue(1) 'Address Information ElseIf sSegmentID = "N4" Then oRsX096DependentInfo("City").Value = oSegment.DataElementValue(1) 'City Name oRsX096DependentInfo("State").Value = oSegment.DataElementValue(2) 'State or Province Code oRsX096DependentInfo("Zip").Value = oSegment.DataElementValue(3) 'Postal Code ElseIf sSegmentID = "DMG" Then ' sValue = oSegment.DataElementValue(1) 'Date Time Period Format Qualifier oRsX096DependentInfo("DOB").Value = oSegment.DataElementValue(2) 'Date Time Period oRsX096DependentInfo("Gender").Value = oSegment.DataElementValue(3) 'Gender Code End If 'Segment ID End If 'sEntityCode ElseIf sLoopSection = "HL;CLM" Then 'Claims If sSegmentID = "CLM" Then If Not oRsX096Claims.BOF Then oRsX096Claims.Update End If oRsX096Claims.AddNew oRsX096Claims("SubscriberKey").Value = oRsX096DependentInfo("SubscriberKey").Value oRsX096Claims("DependentKey").Value = oRsX096DependentInfo("DependentKey").Value oRsX096Claims("ClaimSubmitID").Value = oSegment.DataElementValue(1) 'Claim Submitter's Identifier oRsX096Claims("ClaimAmount").Value = oSegment.DataElementValue(2) 'Monetary Amount ' sValue = oSegment.DataElementValue(3) 'Claim Filing Indicator Code ' sValue = oSegment.DataElementValue(4) 'Non-Institutional Claim Type Code ' sValue = oSegment.DataElementValue(5, 1) 'Facility Code Value ' sValue = oSegment.DataElementValue(5, 2) 'Facility Code Qualifier ' sValue = oSegment.DataElementValue(5, 3) 'Claim Frequency Type Code ' sValue = oSegment.DataElementValue(6) 'Yes/No Condition or Response Code ' sValue = oSegment.DataElementValue(7) 'Provider Accept Assignment Code ' sValue = oSegment.DataElementValue(8) 'Yes/No Condition or Response Code ' sValue = oSegment.DataElementValue(9) 'Release of Information Code ElseIf sSegmentID = "DTP" Then ' sValue = oSegment.DataElementValue(1) 'Date/Time Qualifier ' sValue = oSegment.DataElementValue(2) 'Date Time Period Format Qualifier oRsX096Claims("ClaimDate").Value = oSegment.DataElementValue(3) 'Date Time Period ElseIf sSegmentID = "CL1" Then ' sValue = oSegment.DataElementValue(1) 'Admission Type Code ' sValue = oSegment.DataElementValue(2) 'Admission Source Code ElseIf sSegmentID = "HI" Then ' sValue = oSegment.DataElementValue(1, 1) 'Code List Qualifier Code ' sValue = oSegment.DataElementValue(1, 2) 'Industry Code ' sValue = oSegment.DataElementValue(1, 3) 'Date Time Period Format Qualifier ' sValue = oSegment.DataElementValue(1, 4) 'Date Time Period ' sValue = oSegment.DataElementValue(1, 5) 'Monetary Amount ' sValue = oSegment.DataElementValue(1, 6) 'Quantity ' sValue = oSegment.DataElementValue(1, 7) 'Version Identifier End If 'Segment ID ElseIf sLoopSection = "HL;CLM;NM1" Then If sSegmentID = "NM1" Then sEntityCode = oSegment.DataElementValue(1) End If If sEntityCode = "71" Then 'Attending Physician If sSegmentID = "NM1" Then ' sValue = oSegment.DataElementValue(1) 'Entity Identifier Code ' sValue = oSegment.DataElementValue(2) 'Entity Type Qualifier oRsX096Claims("AttendingLastname").Value = oSegment.DataElementValue(3) 'Name Last or Organization Name oRsX096Claims("AttendingFirstname").Value = oSegment.DataElementValue(4) 'Name First ' sValue = oSegment.DataElementValue(5) 'Name Middle ' sValue = oSegment.DataElementValue(6) 'Name Prefix ' sValue = oSegment.DataElementValue(7) 'Name Suffix ' sValue = oSegment.DataElementValue(8) 'Identification Code Qualifier oRsX096Claims("AttendingID").Value = oSegment.DataElementValue(9) 'Identification Code ElseIf sSegmentID = "PRV" Then ' sValue = oSegment.DataElementValue(1) 'Provider Code ' sValue = oSegment.DataElementValue(2) 'Reference Identification Qualifier ' sValue = oSegment.DataElementValue(3) 'Reference Identification End If 'Segment ID End If 'sEntityCode ElseIf sLoopSection = "HL;CLM;SBR" Then 'Other Subscribers Info If sSegmentID = "SBR" Then If Not oRsX096OtherSubscriberInfo.BOF Then oRsX096OtherSubscriberInfo.Update End If oRsX096OtherSubscriberInfo.AddNew oRsX096OtherSubscriberInfo("ClaimKey").Value = oRsX096Claims("ClaimKey").Value ' sValue = oSegment.DataElementValue(1) 'Payer Responsibility Sequence Number Code ' sValue = oSegment.DataElementValue(2) 'Individual Relationship Code ' sValue = oSegment.DataElementValue(3) 'Reference Identification ' sValue = oSegment.DataElementValue(4) 'Name ' sValue = oSegment.DataElementValue(5) 'Insurance Type Code ' sValue = oSegment.DataElementValue(6) 'Coordination of Benefits Code ' sValue = oSegment.DataElementValue(7) 'Yes/No Condition or Response Code ' sValue = oSegment.DataElementValue(8) 'Employment Status Code ' sValue = oSegment.DataElementValue(9) 'Claim Filing Indicator Code ElseIf sSegmentID = "DMG" Then ' sValue = oSegment.DataElementValue(1) 'Date Time Period Format Qualifier ' sValue = oSegment.DataElementValue(2) 'Date Time Period ' sValue = oSegment.DataElementValue(3) 'Gender Code ElseIf sSegmentID = "OI" Then ' sValue = oSegment.DataElementValue(1) 'Claim Filing Indicator Code ' sValue = oSegment.DataElementValue(2) 'Claim Submission Reason Code ' sValue = oSegment.DataElementValue(3) 'Yes/No Condition or Response Code ' sValue = oSegment.DataElementValue(4) 'Patient Signature Source Code ' sValue = oSegment.DataElementValue(5) 'Provider Agreement Code ' sValue = oSegment.DataElementValue(6) 'Release of Information Code End If 'Segment ID ElseIf sLoopSection = "HL;CLM;SBR;NM1" Then If sSegmentID = "NM1" Then sEntityCode = oSegment.DataElementValue(1) End If If sEntityCode = "IL" Then 'Insured or Subscriber If sSegmentID = "NM1" Then ' sValue = oSegment.DataElementValue(1) 'Entity Identifier Code ' sValue = oSegment.DataElementValue(2) 'Entity Type Qualifier oRsX096OtherSubscriberInfo("OtherSubLastname").Value = oSegment.DataElementValue(3) 'Name Last or Organization Name ' sValue = oSegment.DataElementValue(4) 'Name First ' sValue = oSegment.DataElementValue(5) 'Name Middle ' sValue = oSegment.DataElementValue(6) 'Name Prefix ' sValue = oSegment.DataElementValue(7) 'Name Suffix ' sValue = oSegment.DataElementValue(8) 'Identification Code Qualifier oRsX096OtherSubscriberInfo("OtherSubMIN").Value = oSegment.DataElementValue(9) 'Identification Code ElseIf sSegmentID = "N3" Then ' sValue = oSegment.DataElementValue(1) 'Address Information ElseIf sSegmentID = "N4" Then ' sValue = oSegment.DataElementValue(1) 'City Name ' sValue = oSegment.DataElementValue(2) 'State or Province Code ' sValue = oSegment.DataElementValue(3) 'Postal Code End If 'Segment ID ElseIf sEntityCode = "PR" Then 'Payer If sSegmentID = "NM1" Then ' sValue = oSegment.DataElementValue(1) 'Entity Identifier Code ' sValue = oSegment.DataElementValue(2) 'Entity Type Qualifier oRsX096OtherSubscriberInfo("OtherPayerLastName").Value = oSegment.DataElementValue(3) 'Name Last or Organization Name ' sValue = oSegment.DataElementValue(4) 'Name First ' sValue = oSegment.DataElementValue(5) 'Name Middle ' sValue = oSegment.DataElementValue(6) 'Name Prefix ' sValue = oSegment.DataElementValue(7) 'Name Suffix ' sValue = oSegment.DataElementValue(8) 'Identification Code Qualifier oRsX096OtherSubscriberInfo("OtherPayerID").Value = oSegment.DataElementValue(9) 'Identification Code ElseIf sSegmentID = "N3" Then ' sValue = oSegment.DataElementValue(1) 'Address Information ElseIf sSegmentID = "N4" Then ' sValue = oSegment.DataElementValue(1) 'City Name ' sValue = oSegment.DataElementValue(2) 'State or Province Code ' sValue = oSegment.DataElementValue(3) 'Postal Code End If 'Segment ID End If 'sEntityCode ElseIf sLoopSection = "HL;CLM;LX" Then 'Service Line Info If sSegmentID = "LX" Then If Not oRsX096ServiceInfo.BOF Then oRsX096ServiceInfo.Update End If oRsX096ServiceInfo.AddNew oRsX096ServiceInfo("ClaimKey").Value = oRsX096Claims("ClaimKey").Value oRsX096ServiceInfo("ServiceLine").Value = oSegment.DataElementValue(1) 'Assigned Number ElseIf sSegmentID = "SV2" Then oRsX096ServiceInfo("ServiceID").Value = oSegment.DataElementValue(1) 'Product/Service ID ' sValue = oSegment.DataElementValue(2, 1) 'Product/Service ID Qualifier ' sValue = oSegment.DataElementValue(2, 2) 'Product/Service ID ' sValue = oSegment.DataElementValue(2, 3) 'Procedure Modifier ' sValue = oSegment.DataElementValue(2, 4) 'Procedure Modifier ' sValue = oSegment.DataElementValue(2, 5) 'Procedure Modifier ' sValue = oSegment.DataElementValue(2, 6) 'Procedure Modifier ' sValue = oSegment.DataElementValue(2, 7) 'Description oRsX096ServiceInfo("ServiceAmount").Value = oSegment.DataElementValue(3) 'Monetary Amount ' sValue = oSegment.DataElementValue(4) 'Unit or Basis for Measurement Code ' sValue = oSegment.DataElementValue(5) 'Quantity ElseIf sSegmentID = "DTP" Then ' sValue = oSegment.DataElementValue(1) 'Date/Time Qualifier ' sValue = oSegment.DataElementValue(2) 'Date Time Period Format Qualifier oRsX096ServiceInfo("ServiceDate").Value = oSegment.DataElementValue(3) 'Date Time Period End If 'sSegmentID End If 'sLoopSection End If 'sLoopHLQlfr End If 'nArea 'GETS THE NEXT DATA SEGMENT Set oSegment = oSegment.Next Loop 'save all changes into database oRsX096ServiceInfo.Update oRsX096OtherSubscriberInfo.Update oRsX096Claims.Update oRsX096ProviderInfo.Update oRsX096SubscriberInfo.Update oRsX096DependentInfo.Update oRsX096Header.Update oRsFuncGroup.Update oRsInterchange.Update 'close tables oRsX096ServiceInfo.Close oRsX096OtherSubscriberInfo.Close oRsX096Claims.Close oRsX096ProviderInfo.Close oRsX096SubscriberInfo.Close oRsX096DependentInfo.Close oRsX096Header.Close oRsFuncGroup.Close oRsInterchange.Close 'DESTROY OBJECTS Set oEdiDoc = Nothing Set oSchema = Nothing Set oSchemas = Nothing Set oSegment = Nothing MsgBox "Done" End Sub