Jun 8 2011, 10:00 AM
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.
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.
Jun 8 2011, 10:03 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.
Jun 8 2011, 10:10 AM
Sorry I'm not good at coding. Could you post an example?
Jun 8 2011, 11:39 AM
"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.
Jun 8 2011, 01:54 PM
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.
Jun 8 2011, 05:14 PM
I still don't have a clear picture of how you've organized your data (tables).
Jun 10 2011, 09:56 AM
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?
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here