Set recordset value as a variable

[DaveLondon]

Hi there,

I'm using Acces 2000 and I currently have working code that moves though a record set:

Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim strSql As String
Dim dataitem As String


strSql = "SELECT CODE FROM Qry_Unique_Stocks;"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSql)


Do While Not rs.EOF

Set dataitem = strSql

'code to run the query will be here.

rs.MoveNext
Loop


rs.Close
Set rs = Nothing
Set db = Nothing


However I want to get the value of the record set into the variable dataitem for each value of the record set. dataitem will then be picked up by a query which runs before moving to the next value of the recordset.

I'm getting an object required error message at the line Set dataitem = strSql

Any advice is greatly received.

Many thanks,

Dave.

 

[SkipVought]

Hi,

You SET object variables. However this variable is just a STRING, so, althought it seems trivial, loose the SET...

   [s]Set [/s]dataitem = strSql
[/coe]

Skip,
[sub]
[glasses]Just traded in my old subtlety...
[b]for a NUANCE![/b][tongue][/sub]

 

[Andrzejek]

But this way your dataitem will always contain string: "SELECT CODE FROM Qry_Unique_Stocks;"

Consider something like this:

strSql = "SELECT CODE FROM Qry_Unique_Stocks;"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSql)

Do While Not rs.EOF
    [blue]dataitem = rs!CODE.Value[/blue]
    [green]'code to run the query will be here.[/green]
    rs.MoveNext
Loop

Have fun.

---- Andy

 

[DaveLondon]

Thanks guys That's fantastic