Loading text box after combobox selection

[phytorion]

This seems like it should be simple to do but i can't seem to get anything to work. I result i'm looking for is after a user selects a "Build Name" from a combo box it goes to the the table "tblWORK_DETAIL " and returns the field(in this case "RESOURCE_I D" where "WORK_DATE" is the newest work date for the given "BUILD_NAME ".

This is the SQL:

SELECT 
A.RESOURCE_ID 
FROM tblWORK_DETAIL A WHERE  
A.BUILD_NAME = forms.cboBUILD_NAME 
AND A.WORK_DATE = (SELECT MAX(B.WORK_DATE) FROM tblWORK_DETAIL B WHERE 
B.BUILD_NAME = A.BUILD_NAME 
AND B.WORK_DATE = A.WORK_DATE)

I've tried using a Dlookup function and that didn't work, i think the where clause is to complicated.

txtLAST_RESOURCE.Value = DLookup("resource_id", "tblWORK_DETAIL", "MAX(WORK_DATE)=WORK_DATE AND " & "BUILD_NAME = " & cboBUILD_NAME.Value)

Any ideas of how i might go about this?

Eric

[Dan2kx]

me!txtLAST_RESO URCE.Value = DLookup("resour ce_id", "tblWORK_DETAIL ", "MAX(WORK_DATE) =WORK_DATE AND BUILD_NAME = " & cboBUILD_NAME.V alue)

try that

[phytorion]

me!txtLAST_RESO URCE.Value = DLookup("resour ce_id", "tblWORK_DETAIL ", "MAX(WORK_DATE) =WORK_DATE AND BUILD_NAME = " & cboBUILD_NAME.V alue)

try that

errors out and say i can't have an aggregate function. I'm not really sure what the limits are to the Dlookup() are but i'm guessing it can only handle one statement in the where clause. Is there a function i'm not thinking of that i should be using?

[Dan2kx]

me!txtLAST_RESO URCE.Value = DLookup("resour ce_id", "tblWORK_DETAIL ", "MAX(WORK_DATE) =WORK_DATE AND BUILD_NAME = " & cboBUILD_NAME.V alue)

try that

It is because of the MAX statement you have there, you can have as many where expressions as you want as long as they all tally up

... you need to use DMAX to return the Max if the date field and then put that back into your Dlookup if you need to,