473,320 Members | 1,922 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Loading text box after combobox selection

116 100+
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
3 1452
Dan2kx
365 100+
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
phytorion
116 100+
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
Dan2kx
365 100+
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

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

Similar topics

1
by: Steve | last post by:
I have a continuous form with several unbound comboboxes in the form header. The comboboxes are used to provide criteria for the SQL of the form. The code behind the form looks like: Public Sub...
5
by: Steve | last post by:
I have an unbound combobox in the form header of a continuous form. The selection in the combobox sets the where clause in a querydef which determines QryPFrmInventoryManagement. The following code...
3
by: google | last post by:
This is something I've done plenty of times in '97, but I can't seem to get it to work correctly in Access 2003. Say, for example, I have a form with an unbound combobox, the data source is a...
0
by: George Hartas | last post by:
I am using Visual C# .NET 2003 to make a ComboBox accept both mouse and keyboard selection. For mouse selection code, I double-clicked ComboBox to get the default "comboBox1_SelectedIndexChanged"...
5
by: CCLeasing | last post by:
Hello, I have searched google but can not find a straight forward answer to my problem. Hopefuly someone will be kind enough to offer their expertise. Please forgive if this seems a bit convoluted...
0
by: CCLeasing | last post by:
Hello, I have searched google but can not find a straight forward answer to my problem. Hopefuly someone will be kind enough to offer their expertise. Please forgive if this seems a bit convoluted...
1
by: miadlor | last post by:
Hi, I've searched and searched..... I'm trying to use a combobox to load a textbox on selection. The problem is the textbox will populate according to what is selected, but not all the info...
18
by: Academia | last post by:
I let the use modify the text of a combobox and then I replace the selected item with the new text (in Keyup event). But if he sets the Text property to an empty string ("") that sets the...
2
by: billa856 | last post by:
Hi, My project is in MS Access. In that I have one Form in which I have some Comboboxes. 1st one is Independent, 2nd one is dependent on first one, 3rd one is dependent on 1st and 2nd both....
1
by: Audie7734 | last post by:
I have a comboBox in which a store individual's names. Upon loading the program, I want the comboBox loaded from a text file. Note: the first line of the txt file is: <Enter New Name>. I want to...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.