Hello,

I have a continuous main form and various subforms in the tabs. In my main form, I would like to display the most current data as a summary line.

Example:

Main form is using tblProject, it contains Project_ID, Project_Description, Start_Date, End_Date, etc.

Continuous subform is using tblExpenditures, it contains Expenditure_ID, fk_Project_ID, Date, Actual_Expenditure

I want to display the most current actual expenditure (according to the max date) in a main form unbound field.

Anyone can help? Thanks in advance.

This post has been edited by Belebala: Jun 8 2011, 10:01 AM

Seems like you could do this using a DLookup() function as a source for an unbound textbox, or using a query that returns that "most recent" expense record, and use that to help you fill the textboxes.

Sorry I'm not good at coding. Could you post an example?

Thanks.

"How" depends on "what", and I don't have a very clear picture of what data (i.e., table) structure you're using.

If you use a query, you can design that without any coding.

I did try to use a query to do max and last criteras and I'm able to display the most current expenditure by choosing this as a control source as a form query. But the problem is I'm not able to create any new record.

I still don't have a clear picture of how you've organized your data (tables).

In my tblProject, i have ProjectID, description, lead, sponsor, ... and in tblExpenditure, I have ExpID, fk_ProjectID, Report_Date, Actual_Expenditure. One to many relationship of tblProject to tblExpenditure.

In my main form's query, I only query for the tblProject and another query of tblExpenditure in a subform. I have tried create one query in my main form to display tblProject plus the last value of Actual_Expenditure from tblExpenditure, but because I'm using Last and Max function, it doesn't allow me to create a new record.

How can I create an form OnLoad function to display the actual expenditure value by the max report date? Or how can I create another query within the main form?

Thanks.