By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,209 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

Loading text box after combobox selection

100+
P: 116
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_ID" where "WORK_DATE" is the newest work date for the given "BUILD_NAME".

This is the SQL:
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2. A.RESOURCE_ID 
  3. FROM tblWORK_DETAIL A WHERE  
  4. A.BUILD_NAME = forms.cboBUILD_NAME 
  5. AND A.WORK_DATE = (SELECT MAX(B.WORK_DATE) FROM tblWORK_DETAIL B WHERE 
  6. B.BUILD_NAME = A.BUILD_NAME 
  7. AND B.WORK_DATE = A.WORK_DATE)
  8.  
I've tried using a Dlookup function and that didn't work, i think the where clause is to complicated.
Expand|Select|Wrap|Line Numbers
  1. txtLAST_RESOURCE.Value = DLookup("resource_id", "tblWORK_DETAIL", "MAX(WORK_DATE)=WORK_DATE AND " & "BUILD_NAME = " & cboBUILD_NAME.Value)
  2.  
Any ideas of how i might go about this?

Eric
Dec 18 '07 #1
Share this Question
Share on Google+
3 Replies


100+
P: 365
me!txtLAST_RESOURCE.Value = DLookup("resource_id", "tblWORK_DETAIL", "MAX(WORK_DATE)=WORK_DATE AND BUILD_NAME = " & cboBUILD_NAME.Value)

try that
Dec 18 '07 #2

100+
P: 116
me!txtLAST_RESOURCE.Value = DLookup("resource_id", "tblWORK_DETAIL", "MAX(WORK_DATE)=WORK_DATE AND BUILD_NAME = " & cboBUILD_NAME.Value)

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?
Dec 18 '07 #3

100+
P: 365
me!txtLAST_RESOURCE.Value = DLookup("resource_id", "tblWORK_DETAIL", "MAX(WORK_DATE)=WORK_DATE AND BUILD_NAME = " & cboBUILD_NAME.Value)

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,
Dec 18 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.