Get 1 record from Select Statement and put value into code.

  • Thread starter Thread starter Hoardling via AccessMonster.com
  • Start date Start date
H

Hoardling via AccessMonster.com

I have a SELECT statement that resulst in only 1 record, statement created in
VBA Code. The SELECT statement has only 1 column which displays a unique ID,
primary key. I have to retrieve the unique ID through a couple of WHERE
clauses. What I want to know is how to take that value, unique ID, and put
it into a variable, so that other parts of my code can use that result.
 
You could use the query as a recordset, but that is the slow way and requires
more code. If you are returning only one field in one record, a DLookup
function would be a better choice. You can load the results of the function
directly into a variable:
 
Klatuu said:
You could use the query as a recordset, but that is the slow way and requires
more code. If you are returning only one field in one record, a DLookup
function would be a better choice. You can load the results of the function
directly into a variable:
Click to expand...

I am using a pass through to retrieve my data, so I don't know exactly how to
use a DLookup with it, if possible. So I was looking at the query as a
recordset, which I haven't been able to get it to work.
 
SomeVariable = DLookup("[FIELD]", "TableName", "[SOME_FIELD] = '" &
VariableOrControl & "'")

SomeVariable is the name of the variable you want the value in
[FIELD] is the name of the field you want the value from
TableName is the name of the table you want the value from
[SOME_FIELD] is any field in the table you want to use to filter on.
VariableOrControl is either a variable, a control, or a literal value that
is the criteria you want use for filtering. It is exactly like a WHERE
predicate without the word WHERE.
 
Back
Top