Solved

runtime error 9 subscript out of range

Posted on 2007-11-26
4
565 Views
Last Modified: 2008-02-01
I get this error "runtime error 9 subscript out of range"  when I run my vb6 code.  I am accessing an sql database and it is returning an error at "Com(21) = rec.RecordCount"

Here is some of the surrounding code:

'---------------------------Youth Female Participant 19 to 30-----------

esql = "select frmRegistrationCSWText2,frmRegistrationCSWText4 From frmRegistrationCSW Where frmRegistrationCSWText6 >= 19 And frmRegistrationCSWText6 <= 30 And frmRegistrationCSWCheck2 = 1 And frmRegistrationCSWCheck3 = 1 And frmRegistrationCSWCheck10 =1 And frmRegistrationCSWText15  >= convert( datetime, " & ravi & BeginDate & ravi & " , 101 )And frmRegistrationCSWText15  <= convert( datetime, " & ravi & EndDate & ravi & " , 101 ) "

If Not Combo1.Text = "-All-" Then
esql = esql & " And frmRegistrationCSWText1 = " & ravi & Program & ravi
End If

rec.Open (esql), conn, adOpenStatic, adLockOptimistic

Com(21) = rec.RecordCount


rec.Close


'---------------------------Youth Male Participant 31 to 35-----------

esql = "select frmRegistrationCSWText2,frmRegistrationCSWText4 From frmRegistrationCSW Where frmRegistrationCSWText6 >= 31 And frmRegistrationCSWText6 <= 35 And frmRegistrationCSWCheck1 = 1 And frmRegistrationCSWCheck3 = 1 And frmRegistrationCSWCheck10 =1 And frmRegistrationCSWText15  >= convert( datetime, " & ravi & BeginDate & ravi & " , 101 )And frmRegistrationCSWText15  <= convert( datetime, " & ravi & EndDate & ravi & " , 101 ) "

If Not Combo1.Text = "-All-" Then
esql = esql & " And frmRegistrationCSWText1 = " & ravi & Program & ravi
End If

rec.Open (esql), conn, adOpenStatic, adLockOptimistic

Com(22) = rec.RecordCount


rec.Close
0
Comment
Question by:al4629740
  • 2
4 Comments
 
LVL 26

Accepted Solution

by:
EDDYKT earned 500 total points
ID: 20351201
what do you define Com as?
0
 

Author Comment

by:al4629740
ID: 20351306
Found the problem while I was in the bathroom.  I didn't declare a large enough Com scope.  It was Com (1 to 20) and needed to increase it.  Thanks.
0
 
LVL 2

Expert Comment

by:andrewneely
ID: 20359284
The best approach I've found with arrays is to use dynamic array bounds.  This way, you code gracefully handles larger subscripts without having to guess at the larget subscript.

I declare a counter variable, increment it, and to a redim preserve.

The following code reads a text file into an array of strings.
dim inputBufferString() as string
'the -1 is important so that 0 is our first subscript
dim bufferCountInteger as integer=-1
if io.file.exists("myfile.txt") then
    dim myStreamReader as new io.streamreader("myfile.txt")
    do while mystreamreader.peek <> -1
        bufferCountInteger +=1
        redim preserve inputBufferString (bufferCountInteger)
        inputBufferString(bufferCountInteger)=myStreamReader.readline
    loop
myStreamReader.close
'now the contents of the file is stored in our string array.
0
 

Author Comment

by:al4629740
ID: 20360251
Thank you for that.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question