Error 9. Subscript out of range problem

[waely]

hi,
I have a program that has been running for few years and now it started to give this error. the program is an executable; however, I have access to its source code. when troubleshooting I got to this section when the error occures.

-------------------------------
Public Sub InsertLink(varLinkField As Variant)
Dim I, j As Integer
Dim strR As String
strInsertField = ""
strInsertVal = ""

For j = 1 To UBound(varLinkField, 2)
For I = 1 To lngNumCols
If fldName(I) = varLinkField(0, j) Then
Exit For
End If
Next I
strInsertField = strInsertField & ", " & varLinkField(1, j)

//the subscript out of range is with strData((I, lngRowNumber)
strInsertVal = strInsertVal & ", " & fldQuote(I) & strData(I, lngRowNumber) & fldQuote(I)
Next j
End Sub
----------------------------------------------

I'm not sure if the problem with the program itself, which I double, or with the database ....

please help,
Thanks

 

[PHV]

If by chance fldName(I) never = varLinkField(0, j) then I=lngNumCols+1 when you get the error 9.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rmikesmith]

Which brings up an important point: It is a bad practice to reference a For..Next loop counter variable outside the loop. A better construct would be to declare a separate variable and set it equal to I inside the loop. Example:

For I = 1 To lngNumCols
  If fldName(I) = varLinkField(0, j) Then
    Index = I
    Exit For
  End If
Next I

where Index is declared as Integer.


Regards,
Mike

 

[waely]

thanks for the input but it didn't work. could it be something with the database? the program has been working for years and just recently started giving this error message.

thanks

 

[waely]

Any ideas.... thanks

 

[PHV]

Where are varLinkField, lngNumCols, fldName, fldQuote, strData and lngRowNumber coming from ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886