Read a file and import its data into a table (1 Viewer)

A

anishkgt

Registered User.
Local time
Today, 11:10
Joined
Nov 4, 2013
Messages
384
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.
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
The FileId in each table gets populated automatically but not the pxid, why is that ?
 
N

namliam

The Mailman - AWF VIP
Local time
Today, 10:10
Joined
Aug 11, 2003
Messages
11,695
anishkgt said:
the pxid (which is the autonumber of the tblPax)
Click to expand...
Autonumber, as in Automatic number.... you shouldnt need to fill it....

Dont think or expect FileID to be an Autonumber either.
 
A

anishkgt

Registered User.
Local time
Today, 11:10
Joined
Nov 4, 2013
Messages
384
no fileid is not auto number. I was saying the fileID although being a manual numbering which increments by 1 from within code is able to be populated to other tables and even pxid is the same but is not being populated to other tables which is PK for the following tables like the FileID. Rather only the last pxid is being populated. Should i be placing the code pxid= nz........ else where ?
 
N

namliam

The Mailman - AWF VIP
Local time
Today, 10:10
Joined
Aug 11, 2003
Messages
11,695
Ah wait, I see what you are on about, be CAREFULL in your wording though, something you create yourself with something like DMax()+1 isnt an AutoNumber.
AutoNumber is a field type that you can define in a table, that doesnt require any attention and gets automagicaly filled each time you add a record.

What you are doing you cant really do... Not in the way you are doing it ...

Why try and insert a PaxID anyways when you have the combination of FileID + PaxSeq to make a key value.

If you are "stuck" on making the PaxID, assuming you want to make this work for any number of passengers you would probably be best off using an Array to store your PaxID in, which will be much faster than using a DLookup which would be an alternative.

The A04 and A07 lines do have the PaxSeq in them as well right? Atleast that is what I remember... for as much that I do remember.
So why not use the FileID and PaxSeq instead of trying to add a PaxID? Though possible offcourse, what is your reasoning behind it?
 
A

anishkgt

Registered User.
Local time
Today, 11:10
Joined
Nov 4, 2013
Messages
384
i was getting frustrated with creating the manual form. So was playing around with the primary keys. Well you are correct on the pax seq on different lines i won't be needing the PaxID
 
N

namliam

The Mailman - AWF VIP
Local time
Today, 10:10
Joined
Aug 11, 2003
Messages
11,695
Well using the array you can fix it.... a quick sample...
Code: 
Sub x()
    Dim PaxId(99) As Integer
    Dim PaxSeq As String
    ' Dummy fill Id's 
    For I = 0 To 99
        PaxId(I) = I * 10 + I
    Next I
    
    'get the paxid for the required paxseq
    PaxSeq = "02"
    Debug.Print PaxSeq, PaxId(PaxSeq)

    PaxSeq = "09" 
    Debug.Print PaxSeq, PaxId(PaxSeq)
    PaxSeq = "98" 
    Debug.Print PaxSeq, PaxId(PaxSeq)
    
End Sub
 
A

anishkgt

Registered User.
Local time
Today, 11:10
Joined
Nov 4, 2013
Messages
384
so how will this fix query for maual form work. Is it because of the way the tables are linked in a query ?
 
N

namliam

The Mailman - AWF VIP
Local time
Today, 10:10
Joined
Aug 11, 2003
Messages
11,695
Not sure if it will fix the query for manual work or not.

My suggestion is only a solution to something you are trying to do.

I must admitt I work around these issues with subforms, though I hardly ever (i.e. never) have these complex queries feeding a form.
 
N

namliam

The Mailman - AWF VIP
Local time
Today, 10:10
Joined
Aug 11, 2003
Messages
11,695
I hope you got your project finished, or you are enjoying a well desirved holiday :)
 
A

anishkgt

Registered User.
Local time
Today, 11:10
Joined
Nov 4, 2013
Messages
384
well not exactly a month, 10 days short :)

was wondering if i could create a log file daily with current date in C:\MIR\ of the text displayed in txtMIRActivity and create new file when the original file reaches 1MB.
 
A

anishkgt

Registered User.
Local time
Today, 11:10
Joined
Nov 4, 2013
Messages
384
tried that but when it came to
Code: 
 a.writeline (txtMIRActivity.Value)
it returned an error "Object variable or with block variable not set". Checked the text file and found just the value of last entry for txtMIRActivity.Vaue in it.
 
Last edited:
A

anishkgt

Registered User.
Local time
Today, 11:10
Joined
Nov 4, 2013
Messages
384
Thought i had it but i the excution stops at the line
Code: 
a.writeline (txtMIRActivity.Value & txtMIRActivity.Value)
with error "Object variable or with block variable not set" but the log gets written to the text file.
 
N

namliam

The Mailman - AWF VIP
Local time
Today, 10:10
Joined
Aug 11, 2003
Messages
11,695
what object doesnt exist, did you use a.close without opening it again?
 
A

anishkgt

Registered User.
Local time
Today, 11:10
Joined
Nov 4, 2013
Messages
384
tried this
Code: 
f MIRType = "Z" Then
            txtMIRActivity.Value = srcfile & " " & tkt & " " & "EMD Void" & vbNewLine & txtMIRActivity.Value
            Name SrcFolder & srcfile As DstFolder & "_EMDv_" & srcfile 'Move EMD Void files.
        End If
[COLOR=Red]a.writeline (txtMIRActivity.Value)[/COLOR]
        
NextFile:
        srcfile = Dir 'get next file
    Loop 'srcfile
    RSFiles.Close
    RSPax.Close
    RSPXFare.Close
    RSSector.Close
    RSFop.Close
    RSEx.Close
    [COLOR=Red]a.Close[/COLOR]
but this again leaves the text file completly empty
 
You must log in or register to reply here.

Similar threads

Gasman
Solved Exporting a form as text and then importing.?
Replies
15
Views
166
cheekybuddha
cheekybuddha
R
Linked Table - Excel - Blank Column A
2
Replies
21
Views
995
pdanes
P
Guus2005
Import must be read as-is into a table.
Replies
9
Views
531
Pat Hartman
P
C
exporting access table data to excel
Replies
10
Views
438
raziel3
raziel3
M
Importing data from excel with file name
Replies
15
Views
823
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom