Need to turn List From Query Results into an Array

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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
 
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?
 
Back
Top