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.