I try again

[sms23]

No response to my earliar post. Perhaps not clear.I will try again.

have a table with called Info with following fields

Code Weight Date Ship #

Now we get this text file fixed width with the following information

Code Weight date

This is generated by weighing boxes on scale, this is done everyday and have 500 entires. Code is the destination its going . THis is what it looks like

GVA 20 11/11/2004
GVA 15 11/11/2004
LIM 1 11/11/2004
DEL .5 11/11/2004
Del 2 11/11/2004
Del 5 11/11/2004

Need to import this in table. Which I can. but also need to add the ship #
Meaning that if two GVA were imported in the table then Ship # would be 1 and 2. Del would get assigned 1,2,3 respectivly. The next day same process and if we imported 4 GVA records then they should get ship # 3,4,5,6 .

Thanks

 

[WayneRyan]

sms,

YourTable:
==========
ShipNumber - Generated
Code - Field 1
Weight - Field 2
ShipDate - Field 3

Dim dbs As DAO.Database
Dim rst As DAO.RecordSet

Dim strBuffer As String

Dim varInput As Variant

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("YourTable")

Open "C:\SomeFile.txt" For Input As #1
Line Input #1, strBuffer
While Not EOF(1)
   rst.AddNew
   rst!ShipNumber = Nz(DMax("[Code]", "YourTable", "[Code] = '" & varInput(0) & "'"), 0) + 1
   rst!Code = varInput(0)
   rst!Weight = varInput(1)
   rst!ShipDate = varInput(2)
   rst.Update
   Line Input #1, strBuffer
   varInput = Split(strBuffer, " ")
   Wend
Close #1

Wayne

 

[sms23]

Thanks a million for taking time and replying. I did as you said but was getting this error message and AS DAO.Database was being highlighted. Attached is the file. I used winrar and then zipped it.

Regards

 

[Mile-O]

Without looking you probably need to set a reference to DAO. Search here for references - it is almost a daily question.

 

[sms23]

I am clueless as far a programing is concerned.

 

[WayneRyan]

sms,

Get the code in Design View.

Tools --> References

Then make sure that Microsoft DAO is checked and use the arrow to promote
it as high as you can.

Wayne

 

[sms23]

Did as you said. Now getting Error 13 .Some type mismatch and this line is highlighted

rst!ShipNumber = Nz(DMax("

", "Pouch", "[Code] = '" & varInput(0)       & "'"), 0) + 1

Appreciate the help.

Regards

 

[WayneRyan]

sms,

Can you:

Tools --> Database Utilities --> Compact/Repair

Then Open a ZIP file

Then insert your DB and a small sample import file

Then attach

It would make it a lot easier,
Wayne

 

[sms23]

attached as requested

cheers

 

[WayneRyan]

sms,

Will look at it later today.

Wayne

 

[WayneRyan]

sms,

See attached.

Wayne

 

[24sharon]

Great

and if u dont have refrences (MISSING) like me, than U can write this:

Dim dbs 'As DAO.Database
Dim rst ' As DAO.Recordset

Dim strBuffer As String

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Pouch")

Open "C:\pouch.txt" For Input As #1
Line Input #1, strBuffer

While Not EOF(1)
   rst.AddNew
   rst!Code = Strings.Trim(Strings.Mid(strBuffer, 1, 6))
   rst!ShipNumber = Nz(DMax("[ShipNumber]", "Pouch", "[Code] = '" & rst!Code & "'"), 0) + 1
   rst!Weight = Strings.Trim(Strings.Mid(strBuffer, 7, 6))
   rst!ShipTime = Strings.Trim(Strings.Mid(strBuffer, 14, 8))
   rst!ShipDate = Strings.Trim(Strings.Mid(strBuffer, 23, 10))
   rst.Update
   Line Input #1, strBuffer
   Wend
Close #1

 

[sms23]

Wayne,

Thanks a million. And thanks to others too. Just 2 quick question if we import around 500 records a day , would the process get slow say perhaps after 6 months or a year. And would it help if I move the tables to an SQL server if it does get slow.

Regards

 

[WayneRyan]

sms,

180,000 records a year ... You will definitely slow down! Moving to SQL
Server would help, but the import will definitely be a slow process as
the records accumulate.

I'd use Access in the interim, and experiment with MSDE and/or SQL Server
in parallel.

Wayne

 

[sms23]

thanks it works. but I have another problem. We ship the bags using UPS, TNT and other couriers.Meaning we group all the bags going to one destination and ship them using one of the assinged couriers. for example we imported 5 GVA and 3 LON in the table and they were all assigned ShipNumbers. Now all the GVAs need to be attached an airway bill number and LON also asigned its airwaybill number. Next day some process. Import and need to assign them new airwaybill numbers. All the bags for one destination will be listed on on Airwaybill.

Regards

 

[sms23]

Even a bigger problem
I changed the ShipDate from Text to Date and it worked fine for dates like
11-19-2004 and 11-26-2004 but today 12-01-2004 showed up as 01-12-2004
Meaning month became date and date became month.