If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"Numeric Field Overflow"
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, Fini****emID, 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_He 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 |
Thread Tools | |
Display Modes | |
|
|