Using a Subform's Recordsource as a Temporary Object

A

Altemir

I have a subform that I would like to initially populate with
pre-existing data based on a SELECT query. However, I also want the
user to be able to either add to or edit this data on the screen
without immediately affecting the underlying tables.

I'm trying to come up with a way where the user's data edits are
captured in some sort of temporary data object so that no permanent
changes are made until the user presses a button. In this way, the
subform will provide a sort of a temporary "play space" such that
UPDATES and/or INSERTS are not made to the underlying tables until the
user presses a button to accept the changes and/or additions he has
made.

Can anyone recommend a good way to do this? What kind of recordsource
object should I use?


Thanks in advance,

-- David
 
L

Larry Linson

You can do this with a temporary table (I'd recommend you visit MVP Tony
Toews' site, http://www.granite.ab.ca/accsmstr.htm, and take a look at his
example of creating a temporary database, in which you create the temporary
table, and then when done, delete the temporary database -- it will avoid
database bloat and save you some grief.).

Then you can either use VBA code or a cleverly-constructed Query or two to
update the original table. You'll be putting a lot of work into this to
allow the "batching" of the updates, but if it is required, yes, it can be
done.

Larry Linson
Microsoft Access MVP
 
D

Dirk Goldgar

Altemir said:
I have a subform that I would like to initially populate with
pre-existing data based on a SELECT query. However, I also want the
user to be able to either add to or edit this data on the screen
without immediately affecting the underlying tables.

I'm trying to come up with a way where the user's data edits are
captured in some sort of temporary data object so that no permanent
changes are made until the user presses a button. In this way, the
subform will provide a sort of a temporary "play space" such that
UPDATES and/or INSERTS are not made to the underlying tables until the
user presses a button to accept the changes and/or additions he has
made.

Can anyone recommend a good way to do this? What kind of recordsource
object should I use?
Click to expand...

I've usually done this by using a work table, either defined in the
current database or created in a temporary .mdb file, and binding the
form to that. I load the table with data from the "live" table in the
form's Open event. Later, as appropriate, I either replace the "live"
data with the updated "work" data, or I simply discard the "work" data.
 
Top