Need help with Array...

[Steven M. Britton]

I am trying to fill an Array with values from a Query. The info is showing
in the locals window, but I get an error saying "Subscript out of range" and
the line loading the array...??? Help?

Option Compare Database
Option Explicit

Public Sub Set_DNet()

Dim db As DAO.database
Dim rs As DAO.Recordset
Dim varDNet() As Variant
Dim i As Long

Set db = CurrentDb()

Set rs = db.OpenRecordset("qryDNet")

With rs
For i = 0 To rs.RecordCount
varDNet(i) = !Epx 'this is where I get the error...
.MoveNext
Next
End With


End Sub

 

[Brendan Reynolds]

There are two problems here. One, you need to redim the array, and two, you
can't depend on getting an accurate count from the RecordCount property
unless you do a MoveLast first. Try something like the following instead ...

Public Sub RedimTest()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim varDNet() As Variant
Dim i As Long

Set db = CurrentDb()

Set rs = db.OpenRecordset("SELECT * FROM Table1")

With rs
Do Until .EOF
ReDim varDNet(i)
varDNet(i) = .Fields(0)
Debug.Print varDNet(i)
i = i + 1
.MoveNext
Loop
.Close
End With

End Sub

 

[Steven M. Britton]

Thank you:

Two follow up questions, why do I have to redim the array?

And can I make an array by something like dim varArray ("$", "!", "`") as
string to use to compare items later?

-Steve

 

[Brendan Reynolds]

Oops! I forgot one very important little keyword ...

ReDim Preserve varDNet(i)

 

[Ron Weiner]

Why not

Set rs = db.OpenRecordset("SELECT * FROM Table1")
With rs
If not .EOF
.MoveLast
ReDim varDNet(.RecordCount)
. MoveFirst
End If
Do Until .EOF
varDNet(i) = .Fields(0)
Debug.Print varDNet(i)
i = i + 1
.MoveNext
Loop
.Close
End With
Set rs = Nothing

No need to redim that array a zillion times.

 

[Douglas J. Steele]

Using Dim with empty parentheses declares a dynamic array. However, you need
to tell VBA how big the array is before you can use it, which is what ReDim
does.

And while you can't do dim varArray ("$", "!", "`"), you can do

Dim varArray As Variant

varArray = Array ("$", "!", "`")

 

[Douglas J. Steele]

If the recordset is big, that could be slow. However, ReDim is an
"expensive" operation in terms of resource requirements, so moving to the
end of the recordset and back could be better. You could also do:

Set rs = db.OpenRecordset("SELECT * FROM Table1")
With rs
If not .EOF
.MoveLast
i = .RecordCount
ReDim varDNet(i)
End If
Do Until .BOF
varDNet(i) = .Fields(0)
i = i - 1
.MovePrevious
Loop
.Close
End With
Set rs = Nothing

 

[Brendan Reynolds]

You're probably right, Ron. I was shying away from the .MoveLast because, if
the recordset is large, and especially if the data must be retrieved across
a network, that's an expensive operation. But then, a redim is an expensive
operation too, so yes, the .MoveLast might be the less expensive option.

Of course, there's the more fundamental question of why an array in the
first place? We don't know what the ultimate goal is, so we don't know
whether it really requires an array, or could be achieved using the
recordset directly.

 

[Michel Walsh]

Hi,


Or try something like:



Dim var() As Variant
var=db.OpenRecordset("qryDNet").GetRows()





Hoping it may help,
Vanderghast, Access MVP

 

[Albert D.Kallal]

Of course, there's the more fundamental question of why an array in the

first place? We don't know what the ultimate goal is, so we don't know
whether it really requires an array, or could be achieved using the
recordset directly.

Yes...Brendan..that is my vote too!!...why not just use the roecrdset...it
is a cool object, and you can even use it to write data "back" to the
database.

And, if you add new fields, or change the design of your table..the
reocrdset can reflect this. With an array, every time you change the
structure...you may have to change your array.

Arrays are just not much used anymore...not because I don't like them...they
just don't seem to be bit ticket they used to be.