Read a file and import its data into a table

[namliam]

well sorry for not being online 24/7, glad you figured it out though

First thing to do is that the query is working OK, making a form or report on a query is exactly the same as basing them on a table.... as long as the query works.... this also means making sure PK are setup on the underlying tables

 

[anishkgt]

Ummmmm 24/7 ? sorry kinda new to certain stuffs. Well yes the query is working fine after few corrections in the code and linking the PK

Need to get the manual entry form correct.

 

[anishkgt]

leaving the manual form aside, for some reason BaseFare when 0 returns an error as "type mismatch" but the code seems to correct to me. :banghead:

If Mid(TextLine, 9, 12) = "" Then
                    Basefare = 0
                Else
                    Basefare = CDbl(Mid(TextLine, 9, 12))
                End If

 

[namliam]

what do you dim Basefare as?

 

[anishkgt]

Basefare as double as for all

 

[namliam]

at what line is the error occuring?

If its the cdbl line, then the value you are extracting isnt a number value

 

[anishkgt]

Yup the error was

If [COLOR=Red]Trim[/COLOR](Mid(TextLine, 9, 12)) = "" Then
                    Basefare = 0
                    Else
                    Basefare = CDbl(Mid(TextLine, 9, 12))
                    End If

added the part in red and problemo solved

 

[anishkgt]

need to validate a date from a table.

DLookUp("[LicenseID]","tblLicense","[LicenseExpiry]=<Date()")

was looking for something like the above. How can i correct that ? Greater than today then message box and else nothing

 

[namliam]

Trim was going to be my next suggestion, probably should have added that in my previous post too

Not sure if you can use Date() inside the DLookup, this is the way I would do it

DLookUp("[LicenseID]","tblLicense","[LicenseExpiry]<= #" & format(Date(), "MM/DD/YYYY") & "#")

 

[anishkgt]

Thanks that worked.

 

[anishkgt]

For manual entry, do i need to create another table with the relevant fields ? Tried using the query but that only displayed existing records, could not add new records.

 

[anishkgt]

Attached the screenshot of the linked table in a query, not sure i was doing the rite way.

 

[namliam]

if you add the keys of each of the tables, you should be able to add data to the query

 

[anishkgt]

Try that too, did not work. The add new record in the query remains disabled.

 

[anishkgt]

stuck up with the entry form. , Anybody !

 

[namliam]

Worse comes to worse you can make subforms that each fill a table with the references in it... This also would make data entry a little more friendly I think

 

[anishkgt]

Hmmm thats a little complicate for the end user. Y aren't the quiries working even after linking the tables within it.

 

[namliam]

Not exacly sure, to try and get that working, try only using one table then two see where you get stuck... I hardly ever use queries with so many joins in a form....

Rather use subforms, so that your user only has to input "Filename" one time instead of 10 times

 

[anishkgt]

Well to check one table at a time, the tblpax, tblsector and tblpaxfare has no problem when linking for a form.

Filename is omitted as it cannot be manually entered for ticket data. Similarly a few fields like paxseg will also benomitted. So subforms is the best option ist ?

 

[namliam]

Considering the fileID is part of your key, that would be a problem.

You can offcourse (maybe in code) abuse the file level on manual input to atleast administer its manual and perhaps add a date/time as well to pinpoint them as well?