"Numeric Field Overflow"

[Don]

I am importing data from a txt file into table using a import specification
"OpenOrders Import Specification1". When I import using the specification and
the import wizard, the import works without error. Using the code below which
is behind a button on my form I get this error, "Numeric Field Overflow". The
following is the code behind the form button:

Private Sub Command4_Click()
' Dim tells the database about a variable such as dbs, wrk and
' strSQL and the type of data the variable it will store.
Dim dbs As DAO.Database
' Dim wrk As DAO.Workspace
Dim strSQL As String, strQuery As String, strMessage As String

On Error GoTo Err_Handler

Set dbs = CurrentDb
strMessage = "Error deleting from OpenOrders"
dbs.Execute "Delete * from OpenOrders;", dbFailOnError

' 1. Imports and transferes OpenOrders.txt to table OpenOrdersImport
if
' OpenOrders.txt is present in specified directory.

strMessage = "Error importing from OpenOrders"
If (Len(Dir("A:\OpenOrders.txt"))) Then
DoCmd.TransferText acImportFixed, "OpenOrders Import
Specification1", "OpenOrders", "A:\OpenOrders.txt", False, ""
End If


' 2 Delete report headers and footers from OpenOrder Table
strMessage = "Error cleaning OpenOrders"
dbs.Execute "DELETE * FROM OpenOrders WHERE (OrderID is null) or
(OrderID =0);", dbFailOnError

' 3. Append new order Numbers to tblOrders table"
strMessage = "Error appending new orders"
dbs.Execute "INSERT INTO tblOrders ( OrderID, CustomerId ) SELECT
OpenOrders.OrderID, OpenOrders.CustomerID FROM OpenOrders GROUP BY
OpenOrders.OrderID, OpenOrders.CustomerID;" ', dbFailOnError

' 4. Append any new finished items from Open Orders to tblFinishedItems"
strMessage = "Error appending new finished items from open orders"
dbs.Execute "INSERT INTO tblFinishedItems ( FinishedItemID,
Discription ) SELECT OpenOrders.ItemNumber, OpenOrders.ItemDescription FROM
tblFinishedItems RIGHT JOIN OpenOrders ON tblFinishedItems.FinishedItemID =
OpenOrders.ItemNumber WHERE tblFinishedItems.FinishedItemID Is Null GROUP BY
OpenOrders.ItemNumber, OpenOrders.ItemDescription;", dbFailOnError

' 5. Append new orderlines from Open Orders (complete record) to
tblOrderLines"
strMessage = "Error appending new order lines"
dbs.Execute "INSERT INTO tblOrderLines ( OrderID, LineID,
FinishItemID, QtyOrderd, RequestDate, Status ) SELECT OpenOrders.OrderID,
OpenOrders.LineID, OpenOrders.ItemNumber, OpenOrders.QtyOrdered,
OpenOrders.RequestDate, 1 AS Expr1 FROM OpenOrders LEFT JOIN tblOrderLines ON
(OpenOrders.LineID = tblOrderLines.LineID) AND (OpenOrders.OrderID =
tblOrderLines.OrderID) WHERE (((tblOrderLines.OrderID) Is Null));",
dbFailOnError

' 6. Update QtyShipped from OpenOrders to tblOrderLines QtyShipped
strMessage = "Error updating tblOrderlines QtyShipped from OpenOrders"
dbs.Execute "UPDATE OpenOrders INNER JOIN tblOrderLines ON
(OpenOrders.OrderID = tblOrderLines.OrderID) AND (OpenOrders.LineID =
tblOrderLines.LineID) SET tblOrderLines.QtyShipped =
[OpenOrders].[QtyShipped];", dbFailOnError




' Select OpenOrders_ImportErrors table
On Error Resume Next
MsgBox "Import Complete", vbInformation, "Success"
Exit_Here:
Set dbs = Nothing
Exit Sub

Err_Handler:

strMessage = Error & vbNewLine & vbNewLine & strMessage & _
vbNewLine & vbNewLine & "Please correct the error and try again."
MsgBox strMessage, vbExclamation, "Error"

Resume Exit_Here

End Sub


I checked the import specification field criteria and the table criteria and
both look to be the same. Example Text "255" or Long Integer. What could be
the problem?

Thanks,
Dennis