How to perform an action when a new record is created?

[Seth Schrock]

I have a feeling like I should know this, but I can't think of anything. I'm trying to create a file management portion to my database (which tracks loans) so that every document that is associated with a particular loan will be readily accessible. My plan is to create a folder for each record using the PK as the folder name so that it can be found relatively easily using Windows Explorer. I would like for the folder to be created when the record is created. However, I can't think of what to test for to know when the record gets created. I don't want to check for the folder's presense in the form's AfterUpdate event as that will trigger frequently and there is no need to do so each time. I'm looking more for a NewRecordModifi ed property. I suppose that I could use the form's OnCurrent event to set a flag that the current record is a new record. Then in the form's AfterUpdate event I could test to see if the flag is set to NewRecord and then create the folder. However, that seems more complicated than it should be.

[zmbd]

Before insert event fires before the record is even inserted into the table.
After insert event fires after the record has been inserted into the table, yet all but the autonumber fields have been populated:
Order of events for database objects (V2003) (Sorry, just realized this is an older link; however, I've found most of the event order is still valid. I'll have to find a V2010 now :) ) In any case one of these events should help.
If you use the on current event then you should check if you are in a new record or an existing record.

[Seth Schrock]

The autonumber field displays the value even before the BeforeUpdate event of the form runs, so even if it hasn't been inserted into the table (which surprises me), I can still reference the control to get the value.

So you think that the flag idea is a good one?

[zmbd]

I to think a tad on your case.
I would use the before update event.
Present a confirmation message to the user to confirm the changes.
You don't need a flag to check to see if the current record is a new record, you already have that in the form properties (frm.NewRecord) and if so then run the sub-code to create the required information and the folder etc... You should be able to refer the value in the control for the PK on the form at this point.

This way, if the user cancels or aborts then no new folder.
If the record is an existing record then your conditional wont run the code to make the folder etc....

[TheSmileyCoder]

It might help if you consider the "insert" event as the part in which access reserves a row for your record. Its during the insert that access will reserve a autonumber for the record. Once the insert event has run, an autonumber will be available for use, and will not accidentally be used by someone else.

[TheSmileyCoder]

I have a similar file system. Note that if the user wants to OPEN any stored document, I have code that copies the document to a working folder, so that the user never works on the original document. Of course whether this is relevant or not, depends a bit on whether you want updates to documents to be stored.

[Seth Schrock]

I do want changes to be allowed and saved to the server location. Most of the time the documents will be PDFs so that part won't be a factor, but the few doc files will need to be edited.

So the frm.NewRecord remains true until the record is first saved (between the before and after update events)? I didn't think about that being a possibility.

[TheSmileyCoder]

Me.NewRecord will return true when you are on a new record, that has not been saved first time yet. Me.NewRecord will also return true if the insertion has not yet started.

[NeoPa]

Unlike most Before_ and After_ type events, Before_Insert and After_Insert do not come around a single event. Before_Insert happens at the point where a record is being prepared (and has become dirty), but also where the action is still possible to be canceled. After_Insert, on the other hand, occurs only after the new record has been saved and thus inserted into the table. It seems to me that the trigger you're looking for is this After_Insert event.

[TheSmileyCoder]

Thanks NeoPa. It would seem I that event wrong in my head. I always assumed that the After_Insert would run immediately after the Before_Insert (provided it was not cancelled). I was even so sure I had to draw up a test form and test the order of those events to be sure, but you were absolutely right.

So AfterInsert is an event that only happens occurs for new records, AFTER they are saved. This is the order of events as tested:
(On dirty)
Form_BeforeInse rt

(On save)
Form_BeforeUpda te
Form_AfterUpdat e
Form_AfterInser t

and, as written above, the Insert events only occur for NEW records.


With that said, I would argue that the Before_Update would still be the event to use, since you might not wish to save the record if the filecopy fails for some reason.

[zmbd]

New Record Order of Events.

From the link I posted in #2:

Creating a new record

When you move the focus to a new (blank) record on a form and then create a new record by typing in a control, the following sequence of events occurs:

Current (form) → Enter (control) → GotFocus (control) → BeforeInsert (form) → AfterInsert (form)

The BeforeUpdate and AfterUpdate events for the controls on the form and for the new record occur after the BeforeInsert event and before the AfterInsert event

I'm going to change my mind here and go with that AfterInsert event as I was originally trying to steer Seth to in my post.... frankly I was distracted by that drag-n-drop question! :)

[NeoPa]

Smiley:
I always assumed that the After_Insert would run immediately after the Before_Insert (provided it was not cancelled).

Absolutely. Me too. It seems logical - but nevertheless isn't so :-( I was as surprised then as you are now.

I'd generally go with the _AfterInsert myself, but now the facts are all detailed and out in the open I'm sure whoever reads this can make their own minds up based on their particular requirements.

[Seth Schrock]

I have never played with the Before/After Insert events before. I guess I always assumed that they meant inserting a value into the control, but I gather from the conversation here that it is the inserting of the values into the table (saving the record). I will look into these events more. Thanks everyone for helping out with this.

[NeoPa]

Actually Seth, the _BeforeInsert and _AfterInsert events come around different events entirely. The _BeforeInsert event is before the record buffer is prepared for entering a new record (not updating an existing one) and the _AfterInsert event occurs after (new record only) has been saved away.

Not only is _AfterInsert not guaranteed after a _BeforeInsert, but the operator gets control between these two events.

[Seth Schrock]

Well, at least I was right that I needed to look into them more :)