problem with initalise the query results to an array

[neelesh kumar]

sir,
in my database i have a query which is taking the subjectcodes for a particular testid.
suppose if for particular testid it takes ma,phy,che , i want to insert the query results in array in my code.
can we pass the query values to an array.
dim subcode as variant is there in my code.
how to do this
please help me....
thanks in advance.

[Rabbit]

You'll have to either use a DLookup if there's some sort of ID to go by or you'll need to open up the query as a recordset and step through to assign the values to the array.

[ADezii]

sir,
in my database i have a query which is taking the subjectcodes for a particular testid.
suppose if for particular testid it takes ma,phy,che , i want to insert the query results in array in my code.
can we pass the query values to an array.
dim subcode as variant is there in my code.
how to do this
please help me....
thanks in advance.

Here is a Code Snippet that will point you in the right direction. The following assumptions are made:

__1. Table Name: tblTest
__2. Field to Query on: [testid]
__3. Field values to place into Array: [subjectcodes]

Dim MyDB As DAO.Database, MyRS As DAO.Recordset, MySQL As String
Dim intNoOfRecords As Integer, intConter As Integer, astrCodes() As String
 
MySQL = "SELECT * FROM tblTest WHERE tblTest.testid=3"
 
Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset(MySQL, dbOpenSnapshot)
MyRS.MoveLast: MyRS.MoveFirst
 
intNoOfRecords = MyRS.RecordCount
 
ReDim astrCodes(1 To intNoOfRecords) As String
 
For intCounter = 1 To intNoOfRecords
  astrCodes(intCounter) = MyRS![subjectcodes]
  MyRS.MoveNext
Next
 
MyRS.Close

[Denburt]

Check out the getRows method in the VBA help file.
Just slightly more efficient.