Array and Err 9 Subscript out of bounds

[Sideman]

I've searched everywhere and can't find an answer, so...either I'm just braindead today, or don't know as much as I thought I did (probably this one).

VB6 SP6, Access 2000.

In the general section, I set Option Base 1 and dim a multi-dim array, LineItems()As Variant. In a Sub, I ReDim it to (12,11) and assign values to it from an .mdb (as I've done a hundred times). Then in another Sub I assign the array data, via a For/Next loop, to some text boxes for display.

That's where the Err 9 occurs. On the first pass thru the loop, index 1, data from the first array position goes into txtBox(1) just fine. The next item in the loop does not....and the index gets changed to 13, causing the Err 9. Note, the first iteration thru the loop never finishes...the index is changed while in progress.

There are 11 text boxes involved in the For/Next loop, but the error only occurs on certain ones. This leads me to beleive the cause of the error isn't in the array, but somewhere else (???).

I've debug.printed the array elements with a For/Next loop right before the assignment loop starts and everything's fine. Once the assignment loop starts, something is causing the loop index to change to 13, but there was valid data in all the array cells!?!?!?

Anybody have an idea??

Of all the things I've lost, I miss my mind the most!
Sideman

 

[jebenson]

Could you post the code for the For/Next loop?


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[Sheco]

sounds like your index variable is being incremented by mistake somewhere... do you pass it to any functions?

 

[strongm]

Or are we using global variables?

 

[ChewDoggie]

Let's see the loops/subs you're talking about.

 

[Sideman]

Sorry guys, here's some of the code....if you want more, I'll post it. Just trying to save space.

Option Explicit
Option Base 1
Dim x As Integer, y As Integer, z As Integer, xx As Integer, L As Integer, F As Integer, Ct As Integer
Dim Element As String
Dim ans As String
Dim LineItems() As Variant
Dim FormData() As String  ' Single data item array
----------------------------------------------------------------------------------------------------------
Private Sub Form_Load()
     DoEvents
     lngCustNo = modFinance.plngEditCstno
     lngInvNum = modFinance.plngEditInvNum
     txtOldInvNum.Text = lngInvNum
End Sub
------------------------------------------------------------------------------------------------------------
Private Sub Form_Activate()
     DoEvents
     CatName = ""
     ReDim FormData(56)
     ReDim LineItems(12, 11)
.......other code .......
 *** jump to problem sub from here ****
End Sub

Here's where the problem occurs....................
Private Sub DisplayArrays()
  x = 1
  y = 1
     For x = 1 To 12                                    
        If (LineItems(x, 4) <> "" And LineItems(x, 8) <> "") Or LineItems(x, 6) <> "" Then   
           If LineItems(x, 1) > 0 Then                                                                            
              y = LineItems(x, 1)                                                                                   
           End If                                                                                                          
           lblLineNum(y).Caption = LineItems(x, 1)                                                         
           txtOldInvNum.Text = LineItems(x, 2)
           txtCode(y).Text = LineItems(x, 3)
'          txtQtyOrd(y).Text =LineItems(x,4) <--error here
           txtItemNum(y).Text = LineItems(x, 5)
           txtDesc(y).Text = LineItems(x, 6)
'          txtUnitPrice(y).Text = LineItems(x, 7) <--and here
           txtExtPrice(y).Text = LineItems(x, 8)
           CatLineName(y) = LineItems(x, 11)
       End If
    Next x
End Sub

With just those two lines commented out, the routine runs fine, but of course, I need that data on the form.
So, any ideas?

Of all the things I've lost, I miss my mind the most!
Sideman

 

[Sideman]

This might help make sense of the loop...

...it's for an Invoice form
LineItems(x, 1) = LineNumber
LineItems(x, 2) = InvNumber
LineItems(x, 3) = InvCode
LineItems(x, 4) = QOrder
LineItems(x, 5) = ItemNum
LineItems(x, 6) = Description
LineItems(x, 7) = UPrice
LineItems(x, 8) = ExtPrice
LineItems(x, 9) = QShip
LineItems(x, 10) = QBack
LineItems(x, 11) = InvCategory

Of all the things I've lost, I miss my mind the most!
Sideman

 

[Sideman]

The brain is fritzing out today...here's the assignment snip...

adcARInv.RecordSource = "SELECT * FROM ARDetails WHERE InvNum=" & lngInvNum & " "
     adcARInv.Refresh
     With adcARInv.Recordset
          x = 1
          y = 0
          For x = 1 To .RecordCount   ' Display only the data needed for the form
               If Not .EOF Then
                    If (.Fields("QOrder") <> "" And .Fields("ItemNum") <> "") Or .Fields("Description") <> "" Then   ' If there is actual data
                         If .Fields("LineNum") > 0 Then                 ' and a line number exists
                              y = .Fields("LineNum")                        ' then y equals that line number.
                         End If                                                     ' This puts the data back onto the
                         LineItems(y, 1) = .Fields("LineNum")         ' form where it was originally.
                         LineItems(y, 2) = .Fields("InvNum")
                         LineItems(y, 3) = .Fields("InvCode")
                         LineItems(y, 4) = .Fields("QOrder")
                         LineItems(y, 5) = .Fields("ItemNum")
                         LineItems(y, 6) = .Fields("Description")
                         LineItems(y, 7) = .Fields("UPrice")
                         LineItems(y, 8) = .Fields("ExPrice")
                         LineItems(y, 9) = .Fields("QShip")
                         LineItems(y, 10) = .Fields("QBack")
                         LineItems(y, 11) = .Fields("InvCat")
                         CatLineName(y) = .Fields("InvCat")            ' Load Category data into it's own array
                         CatLineAmt(y) = .Fields("ExPrice")
                    End If
               End If
               If Not .EOF Then .MoveNext
          Next x
     End With

Of all the things I've lost, I miss my mind the most!
Sideman

 

[strongm]

So: yes, you are using global variables

Secondly, do you have any Change or Validate events associated with your text boxes?

 

[Sideman]

OK, people. Problem found.

I had a couple text box "Change" routines that my Display routine was triggering as it did it's thing and, which also used "x" as a variable! (I know better...I really do!) This was "secretly" changing the "x" value in my Display routine. (right under my nose!!!) Like I said...brain-dead today.

Unique variable declaration has it's distinct benefits!!

Thanks for any attempts to help me....sometimes I think I beyond help!! lol

Of all the things I've lost, I miss my mind the most!
Sideman

 

[Sheco]

Don't just make them unique, do like strongm suggests and make them local variable instead of global... by declaring them only in the functions where they will be used. Look up the Dim Statment in the help files for more info.