Numeric Value Overflow - HELP???

[Guest]

Hi,

This is my code that I am using to import a series of text files. When I
click on my command button to import them it starts to work but then pops up
a message saying "Numeric Value Overflow" what does this mean??

My code has worked several times before but now doesnt- wierd!!

This is a sample of my code

Private Sub cmdImport_Click()
Dim MyDatabase As Database
Dim MyTableDef As TableDef
Dim datCurrentDate As Date
Dim db As Database
Dim MyFile, MyPath, MyName
Dim X As Integer
Dim strTable As String
Dim sQRY As String
Dim varRuleType As Variant

On Error GoTo Import_Err

If MsgBox("Warning!" & vbCrLf & "Clicking Yes will delete table information
and replace with new data. Are you sure you want to import?", _
vbYesNo + vbCritical, _
"Housing Services Group Reporting Tool - Confirm Import") <> vbNo Then

'Check for the existence of each file before attempting import process.
'If any not found, then prompt user with details of which file is missing.
For X = 1 To 6
strTable = DLookup("Location", "tblExtractLocations", "Table = " & X)
MyFile = Dir(strTable)
If MyFile = "" Then
MsgBox "File '" & strTable & "' not found. " & _
"Import process aborted. No data has been imported.", _
vbExclamation, "Housing Services Group Reporting Tool - Import
Failure"
Exit Sub
End If
Next X

DoCmd.SetWarnings False

'Delete existing tblTemp; trap error if the table does not exist.
If ObjectExists("Tables", "tblTemp") = True Then
DoCmd.DeleteObject acTable, "tblTemp"
End If

Set db = CurrentDb
datCurrentDate = Format$(Now(), "dd/mm/yyyy hh:nn:ss")

'Clear tblCustomer, tblCustomerTEMP
db.Execute "DELETE * FROM tblCustomer;"
db.Execute "DELETE * FROM tblCustomerTEMP;"

'Import Customer to tblCustomerTemp
strTable = DLookup("[Location]", "tblExtractLocations", "

= 1")
DoCmd.TransferText acImportDelim, "CustomerSPEC", "tblCustomerTEMP",
strTable, False, ""
db.Execute "UPDATE tblCustomerTEMP " & _
"SET [Import Date] = #" & datCurrentDate & "#"

'Append to tblCustomer
DoCmd.OpenQuery "qappCustomer", acNormal, acEdit

Please help urgently,

Thanks,
Jez.

 

[Andi Mayer]

Hi,

This is my code that I am using to import a series of text files. When I
click on my command button to import them it starts to work but then pops up
a message saying "Numeric Value Overflow" what does this mean??

My code has worked several times before but now doesnt- wierd!!

This is a sample of my code

Private Sub cmdImport_Click()
Dim MyDatabase As Database
Dim MyTableDef As TableDef
Dim datCurrentDate As Date
Dim db As Database
Dim MyFile, MyPath, MyName
Dim X As Integer
Dim strTable As String
Dim sQRY As String
Dim varRuleType As Variant

On Error GoTo Import_Err

comment the On error
run it
look where this error happens and look for an integer which gets
filled with a long or similar error