Need to turn List From Query Results into an Array

[Guest]

I have a table that has a text filed containing Values that are not unique to
each record. I am performing a Select DistinctRow query to get the list of
unique values. I then want to use the results of that query to use each
distinct value as an array item in order to make a new table for each item in
the array with the name of the array item. I'm having trouble getting the
list (query results) into my array. Once I get there I'll be good to go.

 

[Douglas J. Steele]

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim lngLoop As Long
Dim lngSize As Long
Dim strSQL As String
Dim strValues() As String

strSQL = "SELECT DISTINCT Values FROM MyTable"
Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset(strSQL)
rsCurr.MoveLast
rsCurr.MoveFirst
lngSize = rsCurr.RecordCount
ReDim strValues(1 To lngSize)
For lngLoop = 1 to lngSize
strValues(lngLoop) = rsCurr!Values
rsCurr.MoveNext
Next lngLoop
rsCurr.Close
Set rsCurr = Nothing
Set dbCurr = Nothing

 

[Guest]

Doug this is returning the proper number of records for lngSize, but the
strValues(lngLoop) is returning "" and is not found in the collection.

Any Ideas?

 

[Douglas J. Steele]

What's the exact code you're running?