Array Dimension

[matrixindicator]

Hello, dummy question,

I use a recordset to fill an array = ok it works.
However he asked to set the dimension of the array "FJa(72)"
I suppose this is the number of elements I expect to put in.
I have 72 records, but this is variable. Can you set the dimension variable ?

Dim FJa(72) As String
Dim i As Integer
 
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("TABLE", dbOpenDynaset)

i = 1
Do Until rst.EOF
    FJa(i) = rst!FIELD
    i = i + 1
    rst.MoveNext
Loop
i = Empty
i = 1

 

[JerryKlmns]

Dim FJa() As String
Dim i As Integer

Set dbs = CurrentDb
'Count your records
Set rst = dbs.OpenRecordset("SELECT COUNT(*) FROM TABLE", dbOpenDynaset)
'Redimension the array
ReDim FJa(1 To rst.fields(0))
'Close rst
rst.close

Set rst = dbs.OpenRecordset("TABLE", dbOpenDynaset)
i = 1
Do Until rst.EOF
FJa(i) = rst!FIELD
i = i + 1
rst.MoveNext
Loop
...

 

[jadams0173]

Yes you can. Have a look at ReDim Preserve or maybe you could set the ubound = to the record count.

 

[TheAceMan1]

matrixindicator . . .

In parallel with JerryKlmns but without the extra recordset:

[blue]   Dim db As DAO.Database, rst As DAO.Recordset, fld As Field
   Dim FJa(72) As String, i As Integer
   
   Set db = CurrentDb
   Set rst = db.OpenRecordset("TABLE", dbOpenTable)
   Set fld = rst!Field
   
   If Not rst.BOF Then
      ReDim Ary(1 To rst.RecordCount)
      i = 1
      
      Do Until rst.EOF
          FJa(i) = fld
          i = i + 1
          rst.MoveNext
      Loop
   End If

   Set fld = Nothing
   Set rst = Nothing
   Set db = Nothing[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884: