OnDirty or After Update? (1 Viewer)

[AlexN]

Hi there,

Every time a new record of a bound form is saved, a small piece of code in the AfterUpdate event of the last control of the record, adds a new record to another table (that means apart from the new record on the table the form is bound).
Where (in which event) do I put the code to update both records (in both tables) whenever a saved-existing record is changed?
I think I should put the existing code in the OnDirty event of the control and put the new code in the AfterUpdate event but I’m not sure about it, and it seems I’m not perfectly aware of when these two events fire.

I don’t want to use a command button for this.

Any ideas?

Thank you

 

[sneuberg]

I've played with the On Dirty event in a form by putting

Msgbox "On Dirty"

in it and found that it only fires once during the modification of a record and fires on the key press event. So any code in the on dirty event would only capture the first keystroke and any other changes made would be missed.

 

[AlexN]

I've played with the On Dirty event in a form by putting

Msgbox "On Dirty"

in it and found that it only fires once during the modification of a record and fires on the key press event. So any code in the on dirty event would only capture the first keystroke and any other changes made would be missed.

Thank you for your answer.
It's obvious then that I can't use the On Dirty event of the control to run the code appending the record to table B.

Keeping in mind that existing records in table A are subject to change, thus affecting relevant records in table B, what would be the proper event to put the code in (first for appending relevant record in table B and then - if change occurs - for updating the record in table A and relevant record in table B)?

 

[moke123]

how about the Form_BeforeUpdate event. You can do a test for new record to insert a record to tblB or if not a new record do an update to tblB.

 

[sneuberg]

The afterdate event fires on both undates and inserts so why not use it for both cases. Couldn't you determine whether to do an insert or update to tblB by doing a DLookup of something in tblB.

Could you tell us something about the data in these tables? Maybe these updates and inserts could be avoid by a change in structure.

 

[moke123]

The afterdate event fires on both undates and inserts so why not use it for both cases. Couldn't you determine whether to do an insert or update to tblB by doing a DLookup of something in tblB.

i think the form retains the new record flag until the record updates so testing for me.newrecord in the before update should work to determine whether to use an insert or an update. once the record is committed i dont think testing for new record would work anymore.

if me.newrecord then
' use an insert sql statement
else
'use an update sql statement
end if

edit: i tested it and it seems to work.

Maybe these updates and inserts could be avoid by a change in structure

i tend to agree

 

[AlexN]

Thank you all for your answers.

I finally adapted moke's and sneuberg's suggestions putting both parts of the code in the After Update event of the control, checking if there's is a new record.

Thanks again, you've all been kind and helpful.