Event of closing a form or navigating away

[guangdew via AccessMonster.com]

I need to use some code to update some tables after entering a new record or
editing an existing record. I need to know what event I should use. I know
after entering a new record, we can navigate away from current record, or the
form can be closed. I don't what else can happen to get away from current
record. Please give me some suggestions regarding how we should catch these
events.

Thanks in advance,

Guangdew

 

[John W. Vinson]

I need to use some code to update some tables after entering a new record or
editing an existing record. I need to know what event I should use. I know
after entering a new record, we can navigate away from current record, or the
form can be closed. I don't what else can happen to get away from current
record. Please give me some suggestions regarding how we should catch these
events.

Thanks in advance,

Guangdew

The Form's BeforeUpdate event will be executed when the user takes any action
which would save the record, and it can be cancelled by setting its Cancel
operand to True.

 

[guangdew via AccessMonster.com]

Thank you. This is the exact event I was looking for.

Guangdew

I need to use some code to update some tables after entering a new record or
editing an existing record. I need to know what event I should use. I know

[quoted text clipped - 6 lines]

Guangdew

The Form's BeforeUpdate event will be executed when the user takes any action
which would save the record, and it can be cancelled by setting its Cancel
operand to True.

 

[guangdew via AccessMonster.com]

I'm working on this event then I found a new problem. Because what I need to
do is on the subform so I can have the BeforeUpdate event to check only the
subform. The problem is, whenever I click somewhere outside the subform, the
Beforeupdate event will fire. This will cause extra effort to do the
programming if we know the event may fire many times before we formally
finish entering data. I don't know whether we can have some way to prevent
this from happening.

Guangdew

I need to use some code to update some tables after entering a new record or
editing an existing record. I need to know what event I should use. I know

[quoted text clipped - 6 lines]

Guangdew

The Form's BeforeUpdate event will be executed when the user takes any action
which would save the record, and it can be cancelled by setting its Cancel
operand to True.

 

[John W. Vinson]

I'm working on this event then I found a new problem. Because what I need to
do is on the subform so I can have the BeforeUpdate event to check only the
subform. The problem is, whenever I click somewhere outside the subform, the
Beforeupdate event will fire. This will cause extra effort to do the
programming if we know the event may fire many times before we formally
finish entering data. I don't know whether we can have some way to prevent
this from happening.

Possibly; but it's not clear from your post! What constitutes "finishing"
entering the data? The subform must save each record as it goes along; if you
must have certain data filled out on the mainform, and some number of records
saved on the subform, before the entire package of records is "finished", it
can get pretty difficult. One solution is to bind the subform to a
"scratchpad" temporary holding table, and run an append query to migrate the
data into the "real" table when the entire package is complete.

What are the tables involved? Why must you leave the subform repeatedly in the
process? What constitutes completion?

 

[guangdew via AccessMonster.com]

Hi John,

It's not that I have to leave the subform repeatedly. The user may click
somewhere on the main form during the time entering subform. Also, after the
data entering is finished, double clicking Total textbox on the main form
will trigger the double click event to calculate the total.

On the subform, all of the textboxes are bound, so the data will be saved
when they are entered. But other tables need to be updated after the form is
finished. I want to use a program to do this work.

If there is potential that the update may happen a few time, then I will have
to check the tables to see whether the information has already been there
before doing the update. There may be some better way to do it, please advise.


Guangde

I'm working on this event then I found a new problem. Because what I need to
do is on the subform so I can have the BeforeUpdate event to check only the

[quoted text clipped - 3 lines]

finish entering data. I don't know whether we can have some way to prevent
this from happening.

Possibly; but it's not clear from your post! What constitutes "finishing"
entering the data? The subform must save each record as it goes along; if you
must have certain data filled out on the mainform, and some number of records
saved on the subform, before the entire package of records is "finished", it
can get pretty difficult. One solution is to bind the subform to a
"scratchpad" temporary holding table, and run an append query to migrate the
data into the "real" table when the entire package is complete.

What are the tables involved? Why must you leave the subform repeatedly in the
process? What constitutes completion?

 

[John W. Vinson]

Hi John,

It's not that I have to leave the subform repeatedly. The user may click
somewhere on the main form during the time entering subform.

Well... the subform data will only be saved if the user sets focus to some
bound control on the mainform; a random click on the background won't do it.

Also, after the
data entering is finished, double clicking Total textbox on the main form
will trigger the double click event to calculate the total.

Ummm... why is a doubleclick needed?? A calculated field should reflect the
current sum; at the worst you could recalc or requery the textbox in any
subform event which would affect the total. You're not STORING the total in
the table, I hope?

On the subform, all of the textboxes are bound, so the data will be saved
when they are entered. But other tables need to be updated after the form is
finished. I want to use a program to do this work.

To be precise, the data will NOT be saved after each textbox is filled; it
will only be saved when the entire subform record is saved, i.e. by moving to
a different record or by leaving the subform.

If there is potential that the update may happen a few time, then I will have
to check the tables to see whether the information has already been there
before doing the update. There may be some better way to do it, please advise.

The process of updating the other tables will need some sort of signal from
the user indicating that they're done. If you're doing calculations on the
data in these tables, to store the calculated values in some OTHER tables, you
probably should not be doing so at all; please explain! Here's my standard
blurb on the subject:

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or in the control source of a Form or a Report
textbox.

 

[guangdew via AccessMonster.com]

Hi John,

Thank you for your detailed advice.

Well... the subform data will only be saved if the user sets focus to some
bound control on the mainform; a random click on the background won't do it.

This form is sales. The main form has customer information and total and the
subform has the detailed sale items. Double click Total will trigger the
calculation.

Ummm... why is a doubleclick needed?? A calculated field should reflect the
current sum; at the worst you could recalc or requery the textbox in any
subform event which would affect the total. You're not STORING the total in
the table, I hope?

I did have the Total in the Sales table, so the Total on the main form is a
bound control. I thought it's a convenient way to do originally. Should I do
it this way: Set the Total still on the main form but make it unbound. During
navigation, I'll use Current event to calculate the total; during data entry,
I will still use double click event to calculate the total.

The process of updating the other tables will need some sort of signal from
the user indicating that they're done. If you're doing calculations on the
data in these tables, to store the calculated values in some OTHER tables, you
probably should not be doing so at all; please explain! Here's my standard
blurb on the subject:

As I mentioned, this form is Sales. After the entry is finished, I want to
update the inventory table to deduct whatever is sold. Should I do it this
way and how should we trigger this update? By using BeforeUpdate event, every
time we double click the Total on the main form, it will perform the update.
Please advise how I should do it.

Thank you again for responding to my post, I learned some new things.

Guangdew