When to run an update query from a form?

  • Thread starter Thread starter Tony Williams
  • Start date Start date
T

Tony Williams

I have a form is used to input data and when the user has input a record we
need to run an update query to update another field in the table. The user
may be inputting numerous records. Where is the best place to run the query,
after each record and if so would that be in the AfterUpdate property of the
form or when all records have been input and use the OnClose property? Or
should we consider a command button on the form that the user clicks on at
the end of the input for a record before they move onto the next?
Any help would be appreciated.
TIA
Tony
 
Tony said:
I have a form is used to input data and when the user has input a
record we need to run an update query to update another field in the
table. The user may be inputting numerous records. Where is the best
place to run the query, after each record and if so would that be in
the AfterUpdate property of the form or when all records have been
input and use the OnClose property? Or should we consider a command
button on the form that the user clicks on at the end of the input
for a record before they move onto the next?
Click to expand...

Do this in the BeforeUpdate event of the form. I wouldn't us a command
button, as that's likely to just annoy the user.
 
I have a form is used to input data and when the user has input a record we
need to run an update query to update another field in the table.
Click to expand...

Ummm...

Why?

It sounds like you're storing derived data. If the value you're
storing can be calculated from the value the user enters, it probably
should not be stored in the table AT ALL; simply calculate it when
it's needed.

Am I misunderstanding? What is the nature of this update query?

John W. Vinson[MVP]
 
Thanks Joan good idea
Tony
Joan Wild said:
Do this in the BeforeUpdate event of the form. I wouldn't us a command
button, as that's likely to just annoy the user.
Click to expand...
 
Long story John, basically the database currently holds quarterly data and
in the future the users will key in YTD data. As all the reports are set up
to handle quarterly data I'm having to take the YTD data, calculate the
quarterly data then update the original quarterly field. Very messy I know
but this is a quick fix before we re write the whole database. Joan Wild
says use the Before Update of the Input form, would you agree?
Thanks
Tony
 
Joan Wild says use the Before Update of the Input form, would you agree?
Click to expand...

I'd fix it, but I sympathize - and I fully agree with Joan.


John W. Vinson[MVP]
 
Back
Top