Main form control to read subforms max value 1

[ragu111]

In my form i have a unbound textbox which will show the latest contract number.

same form i got a subform with all contract details for the customer (like below)

date contract_No expiry
-----------------------------------------
01/02/2004 ABC1234 21/03/2004
12/03/2005 DEF5678 23/10/2005
23/06/2006 GHI9012 31/12/2006

I want the textbox in mainform to show the max date's contract_No from the subform. (mainform textfield should show "GHI9012")

can someone help with the code.
Ragu

 

[Remou]

You should be able to use domain aggregate functions. For example:

[tt]=DLookUp("Contract_No","tblContracts","[Date] = #" & Format(DMax("[Date]","tblContracts"),"yyyy-mm-dd") & "# And Customer_No = " & [Customer_No])[/tt]

 

[ragu111]

the field is reading as below...

#Name?


not reading the correct value

Ragu

 

[Remou]

Can you post the line as amended by you for your application, please.

 

[ragu111]

Dear Remou

here is the code

=DLookUp("REPORTING_NAME","EMP_REPORTING","[FROM_DATE] = #" & Format(DMax("[FROM_DATE]","EMP_REPORTING"),"yyyy-mm-dd") & "# And [EMP_HEAD_ID]=" &  [ID])

 

[Remou]

I missed a bit:
[tt]=DLookUp("REPORTING_NAME","EMP_REPORTING","[FROM_DATE] = #" & Format(DMax("[FROM_DATE]","EMP_REPORTING","[EMP_HEAD_ID]=" & [ID]),"yyyy-mm-dd") & "# And [EMP_HEAD_ID]=" & [ID])[/tt]

But the error you are getting is probably due to a difference in field names, for example, is ID the name of the control on the main form that contains EMP_HEAD_ID?

 

[ragu111]

Perfect...
it's working fine

Ragu