Run Update only on changed records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Sorry for that empty page.

I have a form with a subform on it - the subform's data is a datasheet. I
would like to change the pertinant records in the filtered data on the
datasheet and run an update query that only updates the records in the set
that have been changed. Can I do this? How and if yes could you please give
me the syntax.

Thanks very much in advance.
 
Bonnie said:
Sorry for that empty page.
Click to expand...
You're fired.
I have a form with a subform on it - the subform's data is a datasheet. I
would like to change the pertinant records in the filtered data on the
datasheet and run an update query that only updates the records in the set
that have been changed. Can I do this? How
Click to expand...

When you move off of a record in Access, that record is saved immediately.
So, you're options are to:
1. Run the Update query in the After Update.
2. Record the ID of the record that was changed (in memory or another table)
3. Set a flag on the record that it was changed. (like a checkbox)

Method 1 fires everytime you save a record. 2 & 3 you can wait until all the
saves are finished, then do the update query one time.
and if yes could you please give me the syntax.
Click to expand...
Conceptual design is free, hands on dirty work... not so much.
Thanks very much in advance.
Click to expand...
yw.
 
Sorry for that empty page.

I have a form with a subform on it - the subform's data is a datasheet.
Click to expand...

Well, to be more precise, the subform's data (like all forms' data) is
a Table. A datasheet is a display tool.
I would like to change the pertinant records in the filtered data on the
datasheet and run an update query that only updates the records in the set
that have been changed. Can I do this? How and if yes could you please give
me the syntax.
Click to expand...

This won't be particularly easy! Access doesn't record or remember
which records have been changed and which haven't. Would it be
possible to change each record in the subform's recordsource table as
it's being edited? What determines what's "pertinant"? What are you
trying to update?

John W. Vinson[MVP]
 
It is inventory and I am logging out different items by noting who logs them
out, how many and what the date is - the point of the query is to update the
master table by decrimenting the quantity on hand. Of course, I only want
that to happen to the records that have been changed. If I run the query
against everything it will decriment what we took out an hour ago a second
time so I only want it to update the current record containing the inventory
that has been logged out. Phew - that's a mouthful.

I'll go try what S. Clark suggested and fire the update query on each record
immediately after update.

Thanks for your help.
 
That sounds like what I am looking for. Thanks very much. You'll hear from
me if I have problems. If not ... until the next time...
 
Hi Steve,

I'm still struggling with this problem. How do I "save the ID" of the
record to update later? I have a unique itemno in the field if that would do.

Thanks again.

Bonnie
 
Back
Top