Two dimensional array

[key]

Hi Access-Friends,

I've got a table (field1, field2) with x records and want to save the records in a two dimensional array. What I have is:

ReDim ArrayY(0 To countRecords, 0 To 1)

For k = 0 To countRecords
For j = 0 To 1
ArrayY(j, k) = RS!field1
RS.MoveNext
Next
Next

It doesn't work because I don't know hot to read/save the second field

Many thanx in advance

Key

 

[DALeffler]

How about:

ReDim ArrayY(0 To countRecords, 0 To 1)

For k = 0 To countRecords
j = 0
ArrayY(j, k) = RS!field1
j = 1
ArrayY(j, k) = RS!field2
RS.MoveNext
Next

HTH

Doug.

 

[Travis]

How about:

Dim ArrayY As Variant
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim countrecords As Long

Set db = CurrentDb
Set rs = db.OpenRecordset("Select Field1,Field2 from [TableName]")
On Error Resume Next
rs.MoveLast
If Err.Number <> 0 Then Exit Sub
rs.MoveFirst
countrecords = rs.RecordCount

ArrayY = rs.GetRows(countrecords)

 

[DALeffler]

Now that is slick!

(A normalized array ?)

Thanks,

Doug.

 

[s0rtd]

is it possible to use this on a query rather than from a Table??:

Dim ArrayY As Variant 
Dim rs As DAO.Recordset 
Dim db As DAO.Database 
Dim countrecords As Long 

Set db = CurrentDb 
Set rs = db.OpenRecordset("Select Field1,Field2 from [TableName]") 
On Error Resume Next 
rs.MoveLast 
If Err.Number <> 0 Then Exit Sub 
rs.MoveFirst 
countrecords = rs.RecordCount 

ArrayY = rs.GetRows(countrecords)

im talking namely about this line:

 Set rs = db.OpenRecordset("Select Field1,Field2 from [TableName]")

cheers
Jurgen

 

[Travis]

Yes.

Set rs = db.OpenRecordset("qryName")