Data Types

[aziz rasul]

I am a new user to VBA. I have written code that more or less works. The only problem that I have is that a variable called Count2 which is a Long data type fails when it reaches a value of over 40,000.

The value of Count2 simply holds the RecordCount of a table. This table grows in size when the code is run (as records are appended into the table from several files) and the value of Count2 increases during the process. A error 6 appears saying "overflow". The Help button mentions changing the data type.

If I change the data type to Double or Variant or Single I get the same problem.

Can anyone tell me what I'm doing wrong.

Many Thanks in advance.

 

[Abby N]

Hi Aziz. A long integer can hold numbers ranging from + or - 2,147,483,647. So, I'm guessing there is something else causing the overflow. If you don't mind, could you post the offending code?

~Abby

 

[aziz rasul]

Abby here's the code: -

Public Function Process_TXT_Files()

Dim MyDB As Database
Dim rstTXTFiles As Recordset
Dim Count1 As Long
Dim Count2 As Long
Dim intI As Integer
Dim strCriteria As String

Set MyDB = CurrentDb

strImportDir = "c:\collect\"
strFileExtension = "*.txt"

strFileName = Dir(strImportDir + strFileExtension)

If Len(strFileName) > 0 Then

strFileName = Dir(strImportDir + strFileExtension)

Do
strFileFullPath = strImportDir + strFileName

Set rstTXTFiles = MyDB.OpenRecordset("tblTXTFiles", dbOpenDynaset)
If rstTXTFiles.RecordCount > 0 Then
rstTXTFiles.MoveLast
End If

'Counts the number of records in "tblTXTFiles"
Count1 = rstTXTFiles.RecordCount
rstTXTFiles.Close

'Imports and appends the contents of the TXT file into "tblTXTFiles"
DoCmd.TransferText acImportFixed, "spc_IE_TXTFiles", "tblTXTFiles", strFileFullPath

Set rstTXTFiles = MyDB.OpenRecordset("tblTXTFiles", dbOpenDynaset)
If rstTXTFiles.RecordCount > 0 Then
rstTXTFiles.MoveLast
End If

Count2 = rstTXTFiles.RecordCount
rstTXTFiles.MoveFirst
strCriteria = "[NameofTXTFile] = Null"

'THE PROBLEM OCCURS HERE WHEN Count2 BECOMES TOO LARGE

For intI = Count1 + 1 To Count2
rstTXTFiles.FindFirst strCriteria
rstTXTFiles.Edit
rstTXTFiles("DateofImport") = Now
rstTXTFiles("NameofTXTFile") = strFileName
rstTXTFiles.Update
rstTXTFiles.MoveNext
Next intI

rstTXTFiles.Close

Loop Until Len(strFileName) = 0
Else
MsgBox "No TXT files are available"
Exit Function
End If

 

[Abby N]

My best guess is intI is causing the overflow. Try setting intI as Long.

~Abby

 

[aziz rasul]

Thanks Abby. I feel like kicking muself for not spotting that.

Aziz