When exactly are Insert events firing? 1

[jamaarneen]

Hi,

I am getting sometimes a little confused about when exactly are the BeforeInsert or AfterInsert events of a bound form firing?

Thanks, Ja

 

[JoeAtWork]

BeforeInsert - just before the record will be inserted in the table. It gives us a last chance to do validation and cancel the insert if we so wish.

AfterInsert - occurs right after the record was inserted - it's now in the database. We use it whenever something needs to be done right after a record is inserted.

 

[jamaarneen]

Thank you, but what I mean is, what does the user have to do, that access should automatic insert a record?

I'll be more specific with my problem.
I am talking about a bound form that its 'DataEntry' mode is set to true.

When I put the first character in a bound textbox, the BeforeInsert fires, but if I get the values from another form, and then the values are added by code - not manually, then the BeforeInsert events never fires, and however, the record is added to the table.

So the question is, which event does fires in this case?

Thanks, Ja

 

[TheAceMan1]

How are ya jamaarneen . . .

[blue] . . . what does the user have to do, that access should automatic insert a record?[/blue]

The firing of the events (starting with Before Insert), [blue]specifically occurs when you first edit a new record![/blue]

In a continuous form you can visually see this. As you enter your 1st character in a new record [blue]you'll see the new record line popup right underneath![/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[jamaarneen]

Hi TheAceMan1, Thank you, I'm fine. And ja?...

well, my real problem is, if the user don't edit manually:

but if I get the values from another form, and then the values are added by code - not manually, then the BeforeInsert events never fires, and however, the record is added to the table.
So the question is, which event does fires in this case?

Thanks in advanced
Ja

 

[TheAceMan1]

[blue] . . . [purple]and then the values are added by code[/purple] - not manually, . . .[/blue]

How are you adding by code?

If your adding by query or sql obviously no trigger occurs.

If your adding thru VBA with something like:

[blue]   Me!TextboxName = Forms!Formname!Textbox[/blue]

Then the events should fire!

[green]Don't forget, the events only fire the first time a new record is edited! Thats thru VBA or manually.[/green]

Indications are that your adding thru query/SQL since the events don't occur.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[jamaarneen]

OK, your few lines, have told me a lot, and therefore: Thanks.

But:

1)

How are you adding by code?

it's actually both. I mean that I'm adding it thru VBA (ye, something like your example), but it's in another form, where then refresh my first form...
let's say form1 id to make an order, where the user can call form2 to choose a costumer from a list. in the afterupdate of a combebox in form2, the following occurs:

Forms![i]form1[/i].[i]fieldname[/i] = Me.con_contactID
[code]Forms![i]form1[/i].Refresh

that means, that this way of doing it, is called SQL/query...
I also realized, that if I return to form1 without the 'refresh' and then close form1, no record is added at all..

2) my real problem is...
I am adding an empty record (only with date in it) as form1 is loaded. What I want is, that after I select a record in form2, his ID should be entered in the new record that was already added when form1 was loaded. but in fact, access is adding a new record... (I mean with the 'refresh' line)

and 3)

Don't forget, the events only fire the first time a new record is edited! Thats thru VBA or manually

did you mean the first time edited?
or the first time a new record? (so the second new record will not trigger an event, what seems odd)

Thanks a lot, Ja

 

[TheAceMan1]

jamaarneen . . .

I see what I need but have to ask a breaking point question:

When you open form1, does the newly added record (with date) show the pencil icon

on the record selector?[/blue]

And to quickly answer 3): [blue]Thats each time a new record is entered on the new record line! . . . [/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[jamaarneen]

you're right... I don't see the pencil icon... (I had first to set the 'record selector' property to true...)
so that means, that the form1 is now finish with the record added in the 'load' event, and the form1 is ready for adding another new record. that's what I'm thinking.
but the question is: why? could it be because the 'load' event is to early? should I rather add the new record in a later event?

In the mean time, I'm trying to find my way, avoiding the 'adding' at the form' opening...

 

[TheAceMan1]

[blue] . . . so that means, that the form1 is now finish with the record added in the 'load' event, and the form1 is ready for adding another new record[/blue]

Close . . . but not quite.

Your [blue]On Load[/blue] event is setting the [blue]Default Value[/blue] property of your date field. Default Values only occur on a new record line and [purple]do not trigger edit mode.[/purple] Look at it as a new record line with a [purple]preset[/purple] for your date field. When you edit this new line you'll see another new line popup for continuing the addition of new records with the same preset. If you set the Default Value with something like [blue]=Now()[/blue] and format the date to show Date & Time, you see the time change as enter new records. You can read all about by putting the cursor on the property line and hitting F1. I hope this is clear.

[ol][li]Getting back to the real content, remove the refresh line as it saves as well![/li]
[li]Your using VBA not query/SQL to update the customer from form2.[/li][/ol]
Present state of affairs should now have you back in form1 with customer updated, ready for you to fiishing editing the rest of the record.

Be aware: when you update the customer you'll be updating the record where you see the record selector icon

. This should be on the new record line.

[blue]Your Thoughts?[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[jamaarneen]

Thanks
(and sorry that I response by now - my location is europe, so when your posting after 16:00 it's here after 22:00 when I'm happily on my way home...)

first I want to thank you for your explanation - I love explanations...

Your On Load event is setting the Default Value property of your date field. Default Values only occur on a new record line and do not trigger edit mode. Look at it as a new record line with a preset for your date field

not exactly. it is not a default value, I'm assigning the value explicitly by code (see further)
And now I realized, that the form1 gets open, the textbox bound to the datefield - is empty, while in the table, the first record does have a date value. this brings me again to my assumption that "that the form1 is now finish with the record added in the 'load' event, and the form1 is ready for adding another new record".
the load event calls a sub - here is some of the sub adding a new record:

Set rs = CurrentDb.OpenRecordset(strTableName, dbOpenDynaset)
    With rs
        .AddNew
        .Fields(strFieldName) = varValueToAdd
        .Update
    End With

next,

When you edit this new line you'll see another new line popup for continuing the addition of new records

My form isn't a continuous form, so I can't see a new line popping up...

can read all about by putting the cursor on the property line and hitting F1

Which property line do you mean?

Getting back to the real content, remove the refresh line as it saves as well!

If I remove the refresh line, the values added in form2 will not be entered in the already existing record, and that's annoying.

Your using VBA not query/SQL to update the customer from form2.

I have put a "message" in the "BeforeInsert" event (not "Update") to test when it fires.
with the refresh , I am actually getting 2 new records as described above. and the funny thing is, that I don't get any message.
So according to your writing (in thread 6), if it inserts by VBA it should fire. and that makes me come to conclusion, that both inserting in the load event, and inserting by assigning values in form2 (+refresh) - will behave like an SQL/query.
a little long - but at least clear?...

And again, thanks a lot, Ja

p.s., Are there any HOT KEYS to insert the TGML tag coding???

 

[TheAceMan1]

jamaarneen . . .

The way the thread was going I hadn't counted on a recordset! Rereading this thread I've extracted the following secnario you present:
[ol][li]I am talking about a bound form that its [blue]DataEntry[/blue] mode is set to true. I somehow missed this one. In [blue]DataEntry[/blue] mode the [blue]Before/After Insert[/blue] events don't fire until you parse thru to go to a new record (using Tab or Enter), or you click the [blue]New Record[/blue] button

.[/li]
[li]I am adding an empty record (only with [blue]date[/blue] in it) as form1 is loaded:

[blue]   Set rs = CurrentDb.OpenRecordset(strTableName, dbOpenDynaset)
      With rs
          .AddNew
          .Fields(strFieldName) = varValueToAdd
          .Update
      End With[/blue]

This is your biggest problem! Your adding a record directly to the table, which accounts for your second newly added record when [blue]Refresh[/blue] is performed. Since this date will most likely be used for all new records in this session, you could relieve the problem by setting the [blue]Default Value[/blue] property:

[blue]   Me![[purple][B][I]strFieldName[/I][/B][/purple]].DefaultValue = "=Date()"[/blue]

[/li]
[li]Forms!form1.fieldname = Me.con_contactID [green]'Update by 2nd Form[/green]
This is fine![/li][/ol]
That should do it!

At this point I think it would be a good idea for you to [blue]empower[/blue] yourself with knowledge of the events. To find the event info, in form design view put the cursor on a form event line of interest and hit [blue]F1[/blue].

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[TheAceMan1]

[blue]p.s., Are there any HOT KEYS to insert the TGML tag coding???[/blue]

No!

I'm currently using Mouse and Key Recorder V 6.3 to do just that

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[jamaarneen]

Thank you AceMan1! you'r great

Indeed, I really need to empower my knowledge of events (and not only events...)
Altough I am hitting the F1 frequently, I did'nt really learned the events consistently...

My target is to create a (bound) form where only ONE new record can be added and edited.
And, I really don't want to get any messages from Access because of missing requested fields... (the end user will get frightened...). So I want to control it...

So now I'm thinking, that it's either a simple 'DataEntry' form (without any OnLoad action) and deal with it..., either 'DataEntry' to false and entering a new record in the 'OnLoad' (and filtering the form's record source to the new empty record).
Am I still complicating simple things?...

(I was thinking to work with an unbound form - what seems to me possible - but rigth now it's seems for me to difficut too...)

I must admit, my experience is very poor - this is my second project (the first one was a tiny one). several years ago I studied (not practiced :-(...) VB, Database and SQL basics - NOT access. now I'm trying to teach my self on real... my time for this is very little...

So I really appreciate every bit of help/advise. Therefore - again - Thanks!!!
Ja

 

[TheAceMan1]

jamaarneen . . .

In my opinion the easiest would be [blue]Data Entry[/blue]. If you set the forms [blue]Cycle[/blue] property to [blue]Current Record[/blue], the user can't save until they hit a save button you'll add in the forms footer! The button code would be:

[blue]   DoCmd.RunCommand acCmdSaveRecord
   DoCmd.RunCommand acCmdRecordsGoToNew[/blue]

You could also use the forms [blue]Before Update[/blue] event to validate, allowing the user to go back and make changes.

I have to move on to other posts. If anything comes up you know what to do . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[jamaarneen]

Thanks again & a lot...
for your explanations and your patience...

I will sure take your advise, and try to go on with 'Data Entry' and 'Cycle = Current record'.

All the best,
Ja