Working with arrays

[Guest]

Good evening,

I have the following code to run through records and populate an array.

*****
Dim arrDwgID() As Variant
Dim intCounter As Long
Dim intArrayCounter As Integer
intArrayCounter = 0
ReDim Preserve arrDwgID(0)

Set rst = CurrentDb.OpenRecordset("Drawing History Tbl")
With rst
If .RecordCount > 0 Then
.MoveFirst
Do Until rst.EOF
If rst("Drawing Number") = DwgNum Then
If rst("Drawing Issue") <> DwgIssue Then
arrDwgID(intArrayCounter) = ![Id]
ReDim Preserve arrDwgID(UBound(arrDwgID) + 1)
intArrayCounter = intArrayCounter + 1
End If
End If
.MoveNext
Loop
ReDim Preserve arrDwgID(UBound(arrDwgID) - 1)
End If
.Close
End With
*****

My problem is that I am receiving an error 9 subscript out of range and the
debugger is highlighting the 'ReDim Preserve arrDwgID(UBound(arrDwgID) - 1)'
line.

I believe it to be because of the fact that arrDwgID is empty in this
specific case? What code change do I need to make to be able to manage this
type of exception?

Thank you,

Daniel

 

[Douglas J. Steele]

If UBound(arrDwgID) > 0 Then
ReDim Preserve arrDwgID(UBound(arrDwgID) - 1)
End If

 

[Guest]

Thank you! That worked beautifully!

If UBound(arrDwgID) > 0 Then
ReDim Preserve arrDwgID(UBound(arrDwgID) - 1)
End If

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Good evening,

I have the following code to run through records and populate an array.

*****
Dim arrDwgID() As Variant
Dim intCounter As Long
Dim intArrayCounter As Integer
intArrayCounter = 0
ReDim Preserve arrDwgID(0)

Set rst = CurrentDb.OpenRecordset("Drawing History Tbl")
With rst
If .RecordCount > 0 Then
.MoveFirst
Do Until rst.EOF
If rst("Drawing Number") = DwgNum Then
If rst("Drawing Issue") <> DwgIssue Then
arrDwgID(intArrayCounter) = ![Id]
ReDim Preserve arrDwgID(UBound(arrDwgID) +
1)
intArrayCounter = intArrayCounter + 1
End If
End If
.MoveNext
Loop
ReDim Preserve arrDwgID(UBound(arrDwgID) - 1)
End If
.Close
End With
*****

My problem is that I am receiving an error 9 subscript out of range and
the
debugger is highlighting the 'ReDim Preserve arrDwgID(UBound(arrDwgID) -
1)'
line.

I believe it to be because of the fact that arrDwgID is empty in this
specific case? What code change do I need to make to be able to manage
this
type of exception?

Thank you,

Daniel

 

[Marshall Barton]

I have the following code to run through records and populate an array.

*****
Dim arrDwgID() As Variant
Dim intCounter As Long
Dim intArrayCounter As Integer
intArrayCounter = 0
ReDim Preserve arrDwgID(0)

Set rst = CurrentDb.OpenRecordset("Drawing History Tbl")
With rst
If .RecordCount > 0 Then
.MoveFirst
Do Until rst.EOF
If rst("Drawing Number") = DwgNum Then
If rst("Drawing Issue") <> DwgIssue Then
arrDwgID(intArrayCounter) = ![Id]
ReDim Preserve arrDwgID(UBound(arrDwgID) + 1)
intArrayCounter = intArrayCounter + 1
End If
End If
.MoveNext
Loop
ReDim Preserve arrDwgID(UBound(arrDwgID) - 1)
End If
.Close
End With
*****

My problem is that I am receiving an error 9 subscript out of range and the
debugger is highlighting the 'ReDim Preserve arrDwgID(UBound(arrDwgID) - 1)'
line.

I believe it to be because of the fact that arrDwgID is empty in this
specific case? What code change do I need to make to be able to manage this
type of exception?

Well, you could ud an If around it to avoid the error, bu
then how would you tell if the array had 0 or 1 entry?

How about initializing the array index to 1 so it will all
work and if UBound is 0, there are no entries.

OTOH, I do have to question why you are using an array. Why
can't you just work directly off the recordset?

Regardless of that, you should add the Drawing Number=DwgNum
and Drawing Issue<>DwgIssue as criteria when you open the
recordset so (dramatically?) fewer records are retrieved and
the If statements would be unnecessary:

Set rst = CurrentDb.OpenRecordset("SELECT Id " _
& "FROM [Drawing History Tbl] " _
& "WHERE [Drawing Number] = " & DwgNum _
& " And Drawing Issue <> " & DwgIssue)

Actually, you can probably eliminate almost all of your code
by using the GetRows method.