Displaying last date in a text box from another forms subform

[jjake]

I have a form with a continuous subform. frmPM w/ subfrmPMTasks.

When i click on the task description it opens up another form with a continuous subform. frmPMTaskDetails w/ subfrmPMTaskCompletions.

I would like to display the last "Completion Date" entered from subfrmPMTaskCompletions in the textbox "LastCompletionDate" on subfrmPMTasks.

Thanks.

 

[MarkK]

I recommend you draw this data directly from the table where it lives. Check out the DLookup() and DMax() functions, or search for "MS Access Domain Aggregate Functions."

A user interface (UI) element (like a form, subform, or control) excels at data presentation, but is in fact a cumbersome middleman to use as a data source. Rather, you should always take the shortest route between your UI and the data, and that shortest route is never via the UI.

 

[Galaxiom]

I recommend you draw this data directly from the table where it lives. Check out the DLookup() and DMax() functions, or search for "MS Access Domain Aggregate Functions."

A user interface (UI) element (like a form, subform, or control) excels at data presentation, but is in fact a cumbersome middleman to use as a data source. Rather, you should always take the shortest route between your UI and the data, and that shortest route is never via the UI.

Would have to disagree because the form already has a recordset with the required data.

Put a textbox in the form's header or footer with the following ControlSource. The name must be a field in the recordset, not a control on the form.

= Max([Completion Date])

If you would rather display it in the Detail section of the form, make it not Visible, add another textbox and refer to the header/footer textbox in its ControlSource.

 

[jjake]

Would have to disagree because the form already has a recordset with the required data.

Put a textbox in the form's header or footer with the following ControlSource. The name must be a field in the recordset, not a control on the form.

= Max([Completion Date])

If you would rather display it in the Detail section of the form, make it not Visible, add another textbox and refer to the header/footer textbox in its ControlSource.

Would this be the header/footer of the sub form the completion date is currently present in?. I need to display it in another forms sub form.

 

[Grumm]

You can do this by changing the recourd source of the subform subfrmPMTasks into something like this :
select PMTasks.*,Max(PMTaskDetails.CompletionDate) from PMTasks join PMTaskDetails on PMTasks.TasksID = PMTaskDetails.TaskID

That way you can mix fields from different tables in one form.
(Warning : the sql is just an idea. You still need to change the fields or tables to the correct names)

If you are not sure about the result, you can first create a query and use the query designer from Access. If you are happy with the result, just copy the SQL and past it in the recordsource of the form.

 

[prowaseem]

you have to specify before creating table, when choosing columns for database.

 

[jjake]

I recommend you draw this data directly from the table where it lives. Check out the DLookup() and DMax() functions, or search for "MS Access Domain Aggregate Functions."

A user interface (UI) element (like a form, subform, or control) excels at data presentation, but is in fact a cumbersome middleman to use as a data source. Rather, you should always take the shortest route between your UI and the data, and that shortest route is never via the UI.

I input the following into the control source of the CompletionDate text box

=DMax("completiondate","tblpmTaskCompletions")

which displayed the value just fine. But it showed the same date for every record.

How do I fix this?

 

[jjake]

I got it figured out. I just assigned a query to subfrmPMTasks to include the completion column from tblPMCompletions and used the group by max and assigned that to the unbound text box.

 

[MarkK]

Good for you. Thanks for posting back with your solution.

I misunderstood your requirement, I though you wanted data from the subform to show in a control on your main form. Glad you got this figured out!