the pxid (which is the autonumber of the tblPax) in tblsector and tblFarPaxValue only shows last paxid 2 when there are two pax which should be showing 1 and 2. How can get it corrected.
The FileId in each table gets populated automatically but not the pxid, why is that ?
Code:
RSFiles.AddNew
myfileid = Nz(DMax("FileID", "tblFiles"), 0) + 1
RSFiles![FileID] = myfileid
RSFiles![FIleName] = srcfile
RSFiles![ProcessedOn] = Date
RSFiles![MIRCrDate] = MIRDate
RSFiles![PNRCrDate] = PNRDate
RSFiles![BkgTkgPCC] = BkgTkgPCC
RSFiles![BkgSignOnTkgSignOn] = BkgSignOnTkgSignOn
RSFiles.Update
RSPax.AddNew
RSPax![FileID] = myfileid
[COLOR=Red]pxid = Nz(DMax("PaxID", "tblPax"), 0) + 1
RSPax![PaxID] = pxid[/COLOR]
RSPax![Passengers] = Pax
RSPax![TicketNumber] = tkt
RSPax![PaxType] = PaxType
RSPax![PaxSeq] = PaxSeq
RSPax.Update
passenger = tkt & " " & Pax
Line Input #1, TextLine
Line Input #1, TextLine
Do While Left(TextLine, 3) = ("A02") 'Loops through remaining A02 (Pax) lines (if any)
Pax = Trim(Mid(TextLine, 4, 30))
tkt = ALC & Trim(Mid(TextLine, 49, 10))
PaxType = Trim(Mid(TextLine, 67, 6))
PaxSeq = Trim(Mid(TextLine, 76, 2))
'If PNR = "ZZZZZZ" Then 'Check for valid PNR
' txtMIRActivity.Value = srcfile & " " & tkt & " " & "VOID TKT" & vbNewLine & txtMIRActivity.Value
'Else
' txtMIRActivity.Value = srcfile & " " & tkt & " " & "Processed" & vbNewLine & txtMIRActivity.Value
'End If
RSPax.AddNew[COLOR=Red]
pxid = Nz(DMax("PaxID", "tblPax"), 0) + 1
RSPax![PaxID] = pxid[/COLOR]
RSPax![FileID] = myfileid
RSPax![Passengers] = Pax
RSPax![TicketNumber] = tkt
RSPax![PaxType] = PaxType
RSPax![PaxSeq] = PaxSeq
RSPax.Update
Line Input #1, TextLine
Line Input #1, TextLine
Loop
End If
If Left(TextLine, 3) = "A04" Then 'Reads First A04(Secotor) Line
sectors = Trim(Mid(TextLine, 47, 3)) & "-" & Trim(Mid(TextLine, 63, 3))
AirSector = sectors
RSSector.AddNew
SectorID = Nz(DMax("SectorID", "tblsector"), 0) + 1
RSSector![SectorID] = SectorID
RSSector![FileID] = myfileid
[COLOR=Red]RSSector![PaxID] = pxid[/COLOR]
RSSector![Sector] = AirSector
RSSector![Airline] = IssueAirline
RSSector![PNR] = PNR
RSSector.Update
Line Input #1, TextLine
Do While Left(TextLine, 3) = "A04" 'Loops through following A04(Sectors) lines (if any)
sectors = Trim(Mid(TextLine, 63, 3))
AirSector = AirSector & "-" & sectors
Line Input #1, TextLine
Loop
RSSector.AddNew
SectorID = Nz(DMax("SectorID", "tblsector"), 0) + 1
RSSector![SectorID] = SectorID
RSSector![FileID] = myfileid
[COLOR=Red]RSSector![PaxID] = pxid[/COLOR]
RSSector![Sector] = AirSector
RSSector![Airline] = IssueAirline
RSSector![PNR] = PNR
RSSector.Update
End If
If Left(TextLine, 3) = "A07" Then 'Reads base fare, Total fare and 5 Tax's
If Trim(Mid(TextLine, 4, 2)) = "" Then
Seq = 0
Else
Seq = Nz(CDbl(Mid(TextLine, 4, 2)), 0)
End If
BFC = Trim(Mid(TextLine, 6, 3)) & ": "
If Trim(Mid(TextLine, 9, 12)) = "" Then
BaseFare = 0
Else
BaseFare = CDbl(Mid(TextLine, 9, 12))
End If
If Trim(Mid(TextLine, 39, 12)) = "" Then
EQAmt = 0
Else
EQAmt = CDbl(Mid(TextLine, 39, 12))
End If
If Trim(Mid(TextLine, 24, 12)) = "" Then
TotalFare = 0
Else
TotalFare = CDbl(Mid(TextLine, 24, 12))
End If
If Trim(Mid(TextLine, 57, 8)) = "" Then
Tax1 = 0
Else
Tax1 = CDbl(Trim(Mid(TextLine, 57, 8)))
End If
If Trim(Mid(TextLine, 70, 8)) = "" Then
Tax2 = 0
Else
Tax2 = CDbl(Trim(Mid(TextLine, 70, 8)))
End If
If Trim(Mid(TextLine, 83, 8)) = "" Then
Tax3 = 0
Else
Tax3 = CDbl(Trim(Mid(TextLine, 83, 8)))
End If
If Trim(Mid(TextLine, 96, 8)) = "" Then
Tax4 = 0
Else
Tax4 = CDbl(Trim(Mid(TextLine, 96, 8)))
End If
If Trim(Mid(TextLine, 109, 8)) = "" Then
Tax5 = 0
Else
Tax5 = CDbl(Trim(Mid(TextLine, 109, 8)))
End If
RSPXFare.AddNew
mypfvid = Nz(DMax("PFVID", "tblPAXFareValue"), 0) + 1
RSPXFare![PFVID] = mypfvid
RSPXFare![SectorID] = SectorID
RSPXFare![PaxSeq] = Seq
[COLOR=Red]RSPXFare![PaxID] = pxid[/COLOR]
RSPXFare![FileID] = myfileid
RSPXFare![BaseFare] = BaseFare
RSPXFare![TotalFare] = TotalFare
RSPXFare![EquivalentFare] = EQAmt
RSPXFare![Tax1] = Tax1
RSPXFare![Tax2] = Tax2
RSPXFare![Tax3] = Tax3
RSPXFare![Tax4] = Tax4
RSPXFare![Tax5] = Tax5
RSPXFare.Update
Line Input #1, TextLine
Do While Left(TextLine, 3) = "A07" 'Reads remaining fares
If Trim(Mid(TextLine, 4, 2)) = "" Then
Seq = 0
Else
Seq = Nz(CDbl(Mid(TextLine, 4, 2)), 0)
End If
BFC = Trim(Mid(TextLine, 6, 3)) & ": "
If Trim(Mid(TextLine, 9, 12)) = "" Then
BaseFare = 0
Else
BaseFare = CDbl(Mid(TextLine, 9, 12))
End If
If Trim(Mid(TextLine, 39, 12)) = "" Then
EQAmt = 0
Else
EQAmt = CDbl(Mid(TextLine, 39, 12))
End If
If Trim(Mid(TextLine, 24, 12)) = "" Then
TotalFare = 0
Else
TotalFare = CDbl(Mid(TextLine, 24, 12))
End If
If Trim(Mid(TextLine, 57, 8)) = "" Then
Tax1 = 0
Else
Tax1 = CDbl(Trim(Mid(TextLine, 57, 8)))
End If
If Trim(Mid(TextLine, 70, 8)) = "" Then
Tax2 = 0
Else
Tax2 = CDbl(Trim(Mid(TextLine, 70, 8)))
End If
If Trim(Mid(TextLine, 83, 8)) = "" Then
Tax3 = 0
Else
Tax3 = CDbl(Trim(Mid(TextLine, 83, 8)))
End If
If Trim(Mid(TextLine, 96, 8)) = "" Then
Tax4 = 0
Else
Tax4 = CDbl(Trim(Mid(TextLine, 96, 8)))
End If
If Trim(Mid(TextLine, 109, 8)) = "" Then
Tax5 = 0
Else
Tax5 = CDbl(Trim(Mid(TextLine, 109, 8)))
End If
RSPXFare.AddNew
mypfvid = Nz(DMax("PFVID", "tblPAXFareValue"), 0) + 1
RSPXFare![PFVID] = mypfvid
RSPXFare![SectorID] = SectorID
RSPXFare![PaxSeq] = Seq
[COLOR=Red]RSPXFare![PaxID] = pxid[/COLOR]
RSPXFare![FileID] = myfileid
RSPXFare![BaseFare] = BaseFare
RSPXFare![TotalFare] = TotalFare
RSPXFare![EquivalentFare] = EQAmt
RSPXFare![Tax1] = Tax1
RSPXFare![Tax2] = Tax2
RSPXFare![Tax3] = Tax3
RSPXFare![Tax4] = Tax4
RSPXFare![Tax5] = Tax5
RSPXFare.Update
'Line Input #1, TextLine
Line Input #1, TextLine
Loop
End If