Value from database record to VBA variable

[northy]

How can I get a value from a database record into a VBA variable? I need to get the value from a different field from the one used intially to search the table to find the matching record.

For example: I search the table "Project Details" for a record with a Project ID = 3 and want to capture the value of that records Template ID to a variable called CurrentTemplate.

Any ideas?...I'm stumped.

[This message has been edited by northy (edited 08-29-2001).]

 

[Drew]

hi northy,

you can either use DLookup -

CurrentTemplate = DLookup("[Template ID]", "[Project Details]", "[Project ID] = 3")

or ( if in 97 ) to

dim rst as recordset

set rst=CurrentDB.OpenRecordset("Select [Template ID] FROM [Project Details] WHERE [Project ID]=2")

if rst.recordcount then
CurrentTemplate=rst(0)
else
'something appropriate
msgbox "Nothing found...."
end if

rst.close
set rst=nothing

the second, despite being more code, is quicker than the DLookup esp noticeable in big tables

HTH

Drew

 

[northy]

Thanks...but I have previously managed to locate the record using your first method.

What I need to know is how to get data from another field in this record (ie not the field used in the search) to a variable in my vba code.

Any ideas?

[This message has been edited by northy (edited 08-29-2001).]

 

[Drew]

I may be missing something ( sure wouldn't be a first...) but
CurrentTemplate = DLookup("[Template ID]", "[Project Details]", "[Project ID] = 3") will get the "Template ID" field value where "Project ID" is 3, it won't return "Project ID". Is that not what you were after?

Drew

 

[northy]

Your not missing anything...i just didn't read your post properly...thanks that works great.

Sorry about that.