subscript out of range error

[Tom]

I am receiving the error messgae "Subscript out of
range". I know why I'm recieving it, but can't figure out
how to fix it. Here is my code:

' To read the Array ...
Dim i As Integer, a$
Dim strSQL As String
Dim strInjuryType1 As String
Dim strInjuryType2 As String
Dim strInjuryType3 As String
Dim strInjuryType4 As String
Dim strInjuryType5 As String
Dim strInjuryType6 As String
Dim strInjuryType7 As String
Dim strInjuryType8 As String
Dim strInjuryType9 As String
Dim strInjuryType10 As String
Dim strInjuryType11 As String

For i = 0 To UBound(ParseString)
a$ = a$ & ParseString(i) & vbNewLine
Next i
MsgBox "Your Array Contains:" & vbNewLine & vbNewLine
& a$

strInjuryType1 = ParseString(0)
strInjuryType2 = ParseString(1)
strInjuryType3 = ParseString(2)
strInjuryType4 = ParseString(3)
strInjuryType5 = ParseString(4)
strInjuryType6 = ParseString(5)
strInjuryType7 = ParseString(6)
strInjuryType8 = ParseString(7)
strInjuryType9 = ParseString(8)
strInjuryType10 = ParseString(9)
strInjuryType11 = ParseString(10)


strSQL = "INSERT INTO tblInjuriesperClaimECA
([ClaimNumber],[InjuryType1],[InjuryType2],[InjuryType3],
[InjuryTyp4],[InjuryType5],[InjuryType6],[InjuryType7],
[InjuryType8],[InjuryType9],[InjuryType10])VALUES ('" &
strClaimNumber & "','" & strInjuryType1 & "','" &
strInjuryType2 & "','" & strInjuryType2 & "','" &
strInjuryType2 & "','" & strInjuryType2 & "','" &
strInjuryType2 & "','" & strInjuryType2 & "','" &
strInjuryType3 & "','" & strInjuryType4 & "','" &
strInjuryType5 & "','" & strInjuryType6 & "','" &
strInjuryType7 & "','" & strInjuryType8 & "','" &
strInjuryType9 & "','" & strInjuryType10 & "','" &
strInjuryType11 & "');"

CurrentDb.Execute strSQL


As it reads the array, if there is no value, it doesn't
know what to do. I need to add a loop so that if it sees
a null value, it repeats the code again. In the above
example, it reads the array and since the array only has
three values, it gives me the error when it tries to
declare the fourth value. There can be up to 10 values in
the array.

Thank you so much for your help.

 

[Gerald Stanley]

Try something like

strSQL = "INSERT INTO tblInjuriesperClaimECA
([ClaimNumber],[InjuryType1],[InjuryType2],[InjuryType3],
[InjuryTyp4],[InjuryType5],[InjuryType6],[InjuryType7],
[InjuryType8],[InjuryType9],[InjuryType10])VALUES ("

For i = 0 to 10
If i > Ubound(ParseString) then
strSQL = strSQL & "Null,"
Else
strSQL = strSQL & "'" & ParseString(i) & "',"
End If
Next

strSQL = Left(strSQL , Len(strSQL) - 1) & ");"
CurrentDb.Execute strSQL

The code is pure untested airccode but should point you in
the right direction.
Hope This Helps
Gerald Stanley MCSD

 

[Tim Ferguson]

I am receiving the error message "Subscript out of
range". I know why I'm receiving it, but can't figure out
how to fix it.

Perhaps it might help if you _did_ explain why you are receiving it..?

strInjuryType1 = ParseString(0)
strInjuryType2 = ParseString(1)

etc, etc: what is the point of transferring these strings?

strSQL = "INSERT INTO tblInjuriesperClaimECA " & _
"( [ClaimNumber], " & _
[InjuryType1], " & _
[InjuryType2], " & _
[InjuryType3], " & _
[InjuryType4], " & _
[InjuryType5], " & _
[InjuryType6], " & _
[InjuryType7], " & _
[InjuryType8], " & _
[InjuryType9], " & _
[InjuryType10] " & _
") " & _

OK: big problem here, major design rethink needed...

"VALUES ('" & strClaimNumber & "','" & _
strInjuryType1 & "','" & _
strInjuryType2 & "','" & _
strInjuryType2 & "','" & _
strInjuryType2 & "','" & _
strInjuryType2 & "','" & _
strInjuryType2 & "','" & _
strInjuryType2 & "','" & _
strInjuryType3 & "','" & _
strInjuryType4 & "','" & _
strInjuryType5 & "','" & _
strInjuryType6 & "','" & _
strInjuryType7 & "','" & _
strInjuryType8 & "','" & _
strInjuryType9 & "','" & _
strInjuryType10 & "','" & _
strInjuryType11 & "');"

.... and there is a different number of values from the number of fields
specified...

As it reads the array, if there is no value, it doesn't
know what to do.

You haven't said what type of array it is. If it's an array of variants,
then they can be NULL, so you can use NZ(ParseString(i),0), but if it's an
array of numbers then they can't be null. If you don't know how big the
array is, then you can use LBound() and UBound() to find out.

If it's the latter, then the simplest approach would be just to get the db
design right.

Hope that helps


Tim F