Update Parent form after subform updated? (1 Viewer)

[LucyF]

Parent form/table, with a subform (separate table, 1:M link on "ID")
The parent form data stays fairly constant (occasional change/update), with multiple entries in the subform for each parent record which are added to more frequently
The master table has a field "last interaction" which I need to update whenever a new entry is made in the subform/subtable
It updates fine when I change an entry in the parent table/form, but not when I add a new subentry for that parent

 

[Minty]

How is the update done on change in the parent form, and what code is not working on the sub form update?

 

[LucyF]

On the parent/Master form, the sub fields have an "After update" event which sets the field "LastUpdated" to Date()
I need to also update this same field whenever an entry is added to the sub-form/linked table

I tried the following in both the "after insert" and "after update" fields on the subform
=[Forms]![MasterForm]![Updated]=Date()

Unless anyone can think of a better way of doing it?
I need to link multiple sub entries to the master record but be able to report (by master record) when the last interaction was, or find those which haven't had an interaction in the last 6 months etc

 

[Minty]

Okay - Your terminology is a little confusing; when you refer to sub fields on the parent form I assume you mean bound controls on the main form? Can you post the after update code , and is it attached to every control on the parent form?

 

[LucyF]

sorry - yes, bound controls on the main form have the "after update" event set to update the "lastupdated" field to Date()
If I change an entry on the master form, it updates the "lastupdated" field as expected

However when I add a new entry to the subform it doesn't update the "lastupdated" field & I need it to - I'm not sure which field I need to set it to & how to do this ...

 

[RainLover]

For a Start I would suggest that you use before update event. Do this because the header may have its focus on a new or different record.

Waiting for the update code.

 

[LucyF]

I have no problem with how the master form/table updates. That is absolutely fine - I do NOT need help with that!
Pic 2 shows the "properties" of the field "status" which is a bound control/field on the master form - after update it updates the "updated" field correctly
Pic 1 shows the command that it runs

I am having problems with how to update the master table's "updated" field after I add a new record in the subform (tables linked via "ID")
Sorry, I don't know how else to explain!


(I can get the "lastupdated" field to update if I use the "on exit" event from the subform, which is ok as long as nobody clicks on the subform/childtable unless they wish to make an entry in it - it's far from ideal, as I really need it to only update when somebody adds a new child record ...)

 

[RainLover]

May I suggest a better method.

Keep the Main Form as is and update it only when that changes.

With the Child records do the same and update when required.

The balance of the work is done at Run Time. When you need this last update from the sub Records simply do a DMax on the sub records to get the date you need.

 

[LucyF]

Thanks for your help Rainlover & Minty
I will go & play around with events a little more - maybe I can work out a way of doing what I need it to do. I know I can make it work, but I guess I'm not explaining very well here what I'm trying to do

Thanks for your help, I *really* appreciate it!

 

[Minty]

Please don't edit the information out of your posts - It makes it mighty confusing to read!

To start with create a query that returns all the last Sub table Id's for each header ID from your sub-table. All it needs in it are the two ID fields and possible the date from the sub table.

This query can then be used in your reports link it to your top level data on the ID and then you have the last updated date. You don't need to store it in the top level data.

 

[spikepl]

The solutions offered are much too complex, IMHO.

In the subform's BeforeInsert event, make VBA or macro that updates the control in the parent form with Now() - this will update that control each time you insert a new record in the subform.

If you wish to update after each edit of an already existing record in the subform and also after each addition of a new record, then use instead the subform's BeforeUpdate.

If you also wish to record deletions, then guess which subform event?

 

[LucyF]

Sorry, I am not trying to make things more confusing, I was actually trying to make things clearer.

Please don't waste any more time on my problem - I *really* appreciate your help so far but I don't seem to be able to explain clearly what I need it to do ...
I'll keep working on it & I'm sure that over time I will be able to do what I need it to do

 

[RainLover]

Suggest you mark the Thread as Solved, if you don't want any help.