Lookup queries

J

Joshua6007

I need to write some code to look up a value from a database table that has a
ID and Name columns. So given the ID, it would return the Name. This *needs*
to be done from VBA. Is there a way to create a query, then execute it from
within the VBA code, similar to a stored procedure, give it the ID, and get
back the Name?
Thanks!
 
A

Albert D. Kallal

The function you need is:

my text = dlookup("field name","table name","id = 123")


So, in code

strID = "123"

msgbox "the text value is = " & dlookup("Name","tblCustomers","id = " &
strID)
 
J

Joshua6007

Thanks. This was very helpful. Now suppose I have a complex query that I need
to execute from my code, give it a parameter (like do the calculation for
userA, then userB, etc) and get a result back. How would I do that?
 
A

Albert D. Kallal

Thanks. This was very helpful. Now suppose I have a complex query that I
need
to execute from my code, give it a parameter (like do the calculation for
userA, then userB, etc) and get a result back. How would I do that?
Click to expand...


my text = dlookup("field name","YourComplexQury", "your complex conditions")

It really depends on where, and how you need this data. I mean, if you going
to have to process more then one value..then likely a reocrdset is better
then the above.

As far as parameter goes, you might need processor all records for a
particular city.

dim rstData as dao.RecordSet
dim strSql as string


strSql = "select * from tblCustomers where City = 'Edmonton'"

set rstData = currentdb.OpenReocrdSet(strSql)

do while rstData.EOF = false
--- do somtihing with the reocrd
msgbox "company name is = " & rstData!CompnayName
rstData.MoveNext
loop
rstData.close

So, you can add, or make any parameter etc you want in code. The use of
dlookup is less code, and handy..but only handy if you need ONE VALUE.

if you need to process a bunch of records....then the reocrdset loop code
example is the way to go.
 
Top