How to save result from query into a variable...

[czach]

I'm trying to save a result data into a variable but i don't have a clue on how to do it... can anyone help me with this?..

i'm pretty sure that this is wrong but this is what i'm trying to do

 Dim product as String
 
product = DoCmd.RUNSQL = "SELECT product_name FROM product" 

let's just assume that the query returns only a single row... and i'm using ms access 2007 by the way... hope somebody can help me..

[dsatino]

Dim rst as recordset
Dim product as string
Dim db as database
 
Set db = currentdb
 
Set rst = db.openrecordset("SELECT product_name FROM product")
 
With rst
   .movefirst
   product = .fields(0)
End with
 
set rst = nothing
set db = nothing
 

This will only capture the variable for use within this sub though. If you need to use it elsewhere you'll need to pass it to the other procedure or simply declare the variable as Global outside of this procedure.