Read a file and import its data into a table (2 Viewers)

namliam · Dec 4, 2014

anishkgt said:
just noticed, I need data from line A07 as it has the fares for each passenger. Should that go into a new table like tbleFares or any of th existing ?

Geven the structure of the file i would make a separate table per type of line that does have your paxseq, just Sems easiest that way

What part of the code is throwing you the error?
I expect like i told you a few times already it has something to do with your recordstet being closed

namliam · Dec 4, 2014

anishkgt said:

Code:

A04[COLOR=Red][B]01[/B][/COLOR]QR157QATAR AIRWAY   5V HK02DEC1720 2200 2DOHDOHA         LHRLONDON/HEATHRINM   O0   333    03259F TK:YJT:07.40ANL:QATAR AIRWAYS           DDL:02DEC14
A04[COLOR=Red][B]02[/B][/COLOR]QR157QATAR AIRWAY   4B HK20DEC1415 2355 2LHRLONDON/HEATHRDOHDOHA         INM   O0   388 T4 03259F TK:YJT:06.40ANL:QATAR AIRWAYS           DDL:20D

Side note:
The ones marked in RED are not linked with any data of the PAX

These may not be related to the pax, however they are legs of either the flight or the ticket booked by the passengers?
So make a separate table and store your required info + what ever linking data you need (ticket number or flight number)

anishkgt · Dec 5, 2014

The error line is in red

Code:

If Left(TextLine, 3) = "A07" Then 'Reads base fare, Total fare and 5 Tax's
                BFC = Trim(Mid(TextLine, 6, 3)) & ": "
                BaseFare = Mid(TextLine, 9, 12)
                If Trim(Mid(TextLine, 39, 12)) = "" Then
                    EQAmt = 0
                Else
                    EQAmt = CDbl(Mid(TextLine, 39, 12))
                End If
                TotalFare = Mid(TextLine, 24, 12)
                Tax1 = Trim(Mid(TextLine, 57, 8))
                Tax2 = Trim(Mid(TextLine, 70, 8))
                Tax3 = Trim(Mid(TextLine, 83, 8))
                Tax4 = Trim(Mid(TextLine, 96, 8))
                Tax5 = Trim(Mid(TextLine, 109, 8))
                [B][COLOR=Red]RSPXFare.AddNew[/COLOR][/B]
                RSPXFare![BaseFare] = BaseFare
                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
                Do While Left(TextLine, 3) = "A07" 'Reads remaining fares
                    BFC = Trim(Mid(TextLine, 6, 3)) & ": "
                    BaseFare = Mid(TextLine, 9, 12)
                    If Trim(Mid(TextLine, 39, 12)) = "" Then
                    EQAmt = 0
                Else
                    EQAmt = CDbl(Mid(TextLine, 39, 12))
                End If
                    TotalFare = Mid(TextLine, 24, 12)
                    Tax1 = Trim(Mid(TextLine, 57, 8))
                    Tax2 = Trim(Mid(TextLine, 70, 8))
                    Tax3 = Trim(Mid(TextLine, 83, 8))
                    Tax4 = Trim(Mid(TextLine, 96, 8))
                    Tax5 = Trim(Mid(TextLine, 109, 8))
                    Line Input #1, TextLine
                    Line Input #1, TextLine
                Loop
            End If

anishkgt · Dec 5, 2014

Ooops ! my bad. did not set the variable to the table. Done now.

namliam · Dec 5, 2014

That is what I was talking about, setting the table

Arent you missing the PAXSeq in this table (and possibly the FileID)?

anishkgt · Dec 5, 2014

Yup careless mistakes

I was going to ask you about how to populate the FileID for each table from tblFiles for all the tables and the paxseq for this table.

I only know the addnew part not the edit syntax. How do i add the FileID to each table and paxseq

namliam · Dec 5, 2014

You dont need to edit anything.... you can simple add them while adding, there are two different things though.
1) FileID
This is unique per file, earlier in your code (see my post #198) you filed FileID.
Here in this part of the code simply add it to your record

2) PaxSeq
Already exists on this line, so simply extract it the same as the other information and add it to your table

Dont really see this as being complex?

anishkgt · Dec 5, 2014

Not sure if i got it correct but here it is

Code:

    Set RSFiles = CurrentDb.OpenRecordset("tblFiles")
    Set RSPax = CurrentDb.OpenRecordset("tblPax")
    Set RSPXFare = CurrentDb.OpenRecordset("tblPAXFareValue")
    Set RSSector = CurrentDb.OpenRecordset("tblSector")
    FileID = Nz(DMax("FileID", "tblFiles"), 0) + 1
.
.
.
.
.
.
.
.
RSFiles.AddNew
        RSFiles![FileID] = FileID
        RSFiles![FileName] = srcfile
        RSFiles![PNR] = PNR
        RSFiles.Update

the result in the table tblFIles is that FileID of all the records are 1

namliam · Dec 5, 2014

The FileID = Nz(Dmax()) line needs to be just before the RSFiles.Addnew
If it is not there, then it would seem logical the FileID= line is only executed once and will always be 1

BlueIshDan · Dec 5, 2014

You're the man namliam

namliam · Dec 5, 2014

BlueIshDan said:
You're the man namliam

Thanks, but huh what where? why who? when whatshamadidi?

Why have I been giving this tittle?

anishkgt · Dec 5, 2014

all seem to be working well except the paxSeq. how do i add the paxseq from tblpax to the tblPaxFareValue ? tired the concept used in for the FileID, the PaxID came correct but the PaxID in tblPaxFareValue was repeating.

BlueIshDan · Dec 5, 2014

For you patients and willingness to help people to the extent that you display in this thread. Kudos to you my friend.

anishkgt · Dec 5, 2014

that's what makes oneself different from the others

namliam · Dec 5, 2014

The paxseq is on the same line as well isnt it?

A0701
A0702

Or whatever A line it was

anishkgt · Dec 5, 2014

ah yes, how could i forget that ? got it, done. all ok, now for some error traps. was trying the below and it was working before

Code:

If DCount("PaxID", "tblPax", "[Ticket#]=" & tkt) Then 'Find ticket # Duplicate

i get an error "data type mismatch in criteria expression"only when condition is met else the field is populated.

anishkgt · Dec 5, 2014

I guess there is a glitch. The FileID does not seem to be populated to other tables its just repeating the ID.

Code:

If Left(TextLine, 3) = Trim("A02") Then  'First A02(PAX) line
                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))
                txtMIRActivity.Value = srcfile & " " & tkt & " " & "Processed" & vbNewLine & txtMIRActivity.Value
                tktDisp = ALC & Trim(Mid(TextLine, 49, 10))
                'If DCount("PaxID", "tblPax", "[Ticket#]='" & tkt) Then 'Find ticket # Duplicate
                '   'Name SrcFolder & srcfile As DupFolder & srcfile
                '    txtMIRActivity.Value = srcfile & " " & tkt & " " & "DUPLICATE TICKET " & vbNewLine & txtMIRActivity.Value
                '    GoTo NextFile 'Abort and move to next file
                'End If
                RSPax.AddNew
                [COLOR=Red][B]RSPax![FileID] = myfileid[/B][/COLOR]
                pxid = Nz(DMax("PaxID", "tblPax"), 0) + 1
                RSPax![paxid] = pxid
                RSPax![Passengers] = Pax
                RSPax![ticket#] = tkt
                RSPax![PaxType] = PaxType
                RSPax![PaxSeq] = PaxSeq
                RSPax.Update
                If Trim(Mid(TextLine, 49, 10)) = "" Then
                    Close #1
'Name SrcFolder & srcfile As NoTicket & srcfile
                    txtMIRActivity.Value = srcfile & " " & PNR & " " & "No Ticket number " & vbNewLine & txtMIRActivity.Value
                    GoTo NextFile 'Abort and move to next file
                Else
                    tktNum = Trim(Mid(TextLine, 49, 10))
                End If
                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))
                    txtMIRActivity.Value = srcfile & " " & tkt & " " & "Processed" & vbNewLine & txtMIRActivity.Value
                    RSPax.AddNew
                    pxid = Nz(DMax("PaxID", "tblPax"), 0) + 1
                    RSPax![paxid] = pxid
                    [COLOR=Red][B]RSPax![FileID] = myfileid[/B][/COLOR]
                    RSPax![Passengers] = Pax
                    RSPax![ticket#] = tkt
                    RSPax![PaxType] = PaxType
                    RSPax![PaxSeq] = PaxSeq
                    RSPax.Update
                    Line Input #1, TextLine
                    Line Input #1, TextLine
                Loop
            End If

I did not notice until i was building the query as there duplicates coming up.

anishkgt · Dec 5, 2014

Aite ! having bad day:banghead:.......Figured out everything just need help with post 216

anishkgt · Dec 5, 2014

Figured that out too....whew !

anishkgt · Dec 6, 2014

Never had to come across creating forms from a query but here there seems to be a need. I did try making the current query into a form for manual entry but the form when displayed in the form view goes completely blank.

How can i create this form ?

Read a file and import its data into a table (2 Viewers)

The Mailman - AWF VIP

The Mailman - AWF VIP

Registered User.

Registered User.

The Mailman - AWF VIP

Registered User.

The Mailman - AWF VIP

Registered User.

The Mailman - AWF VIP

&#9760;

The Mailman - AWF VIP

Registered User.

&#9760;

Registered User.

The Mailman - AWF VIP

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Similar threads

Users who are viewing this thread

☠

☠