Undo all changes in session

[hermanko]

Hi,

I have a form, "frmDocumentBrowser", and an embedded subform,
"sfrmVersionHistory", that displays certain information about a
database of documents (i.e File Code, File Name, File Location, etc),
and the user can make edits to the information in both the main form
and subform. Both are based on the same underlying table "tblDocList",
where the difference is the main form displays the most current
[Version] and the subform displays previous version's (i.e. [Version],
and [Last Modified] fields are different).

I have a button on the main form that is supposed to close the form and
undo all changes made during that session and closes the form (i.e.
from when the form was opened until that button is clicked). Initially
i used the .Undo method but realized it only undid the last change
before the focus was moved to another record. From the discussion
boards it was suggested to incorporate temp tables. This is the method
I am trying now but require help.

Upon opening the form "frmDocumentBrowser" i have in vb code:
DoCmd.CopyObject , "tblTemp", acTable, "tblDocList"
(this part is fine, as i know have a copy of my tblDocList before any
edits have been made)

Once the user makes edits, he has two options..click on a "save" button
or a "cancel" button. the save button is easy, as I just delete the
temp table and the edited records will be saved automatically once the
form is closed.

The problem occurs when the user clicks on the Cancel button, where I
want to revert back to before any edits were made, using the tblTemp
table. The code used for this event:

DoCmd.Close acForm, "frmDocumentBrowser"
DoCmd.OpenForm "frmAdminMenu"

'undo all changes by restoring from tbltemp
'DoCmd.SetWarnings False
DoCmd.CopyObject , "tblDocList", acTable, "tblTemp"
'DoCmd.SetWarnings True
DoCmd.DeleteObject acTable, "tblTemp"

I get the Error 3211, where the table could not be locked, due to a
process still in use for the table tblDocList. I realize my form and
subform have a recordsource of queries that are based on tblDocList
which might be causing this error. Also, i realize it isn't the
greatest idea to be deleting and recreating my main table like this
unnecessarily. Can somebody suggest a better approach? I would really
appreciate it!!!

Herman

 

[Michel Walsh]

Hi,


If you use unbound form, you can use a transaction, and roll it back to
cancel all changes.

If you use bound form, you may be able to use the suggestion presented in
ADH (Access Developers' Handbook), at Sybex, with Access version 2000 and
more recent. The technique is unfortunately a little bit too long to explain
here, and it is not something I do every day, I greatly prefer to refer you
to the books.



Hoping it may help,
Vanderghast, Access MVP

Hi,

I have a form, "frmDocumentBrowser", and an embedded subform,
"sfrmVersionHistory", that displays certain information about a
database of documents (i.e File Code, File Name, File Location, etc),
and the user can make edits to the information in both the main form
and subform. Both are based on the same underlying table "tblDocList",
where the difference is the main form displays the most current
[Version] and the subform displays previous version's (i.e. [Version],
and [Last Modified] fields are different).

I have a button on the main form that is supposed to close the form and
undo all changes made during that session and closes the form (i.e.
from when the form was opened until that button is clicked). Initially
i used the .Undo method but realized it only undid the last change
before the focus was moved to another record. From the discussion
boards it was suggested to incorporate temp tables. This is the method
I am trying now but require help.

Upon opening the form "frmDocumentBrowser" i have in vb code:
DoCmd.CopyObject , "tblTemp", acTable, "tblDocList"
(this part is fine, as i know have a copy of my tblDocList before any
edits have been made)

Once the user makes edits, he has two options..click on a "save" button
or a "cancel" button. the save button is easy, as I just delete the
temp table and the edited records will be saved automatically once the
form is closed.

The problem occurs when the user clicks on the Cancel button, where I
want to revert back to before any edits were made, using the tblTemp
table. The code used for this event:

DoCmd.Close acForm, "frmDocumentBrowser"
DoCmd.OpenForm "frmAdminMenu"

'undo all changes by restoring from tbltemp
'DoCmd.SetWarnings False
DoCmd.CopyObject , "tblDocList", acTable, "tblTemp"
'DoCmd.SetWarnings True
DoCmd.DeleteObject acTable, "tblTemp"

I get the Error 3211, where the table could not be locked, due to a
process still in use for the table tblDocList. I realize my form and
subform have a recordsource of queries that are based on tblDocList
which might be causing this error. Also, i realize it isn't the
greatest idea to be deleting and recreating my main table like this
unnecessarily. Can somebody suggest a better approach? I would really
appreciate it!!!

Herman