Result of an SQL Statement into a variable

[Razor1]

I have a sql statement that returns 1 field. I need to put that field into a variable. I know I can open a recordset to get the value. Thie sql statement runs several times during the program execution.
Is there a way to directly put the result of the sql statement into a variable without opening a recordset?

Here is the code:

rstSQLCurrBom = "SELECT Max([ItemSpecId]) AS CurrentBOM " & _
"FROM dbo_ItemSpecs " & _
"WHERE (((dbo_ItemSpecs.ItemNo)='" & rstChairs!Item & "'));"

CurrentBOM is what I need to put in a variable.

Thanks in advance for your help.

Razor1

 

[Andrzejek]

Did you try this:

http://tinyurl.com/6zrn485

Have fun.

---- Andy

 

[SkipVought]

Here is the code:

You really mean, "here is only part of the code."

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MajP]

There are workarounds (domain aggregate functions, binding to a control) but I would think you are best off to open the recordset. Even if you are doing this hundreds of times I would think it would be fast. Open it as readonly or forward for speed since you are only reading.

You may be able to do a group by on your query and then leave the recordset open. Then just do a find first. But I am not sure what part is changing when you run it the subsequent time. Still probably not much faster unless the original query takes a long time to run initially.

 

[PHV]

Why not simply use the DMax function ?

strCurrBom = DMax("ItemSpecId", "dbo_ItemSpecs", "ItemNo='" & rstChairs!Item & "'")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886