When is recordset updated?

[Carl]

Hi All,

When adding a new record it appears that a form's recordset is not updated
until AFTER the AfterInsert procedure completes. Does anyone know of a way
to know when the recordset has actually been updated? Even more importantly,
when the changes have actually been saved to the underlying database?

Particulars:
-Using an ADP with ADO recordsets.
-Form has no recordsource, its recordset is set at run time based on user's
selections.
-When New record selected an empty recordset is created and assigned to the
forms recordset property.
-Backend database is SQL Server 2000

When the user creates a new record they fill in the information for the
parent record. They can also select multiple values from a multi-select
listbox. When the new record is saved I need to save their listbox
selections to a child table.

However, in the AfterInsert procedure the parent recordset is still
reporting BOF and EOF indicating that Access has not saved the data to the
recordset. Once the AfterInsert has completed processing and control is
returned to the user the recordset no longer reports BOF and EOF.

Thanks for any insights,
Carl

 

[EJ Williams]

Carl,

I'm not 100% sure but I believe the Form_AfterUpdate event is raised once
the underlying recordset has been updated. When using bound forms in Access
I believe this is also the point where a Commit has taken place.

In contrast my understanding of the Form_AfterInsert event was that it was
raised when a new record was created (in memory?) to receive new data.

Eric

 

[Carl]

Hi Eric,

Thanks for your thoughts. According to Litwin/Getz/Guildroy and my
experimentation the form-level AfterUpdate event fires first followed by the
form-level AfterInsert. Neither the AfterUpdate procedure nor the
AfterInsert procedure see the new record in the form's recordset. Once
control comes back to the user interface I can get the new record from the
recordset.

So unfortunately, AfterUpdate doesn't seem to be sufficient here.

P.S. This is an Access 2002/2003 project.

Thanks again,
Carl

 

[Carl]

Hi All,

I found a solution to my problem and it is realy simple. First some
background.

What I really needed was to get the Parent Key so I could insert the child
records for the multi-select list box selections. My code to do this was
getting the value from underlying recordset. By simply changing it to get
the value from the form object like this Me!PrimaryKey I was able to save the
subordinate records.

Now, as to when the form's recordset is updated, it does appear to be AFTER
the AfterInsert. However, the *database* has been updated by the time the
AfterUpdate event fires. Furthermore, Access seems quite content with my
resetting its recordset in the AfterInsert event using code something like
this:

MyClass.PrimaryKey = me!PrimaryKey
Me.Recordset = MyClass.Recordset

(I chose not go this route because of design decisions I had already made in
the the class that supplies the recordset. Specifically, setting a new
primary key refreshes not only the Parent recordset but also the child
recordset used by my data-aware multi-select listbox.)

Hopefully others will find these insights useful.

Carl