Summary from subforms on main form (1 Viewer)

[Juan]

Hi everyone

I use Access 2003 and have created a form with several subforms which I've placed onto a number of tabs. I'd like to have some of the most recently entered data from the subforms displayed on the main form.

For instance - if one of the subforms details the repairs to my car, in terms of date, items repaired, name of the mechanic and the cost, I'd like the most recent date of repair to be displayed on the main form. I know that I can see it be clicking the "Car Repairs" tab then scrolling down the information, but I'd like to have it displayed for easy viewing. I'm not sure if I'd need to have some VBA to do this or if it can be achieved by, for instance, creating a query to populate the appropriate textbox on the main form.

Thanks for your time and patience!

Juan

 

[spacepro]

Juan,

You could create a textbox with the following controlsource:

DMax("[Field Name]","[Table/Query Name]")

This will return the maximum value entered or Dmin will return the lowest.

Search in Access Help for DMin and Dmax and it explains how you can use criteria to return the max or min values.

Hope this helps

Andy

 

[Juan]

Thank you Andy. I understand now how DMax and DMin work, but it doesn't do what I want!

Imagine a subform with 3 rows of data, each having 6 fields and I want fields 2, 5 and 6 of the 3rd row to be displayed on the main form. If I add a fourth complete row of data, I'd want the information on the main form to change to show the new data in fields 2, 5 and 6 of row 4, rather than row 3. If I use DMax and DMin, the information won't change, unless the newly added information in row 4 is higher (or lower) than the information in the same fields of the other rows.

I'm thinking aloud, but wonder if used DMax to find the highest autonumber, but how could I "scroll" across the row to get the data from the relevant field(s) from this highest autonumber row? That seems logical, but I don't know how/if it can be implemented.

 

[Juan]

Just after posting my reply, I saw DFirst and DLast in the help facility. That might just be what I want!

 

[spacepro]

Juan,

Take a look at this microsoft help article:

Article - First and Last Record

I suppose you could always use DAO/ADO to retrieve the info you want.

See if the above info is any use.

Andy

 

[Juan]

Thank you Andy. That article gives a very nice extension to what I need to do. I hope that others find it useful too.

 

[spacepro]

Glad to Help and welcome to the forum Juan..

Andy