Main form control to display latest subform entry

[dbDamo]

Hi

I have a main form (frmIncidentLog) which holds details of all incident calls raised to our system providers. Within this form I have a subform (frmIncidentUpdates) which holds incident call status updates which are added to every 3 days. The subform contains the fields UpdateDate and CallStatus.

I would like to create a control on frmIncidentLog that looks up the latest UpdateDate in frmIncidentUpdates and returns the CallStatus for that date. The main and subforms are linked by a field named SSRNumber.

I have tried using the DMAX function but with no luck - is this the right function to use?

I have also been trying to perform the calculation in the textbox in the main form - would it be easier to work out the latest CallStatus within the subform and then simply refer to this from the main form?

Any help greatly appreciated!!!

 

[Galaxiom]

The best place to find the maximum value is in the object whose Record Source contains the field. In your case this sounds like the subform. A aggregate from the object's Record Source is preferable to using a domain function which must open another recordset.

Use a control source in a text box in that form:
=Max([fieldname])

If the form is in Datasheet mode (and in reports) place it in the Header or Footer section.

Then, as you suspected, refer to the control on the subform from the main form.

BTW Your question would be better presented by refereing to "the subform" and "the Main form" rather than have the reader trying to grasp the names of your forms. Similarly the fields.

 

[dbDamo]

Thanks for the help Galaxiom - note taken about the presentation of the question - just thought it might be easier to know the names of the forms/controls if someone were to provide an example formula.

I don't think I have been clear in what I was asking for. My subform displays data in this way for each record in the main form:-

UpdateDate | CallStatus
01/12/09 | Raised
04/12/09 | Issued to Developer
07/12/09 | System Tested
10/12/09 | Awaiting Release

In this case, I would like the new control on the main form to look up the latest date (10/12/09) and return the call status for this date (Awaiting Release)

 

[Galaxiom]

Derive the Maximum date with this control source on a textbox say called MaxUpdateDate in the footer of the subform.

=Max([UpdateDate])

The Control Source of the textbox on the main form will be:

=DLookup("[CallStatus]","[subform source table]","[subform source table].[UpdateDate]= Forms!Formname!subformcontrolname.Form!MaxUpdateDate"

You can normally drop the tablename in the third argument unless there are controls called by the fieldname. Access sometimes get confused if their is ambiguity.

 

[dbDamo]

=Max([UpdateDate]) picks up the max UpdateDate for all records in the control source - I only want it to pick the max UpdateDate for the records linked to the record in the main form.

I have created a new control on the main form to try to select the latest date using:-

=Max(Forms![frmIncidentLog]![frmIncidentUpdates].Form![UpdateDate])

but this is returning an #error. When I remove the max calculation it simply returns the UpdateDate for the first record in the subform.

Now I know I could go and change the sort of the control source to display the latest entry first making my task much simpler, but should the user change the sort when viewing the form, the control will change to display the earliest date and the incorrect value will be displayed for all records while the user is in the form or until they change the sort back.

 

[JamesMcS]

So you want a text box in the main form to show the latest status as shown in the subform? How about setting the control source of the text box to a select statement, bringing back the status with max of update date in the incident log, where SSRNumber is equal to the main form? It would be a calculated control though.... if you want to save the status in the main table, add the status field into the main table and use an SQL update statement in a command button's on click (or the afterupdate of the last text box in the subform) to update the main table status to whatever the user has selected in that record?

 

[dbDamo]

All sorted. Bit more long winded as I would have liked though.

I ended up changing the sort of the control source to UpdateDate descending, disabling the right click menu to stop users changing the sort and then creating a control on the main form to concatenate the UpdateDate and CallStatus subform controls.

Thank you both for your time and suggestions + rep for both.