making an array out of a query result

[Richie27]

Posting a lot here as I am a total noob and have no place else to go!!



making an array out of a query result - how do I do this??? Help!

[ssanfu]

Pretty vague question. What are you trying to do? Maybe a recordset would be better. Why do you want to use an array?

[Richie27]

I know ya and apologies.

Well say I want to - "select * fruits from fruit_n_veg_shop" and I want to make an array directly out of that.

It could have any number of records an maybe like ("apples","oranges","pears","peachs", etc etc)

[ssanfu]

Again, Why do you want to use an array? Would a recordset work? Arrays generally take more coding....

[ssanfu]

OK, so here is an example of using a dynamic array.

Public Sub MakeArray()
'uses DAO
   Dim dbs As DAO.Database
   Dim rst As DAO.Recordset

   Dim varArray() As String  'dynamic array name
   Dim k As Integer   'counter
   Dim RC As Integer  'record count

   Set dbs = CurrentDb()
   k = 0
   'open recordset
   'SQL of query = "SELECT DISTINCT fruit_n_veg_shop.Fruits FROM fruit_n_veg_shop;"
   'NOTE:  query has only one field
   
   Set rst = dbs.OpenRecordset("qryFruits", dbOpenDynaset)
   
   If Not (rst.BOF And rst.EOF) Then
      rst.MoveLast
      RC = rst.RecordCount
      rst.MoveFirst
      
      'set array dimension
      ReDim varArray(RC)

      'fill recordset
      Do Until rst.EOF
         varArray(k) = rst.Fields(0)
         rst.MoveNext
         k = k + 1
      Loop
   End If
   '--------------------------------------

   '    do other stuff here with the array

   '--------------------------------------

   rst.Close
   Set rst = Nothing
   Set dbs = Nothing
End Sub