AfterInsert Event not Triggered as Expected on Pasted Records

[Mr Key]

Hi all!!
I found my in position to ask for your assistance!
I have a form(frmTable2, in DatasheetView) set to Append NewRecords to other tables and Update the Oldrecords as might be required.
On the AfterInsert Event I have the following Code!

INSERT INTO tblMytable ( ID, Code, SName, IDNumber, DateOfBirth)
SELECT tblTable1.ID, tblTable1.Code,tblTable2.SName, tblTable2.IDNumber, tblTable2.DateOfBirth, 
FROM (tblDepartment INNER JOIN tblTable1 ON tblDepartment.DeptID = tblTable1.DeptID) INNER JOIN 
tblTable2 ON tblDepartment.DeptID = tblTable2.DeptID
WHERE (((tblTable2.[SID])=[Forms]![frmTable2]![SID]));

AfterUpdate Event set the following Code

UPDATE tblDepartment INNER JOIN (tblTable2 INNER JOIN (tblTable1 INNER JOIN tblMytable ON 
tblTable1.Code = tblMytable.Code) ON tblTable2.[SID] = tblMytable.[SID]) ON (tblDepartment.DeptID = tblTable2.DeptID) 
AND (tblDepartment.DeptID = tblTable1.DeptID) SET tblMytable.[SName] = [tblTable2].[SName], tblMytable.[IDNumber] = [tblTable2].[IDNumber], 
WHERE (((tblMytable.[SID])=[Forms]![frmTable2]![SID]));

They are both works fine if you enter the data one by one!
AfterInsert Event Never triggered if you decide to paste the data(form on DatasheetView) instead of typing one record after another.
Is there any means of Iterating all fields so that to force the AfterInsert Event, even if you PASTE the data to the forms???

[Stewart Ross]

AfterInsert is only triggered when you manually insert records. Pasting records, as you have found, does not trigger this event.

Without knowing why you are pasting records it is difficult to advise you on this. There is no event I know of which you can pick up upon to advise that you pasted records, although other posters may have found a different approach.

In any case, I would advise that it is better to avoid manual cut-and-paste when you want to have additional control. Provide some means by which your users can select records to copy, then use a fully-programmed approach to copying and appending your records - allowing you to take whatever actions are necessary under program control at the time.

-Stewart

[NeoPa]

What a thoroughly confusing thread. It starts with a post saying the code in the AfterInsert event procedure is made up of two bits of SQL code, but that's impossible as event procedures must be VBA. It then goes on to say that the AfterInsert event isn't triggered when multiple records are pasted in, then strangest of all, Stewart agrees this is the case.

I don't know what's different between what you guys are seeing and what I have here, except maybe the Access version (I'm testing with 2003), but when I tried to duplicate this (very basic pasting into a table of multiple records from the clipboard) it worked fine for me. Each record inserted triggered both the Form_AfterUpdat e, as well as the Form_AfterInser t event procedures and both procedures had full access to the appropriate single record being processed. I'm testing in Datasheet view, just as stated quite clearly in the question. Obviously, I'm assuming the AfterInsert event being asked about is the Form_AfterInser t rather than one for any of the individual controls (which wasn't quite so clearly stated in the question) as I'm pretty sure that's the only one that would make sense in this scenario.

Normally I would just assume that neither of you had looked into it clearly enough, but as one of you is Stewart I don't feel that would be too bright an assumption on my part. We all make mistakes sometimes of course, but I certainly wouldn't be comfortable assuming that is the case just because I don't understand (yet) how it might not be.

[Stewart Ross]

Hi All. Well, I 'fess up - sometimes I get it wrong (I was thinking of VBA-based insertion here). AfterInsert is indeed triggered by a paste (which it would not be if you used a programmed approach).

Apologies for my error.

-Stewart

[Stewart Ross]

I also confirm after testing that A2007 behaves identically to A2003 as tested by NeoPa(as expected). The after update event for individual controls and the after insert event for the form itself is triggered normally by a paste append in datasheet view.

Making amends for my mistake earlier, I can see that the actual problem that is occurring results from the WHERE clauses of the two event procedures:

WHERE (((tblTable2.[SID])=[Forms]![frmTable2]![SID]));

The reference to [Forms]![frmTable2]![SID] will not work for a group of pasted records. It will be referring to the first of the records in the group only. When you paste the records the first of these is taken by Access to be the Current Record (the one which has focus, and therefore the record whose values will be returned when controls are being referred to in expressions).

Although the events ARE being fired, the code CANNOT refer to each record in sequence unless there is some value in a field somewhere that was common to all and could be used to identify the group of records pasted.

The On Current event is fired twice for a paste append of records, once before the paste (on the new record row in effect) and once after (on the same row with the new contents). It does not fire at all as the paste takes place until all the records have been pasted, although the individual After Update and After Insert events do.

Moral of this one for me: never assume, test it first!

-Stewart

[NeoPa]

Stewart's human after all :-D

Good job with the explanation too.

There was another thread posted by the same OP at a similar time which, though different and correctly posted separately, seems to be related to the same issue. I suspect the issue here is very similar to the one there too (which I guessed as being related to the updates all being completed before any updated data from the table is available).

There may well be an element (It's hard to know from what's been posted to be fair) of the logic for each record expecting and requiring the updates from the previously added records to be reflected in the data to work correctly. I hope that makes sense. The other thread, for interest, is DMax Function Not Responding on Pasted Records.

[Mr Key]

As Stewart explains, if you paste a single record, the AfterInsert event is trigered but if you paste multiple records at once only the first record is Appended to the Output table but the rest remains on the original table unnoticed!
In this design I set tblTable2.[SID]=[Forms]![frmTable2]![SID] to restrict record with focus only to be Appended to other table.
The reason for this is to prevent duplicates of data to the output table.
The AfterUpdate Event is set to allow users to be able to edit the records as they wish.
I have to have both AfterInsert and AfterUpdate on the same form.
As NEOPA point out, I face the same problem on the DMax functions on the thread Dmax Function Not Responding on Pasted Records
Please help!!!

[Stewart Ross]

You could try moving to the next new record after completion of your AfterInsert actions, by adding the following lines at the very end of your AfterInsert routine, replacing [Your First Control Name] with the real name of the control that should have focus after you insert a record:

DoCmd.GoToRecord , , acNewRec
Me![Your First Control Name].Setfocus

Although I have not for test purposes run a SQL update as you do, the AfterInsert tests I did successfully referred to each of the pasted records one after the other when the DoCmd.GotoRecor d was included. The SetFocus was required to maintain the normal operation of the standard insert if you were manually entering a record instead of pasting groups of records.

Please note that these lines must be placed at the end of the sub just before the normal exit of the routine. This approach will only work if you do the move to the new record as the very last action undertaken by your AfterInsert routine.

-Stewart