On Insert or Update - index out of bound error

  • Thread starter Thread starter Gina
  • Start date Start date
G

Gina

Hi.

I have 150 records text, text, currency exported to text file
now on importing them after about 100 are inserted/updated I
get runtime error 9 'index out of bound '
what does that mean ???

if I reduce the number of records ... fine but that can't be it !!

any idea of what I am doing wrong would be highly appreciated

Thanks,
Gina
 
Gina said:
Hi.

I have 150 records text, text, currency exported to text file
now on importing them after about 100 are inserted/updated I
get runtime error 9 'index out of bound '
what does that mean ???

if I reduce the number of records ... fine but that can't be it !!

any idea of what I am doing wrong would be highly appreciated

Thanks,
Gina
Click to expand...

Are you importing into an array? That message means a value being used
as the subscript into an array is not within the bounds of the array.
Post your code.
 
Hi Dirk.

Thanks ....

table :
Material - text(PK)
Use - text (not indexed, not required, can contain zero length)
Price - currency (not required, can contain zero length)
____
Public Sub sImportData(strTableName As String)
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim mat, use, price As String
Dim Line
Dim arWords

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM " & strTableName)
Open CurrentProject.Path & "\" & strTableName & ".txt" For Input As #1

Do While Not EOF(1)
Line Input #1, Line
arWords = Split(Line, "|")
mat = arWords(0)
use = arWords(1)
price = arWords(2)
If IsNull(price) Then price = 0 'no quotes for a currency field!
rst.FindFirst "Material = '" & mat & "'"
If rst.NoMatch Then
rst.AddNew
rst.Fields("Material") = mat
Else
rst.Edit
End If
rst.Fields("Material") = mat
rst.Fields("Use") = use
Debug.Print use
rst.Fields("Price") = price
rst.Update
Loop
rst.Close
Close #1

Set rst = Nothing
Set db = Nothing

End Sub
____


Gina
 
Gina said:
Hi Dirk.

Thanks ....

table :
Material - text(PK)
Use - text (not indexed, not required, can contain zero length)
Price - currency (not required, can contain zero length)
____
Public Sub sImportData(strTableName As String)
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim mat, use, price As String
Dim Line
Dim arWords

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM " & strTableName)
Open CurrentProject.Path & "\" & strTableName & ".txt" For Input
As #1

Do While Not EOF(1)
Line Input #1, Line
arWords = Split(Line, "|")
mat = arWords(0)
use = arWords(1)
price = arWords(2)
If IsNull(price) Then price = 0 'no quotes for a currency
field! rst.FindFirst "Material = '" & mat & "'"
If rst.NoMatch Then
rst.AddNew
rst.Fields("Material") = mat
Else
rst.Edit
End If
rst.Fields("Material") = mat
rst.Fields("Use") = use
Debug.Print use
rst.Fields("Price") = price
rst.Update
Loop
rst.Close
Close #1

Set rst = Nothing
Set db = Nothing

End Sub
____


Gina
Click to expand...

From the looks of it, I'd say that you have read in a line that doesn't
contain two instances of the "|" character, so your array arWords, as
returned from the Split function, doesn't have three elements. Debug
the code and check the value of the variable Line when the error is
raised. You may need to add logic to skip certain lines, or to check
the upper bound of arWords, as returned by the UBound() function.
 
Dirk.... I checked it in the source text file .... which I created earlier
From the looks of it, I'd say that you have read in a line that doesn't
contain two instances of the "|" character, so your array arWords, as
returned from the Split function, doesn't have three elements.
Click to expand...

It was exactly the case missing "|"

ok .... I rewrote the sub with UBound(arWords) .... but it couldn't handle
it either
so better avoid this missing "|" in the first place ... during the export
all works great now!!! :))

that was a long day!!!
big !! thanks,

Gina
 
Back
Top