Undo changes in each record upon exit 2

[Hinfer]

I have a form that displays all the current 5 employees. How do I create a button so that I can exit the form and not save the changes. The problem is that each row is a record.

DoCmd.DoMenuItem A_FORMBAR, A_EDITMENU, A_UNDOFIELD, , A_MENU_VER20
DoCmd.Close A_FORM, "LkEmployees"

If I have changed 2 records then this will only undo the last record that I changed.

Is there some way to undo the changes in all the records. Access seems to save automatically when I move to different records. Can I disable this or is my code wrong?

I have tried Docmd.Close, , acsaveno but this again works for the last record changed.

 

[TTThio]

Hei,

one thing that is able to be done is,

in the form_beforeUpdate event, just write Me.Undo

All the changes won't be saved at all. In fact, just when you leave a record, any change made to previous record will be restored.

I'm not sure if this is what you want. You're right that Access save record automatically.

 

[Hinfer]

TTThio:

Thanks for replying. Sort of what I am looking for but I would like to be able to do this when the user clicks the cancel button. This button is allows the user to exit without making changes. As stated above, I have created a button wtih the code above and it will only undo the last record changed.

 

[TTThio]

Hi again,

if you just like to use a button,

then how about this,

call this routine in the controls' after_update event

Sub UndoUponExit()
If me.dirty then
me!btnUndo.enabled = True
Else
me!btnUndo.enabled = False
End if

Sub btnUndo_Click()
dim ctl as control

for each ctl in me.controls
If ctl.ControlType = acTextBox then
ctl.value = ctl.OldValue
End if

Next ctl

See if this is the one will work for you.

 

[Hinfer]

TTThi:

Sorry I am a novice user when it come to VB. Could you give me a little more guidance.

Where exactly do I place the code? In each control or the button? Or is it in the form? Am I making any sense?

 

[TTThio]

No problem.

The sub UndoUponExit is called on the control's AfterUpdate (whatever controls that might be changed by user)

The btnUndo_Click is called on the button's Click event.

Let me know if you have further question.

 

[LonnieJohnson]

Hello Guys, I just happened to read in. I thought I'd just throw in that the BeforeUpate or the AfterUpdate is only for the current record.

So...

If you have a continuous form.
You change record1.
You move to record2.
Record1 has been COMMITED to the table.
There is no undo for this record now.

Hinfer, am I not understanding the original question. ljprodev@yahoo.com
ProDev, MS Access Applications B-)

http://www.galaxymall.com/software/PRODEV

 

[Hinfer]

TTThio:

Sorry I am still having trouble understanding what you mean. I go to the form and go to the properties of the control. In the after update event, I have

Private Sub EmployerLastname_AfterUpdate()
If Me.Dirty Then
Me!Command5.Enabled = True
Else
Me!Command5.Enabled = False
End If
End Sub
------------------------------------------------------
'In the cancel button, on click event I have:

Private Sub Command5_Click()
Dim ctl As Control

For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
ctl.Value = ctl.OldValue
End If

Next ctl
End Sub


Is this how I am supposed to do this? If so, it is still only undoing the changes of the last event.

 

[TTThio]

Yes Lonnie you are right.
My point is using me.undo in the form_beforeupdate will restore the old values you want or not soon you move to other record. With the button, you can decide whether or not to save the change of that certain record and could be specifically applied to certain controls before moving to another record.

Sorry Hinfer if I somehow misled you to the thought of undoing all the changes at the last moment before closing the form.

 

[Hinfer]

Let me clarify:

I have a form linked to a table. In a table I have 5 employees. They have last name and first name and a autonumber as primary key.

The form is allowed to have edits, deletes and additions. The form thus displays 5 records for the user to edit , delete or to add another employee.

If a user has made some changes to all 5 employees I would like a cancel button to undo all those changes and exit. The problem is that it only undos the last record I made changes to. Each time I move to another record it has Access has saved it. I tried the codes above but still they only allow an undo on the last record that was changed.

 

[LonnieJohnson]

Thus my point, Hinfer, once you leave a record and go to another record, the first record get's commited to the table. Once it has been commited to the table. There is no undo.

You will have prompt your users with a messgae box asking them if they want to save the changes for each record then use code simular to what TTThio is suggesting.

ljprodev@yahoo.com
ProDev, MS Access Applications B-)

http://www.galaxymall.com/software/PRODEV

 

[Hinfer]

Thanks guys for all your help.

 

[TTThio]

Hinfer,

I just find another way to do this, let me know if you are still interested and has not given up. I'll paste the code with some explanation. It will undo all the changes at once upon closing the form.

Tin Tin

 

[Hinfer]

TTThio:

Yes I am very interested!!!!

 

[Hinfer]

Lonnie and TTThio:

From trying to solve this problem I found out a cause to another problem. I wanted to do the same in a form with a subform and the subform has many records.

Thanks. At least I know what the cause is.

If you are interested check out: thread702-138813

 

[TTThio]

Okay, here so far I tried and it worked on mine.
Your question makes me learn something too.

1. Don't forget to set a reference to Microsoft DAO 3.6 Library (in Tool, Reference)

2. in the declaration section
Public dbs as Database


Private Sub Form_Open(Cancel As Integer)
'to avoid built-in error message for existing table name
On Error Resume Next

Set dbs = CurrentDb

'make a back up copy of the un-yet-altered table/source
dbs.Execute "SELECT lkEmployee.* INTO BackUp FROM lkEmployee;"

dbs.Close

End Sub


Private Sub btnUndo_Click()
On Error Resume Next

'reset the form record source to nothing so you can delete the underlying table
Me.RecordSource = ""

Set dbs = CurrentDb

'delete existing source/underlying table
dbs.Execute "DROP TABLE lkEmployee;"

'rename the back up with the same name as the original source table
DoCmd.Rename "lkEmployee", acTable, "BackUp"

' set the form record source
Me.RecordSource = "lkEmployee"

End Sub

'Just in case the user want to save the changes instead of undo it
Private Sub Form_Close()
On Error Resume Next
Set dbs = CurrentDb

'delete existing source/underlying table
dbs.Execute "DROP TABLE BackUp;"

End Sub

For a try, first make a copy table of the lkEmployee, but don't forget to change the "lkEmployee" in the above code to the copy table name. So, if something wrong, you won't delete your original table. (Then you will kill me....)

Have a try, let me know if it works on yours.

Tin Tin

 

[Hinfer]

How do I do step 1.

1. Don't forget to set a reference to Microsoft DAO 3.6 Library (in Tool, Reference)

 

[TTThio]

When you go to the module, go to menu TOOL, then REFERENCE, check MICROSOFT DAO 3.6 LIBRARY.

 

[Hinfer]

TTThio:

I get the error message name conflicts with exisitng module, project, or object library.

Could I actually send you my db so you can take a look at it?

 

[Craig0201]

Hinfer,

To make the code work, you need to add a reference to DAO in each variable declaration. It seems you have both ADO and DAO declared so Access doesn't know which one to use. Hence Public dbs As Database becomes Public dbs as DAO.Database

Craig