Internal SELECT

[Temporalis]

I'm working on a database with its front end in the same file (I know,
not good practice), and I'd like to run a command internally to get
values from the database.

i.e.
SELECT Value FROM tblTable WHERE Index = 5

How could I get a connection to the current database from the VBA to
execute this command? Is there an easier way to get at the data I'd
like?

Thanks!
- Kurtis Story

 

[Amy Blankenship]

this should point you in the right direction
http://www.mvps.org/access/queries/qry0003.htm.

HTH;

Amy

 

[Guest]

Kurtis:

You can use the DLookup function rather than a query:

Dim varValue As Variant

varValue = DLookup("Value", "tblTable", "Index = 5")

This will assign the value to the variable varValue. By declaring the
variable as a Variant this caters fro the possibility of the function
returning a Null (if no row exists whwre Index = 5). Only variables of
Variant data type can be Null.

It would be possible to do it with SQL. What you'd do is create a Recordset
object baswd in the SQL statement and examine its Value field. However, for
getting a single value the Dlookup function has been shown by benchmarking to
be quicker.

Ken Sheridan
Stafford, England