Last Modified date on a form with subforms

[mindbender]

I have a form with 4 subforms on it and I am trying to find a way to capture the last modified date for the record regardless of what form or subform they touched

Is this possible?

Right now I have a macro on before update of Now() on the form and all of the subforms but that is only showing when that form was last modified whcih is not exactly what I am trying to achieve.



Thanks

Bret

[June7]

Don't understand what you want to achieve.

Sounds to me like the macro is accomplishing the requirement.

[mindbender]

I guess I was not clear - happens


I am wondering if there is a way to have a field on the main form that stores the value of the most recent last modified date from all of the the other forms.

Example

sub form activity was last updated june 1
sub form opportunity was last updated july 1

field on the main form would show july 1

[June7]

You want to know the latest date any record was edited? Where would these dates come from? Is there a DateModified field in the subform(s) RecordSource?

[mindbender]

The database has about 500 records and has 1 main form and 4 subforms and every one of them has a last modified date field that gets updated to Now() by a macro.

I woudl like a field on the main form that shows the latest date the record was modified.

Basically look at the last modified dates of all the sub forms and put the latest date in the last modified date field on the main form.

Each form has its own table and they are all linked in a 1:1 relationship based on ID

[June7]

Small database. Can you provide it for analysis? Follow instructions at bottom of my post.

[mindbender]

Thanks for taking a look - I uploaded the db......In addition to the last modified date I am also trying to export all the records in the database based on this last modified date as well if you could help with that.

Basically once a wekk export the database so only 1 row per account is exported with the data from the most recent date.

[June7]

Why don't you have primary key designated on each table?

If tblMain has a 1-to-1 relationship with each of the other 4 tables, then why are there more records in some of the related tables than are in tblMain?

Try this:

Build a UNION query that retrieves all Account numbers and dates into a single dataset. There is no wizard for UNION, must type or copy/paste into SQL View window of query designer.
SELECT Account, [Last Modified Date] FROM tblMain
UNION SELECT Account, [Last Modified Date] FROM tblActivities
UNION SELECT Account, [Last Modified Date] FROM tblOpties
UNION SELECT Account, [Last Modified Date] FROM tblXeroxContacts
UNION SELECT Account, [Last Modified Date] FROM tblOrigContacts;

Then in textbox on form, expression in ControlSource:
DMax("[Last Modified Date]","UnionTables","Account='" & [Account] & "'")

That expression could also be used in a query or use the UNION in a GROUP BY query:
SELECT UnionTables.Account, Max(UnionTables.[Last Modified Date]) AS [MaxOfLast Modified Date]
FROM UnionTables
GROUP BY UnionTables.Account;t
then include the GROUP BY in a query that joins to tblMain along with the other related tables if wanted.

The alternative would be VBA code.

[mindbender]

I put in what you had sent and I get #NAME? in the last modified date text box. If I put an = in front of the statement I get#ERROR on the ones with records in the subforms.

I did not put promary keys on the tables because I am not 100% sure that each Account ID is Unique. There is one account but each account can have multiples of every subform (more than 1 contact, activity and opportunity) I may have misspoke on the 1:1 relationship.

I appreciate your help on this

[mindbender]

I figured it out - I did not save the query the same name as you mentioned. It is working now.

Thank you so much

[mindbender]

1 last thing - how can I get the text field that shows the Max Date to refresh automatically without having to hit Shift+F9 or will it update on its own after a while

[mindbender]

also when I created the export query - what seems to be happeing is it is pulling all of the activities for the account and giving them all the same last modified date rather than just pulling the account and the activity (or opty or whatever) with the lastest modified date.

I added a screenshot fo what is happening

[June7]

The GROUP BY query is acting correctly as designed because you included other fields in the grouping criteria. I did not suggest a group by with this join. The GROUP BY was only to determine the max date for the account, joining that query to the table will show the max data value on each record with the associated account. Isn't this what you want?

[mindbender]

The Group By is working perfectly for showing the last modified date on all of the forms/subforms. I tried to use it on the export which is a different process....basically what I am trying to do is return 1 row per account Id and have the additional fields (all are from subforms) showing the most recent data. So if there was a contact added on may 1st and an activity on May 17 and an opportunity on may 3 (and those were the msot recent dates for all the records in the sub forms) then display this data along with the account Id in 1 row.

Maybe i am trying to do the impossible.

I really appreciate your time though. you have been a huge help

[June7]

So you want only the related data associate with the MaxLastModified data?

Modify the UNION query to include all the fields you want to show.

Then join the UNION query to the GROUP BY that returns the Max date.

Then criteria under the LastModified date would be criteria to match MaxLastModified. Try: =[MaxOfLast Modified Date] Or Is Null