Is there a better way to find most recent?

[revver]

I have a form/subform based on two related tables (1-many) in the usual way. The subform has a date field.

For a given record in the main form I want to find the (date of the) most recent detail record. My first thought is to create a RecordSet of qualifying detail records ordered by date descending. The first record in this RecordSet will be the one I want.

Now I think there is probably a better way than using a RecordSet. Any ideas?

 

[apr pillai]

Have a look at this link: Who canged the data?

 

[revver]

Thank you but my question is not about who changed data. I already use a technique described by Allen Browne for that.

My question is:
I have tblVehicles and tblServiced in a 1-many relationship. I want to find the most recent date that Vehicle_123 was serviced. This I can do by creating a RecordSet which contains all the records in tblServiced for Vehicle_123 and sorting that RecordSet in date order.

Is there an easier way?

 

[SydneyGeek]

A recordset is probably the way to go if you want to move to that record. If you just want to know which record it is, you could use a listbox on the main form whose rowsource is a Top 1 query of the subform, filtered to the correct vehicle and sorted descending on date.
Don't forget to requery the listbox when you switch records on the main form.

Denis

 

[revver]

Thanks Denis.

All I really want is the date.

I was debating whether to add a field to the main table and updating it each time a new service record is added.

The trouble has been that service records dated earlier than the most recent one have been trickling through and upsetting the apple cart. I would need to test the date to see if this service record should update the main table or not.

So I still haven't decided whether to add the field or not.

 

[SydneyGeek]

You could do the following:

1. In the subform, in Design view, add a textbox called txtLatest to the form's Footer. Make the Control Source an expression like
=Max([ServiceDate])
...or, use DMax if Max doesn't work.
2. In the main form, place a textbox. Its control source should be
=[The name of the container for the subform].Form![txtLatest]

You can find the container's name (it doesn't always match the name of the subform) by clicking the subform ONCE in design view, then looking at the Property sheet.

Denis

 

[revver]

Well Done Denis.

Didn't need DMax, Max worked fine.