Read a file and import its data into a table (1 Viewer)
- Thread starter anishkgt
- Start date
Ah your tkt is not numeric?
It REALLY helps if you provide proper examples in your example here you gave a proper number. If you had given something like 123ASDK, I would have recognized that immediately
It REALLY helps if you provide proper examples in your example here you gave a proper number. If you had given something like 123ASDK, I would have recognized that immediately
Well i was confused at to why your code was wrong. The ticket number is actually only numbers. But in the table the data type is shorttext. So i guess maybe thats why all the confusion. For everything seems to be ok. Had to use the goto statement for omitting some steps.
Tried changing the changing the data type of tkt, whihc holds the ticket number to ineger in vba but gives an error "Overflow", So unless that is done can't change data type in the database
variable tkt is
so halting on the change. The code for 'goto is
variable tkt is
Code:
tkt = ALC & Trim(Mid(TextLine, 49, 10))
Code:
If Len(Pax) = 0 Then ' no passengers
Name SrcFolder & srcfile As ErrorFolder & srcfile
txtMIRActivity.Value = txtMIRActivity.Value & _
"FILE ERROR! " & " " & srcfile & "-" & _
"No Passenger(s)" & vbNewLine
GoTo Skip
End If
..............CODE-------------------
End If
txtMIRActivity.Value = txtMIRActivity.Value & srcfile & " " & tktDisp & " " & "Processed" & vbNewLine
Name SrcFolder & srcfile As DstFolder & srcfile
Skip:
srcfile = Dir 'get nextfilewhat happened to my code from
No need for goto's?
Instead of integer try using a long integer or even double.
Integer is a pretty small field.
Code:
Close #1 ' Close file
If len(pax) = 0 then
' no passengers
name srcfolder & srcfile as errorfolder & srcfile
txtMIRActivity.Value = Trim(txtMIRActivity.Value & vbNewLine & _
"ERROR " & srcfile & " " & "[" & Now() & "]" & vbnewline & _
"No Passengers" )
else
rs.addnew
rs!... 'fill all the fields
RS.Update 'update the table
Name SrcFolder & srcfile As DstFolder & srcfile
endif
srcfile = Dir 'get next file
txtMIRActivity.Value = Trim(txtMIRActivity.Value & vbNewLine & srcfile & " " & "[" & Now() & "]")No need for goto's?
Instead of integer try using a long integer or even double.
Integer is a pretty small field.
tried the above and i get an error "Data type converstion error"
on the db the data type is Long Integer and in vba tkt is decalerd as Long. Don't what this is error is related to ? The tkt value is null so i guess its something to do with handling null values as for Files the A02 may not be there and the tkt value goes null.
Code:
RSTkt![TicketNumber] = tktok ! i've managed clear all the errors and everything seems to be working fine now. Inorder to overcome those errors i had to use the goto statements. here is my code so far
Code:
Open SrcFolder & srcfile For Input As #1 'Open specified file
Pax = Empty 'clear variable for next file
tkt = Empty 'clear variable for next file
Do While Not EOF(1)
Line Input #1, TextLine
If Left(TextLine, 2) = "T5" Then
.......................CODE follows.....................
If Left(TextLine, 3) = "A12" Then 'Reads Phone field
.......................CODE follows.....................
End If
Loop ' EOF
Close #1 ' Close file
If DCount("TicketNoID", "tblticketNumber", "[TicketNumber]=" & tkt) Then 'Find ticket # Duplicate
Name SrcFolder & srcfile As DupFolder & srcfile
txtMIRActivity.Value = txtMIRActivity.Value & _
srcfile & " " & tkt & " " & _
"DUPLICATE TICKET " & vbNewLine
GoTo NextFile ' quits further processing as ticket number already exists so moves to next file
End If
If Len(Pax) = 0 Then ' no passengers
Name SrcFolder & srcfile As ErrorFolder & srcfile
txtMIRActivity.Value = txtMIRActivity.Value & _
srcfile & " " & _
"No Passenger(s)" & vbNewLine
GoTo NextFile ' quits further processing as no passenger info is available in current file so moves to next file
Else
RSTkt.AddNew
RSTkt![FileName] = srcfile
RSTkt![TicketNumber] = tkt
tkt = Empty
RSTkt.Update 'update tblTicketNumber
End If
RS.AddNew
RS![FileName] = srcfile
RS![DateAndTime] = Now()
RS![IATACode] = IATACode
RS![I/OGTIDS] = IOGTID
RS![MIRCreationDate] = MIRDate
RS![IssuingAirline] = IssueAirline
RS![BTPcc] = BkgTkgPCC
RS![PNR] = PNR
RS![BS/SBI/TS/DutyCode] = BkgSignOnTkgSignOn
RS![PNRCreationDate] = PNRDate
RS![Passengers] = passenger
RS![sector] = AirSector
RS![Tax's] = Tax
RS![EquivalentAmount] = EQAmt
RS![BaseFare] = BaseFare
RS![TotalFare] = TotalFare
RS![FOP] = FOP
RS![ActuallyCollected] = ActualAMTCol
RS![PhoneField] = phoneFieldCont
RS.Update 'update the tblMIR
'Pax = Empty
'tkt = Empty
txtMIRActivity.Value = txtMIRActivity.Value & srcfile & " " & tktDisp & " " & "Processed" & vbNewLine
Name SrcFolder & srcfile As DstFolder & srcfile
NextFile:
srcfile = Dir 'get next file
Loop 'srcfile
RS.Close ' Close the table
txtMIRActivity.Value = txtMIRActivity.Value & "Completed" & " " & "[" & Now() & "]" & vbNewLine
Set RS = Nothing ' Clear RS
Set RSTkt = Nothing ' clear RSTkt
DoCmd.Hourglass False
Err:
If Error >= 0 Then
Else
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End If
End SubYou cannot, which is why a couple of pages back when you uploaded your DB I suggested you change
txtMIRActivity.Value = txtMIRActivity.Value & "Completed" & " " & "[" & Now() & "]" & vbNewLine
to
txtMIRActivity.Value = "Completed" & " " & "[" & Now() & "]" & vbNewLine & txtMIRActivity.Value
So that new files are added on top instead of at the bottom...
May also be an idea you make a list of labels that you fill with the filenames, that way in code you can make it scroll like you intent too.
