Cancel button to undo data on subform

[Tina Hudson]

Good day. I have a form with a subform that I use to add records to multiple
tables. I want the user to be able to just cancel data entry no matter if
they entered data on the main form or the subform, but I don't want to have
to have a "cancel" button the subform as this will be too confusing for them.

I have a cancel button on the main form with the standard code to cancel (if
dirty, then acmenu, undo, etc.) and this works really well for the
fields/controls on the main form. Not so much for the subform. The subform
has several fields that have default values.

Any help will be most appreciated.

 

[John W. Vinson]

Good day. I have a form with a subform that I use to add records to multiple
tables. I want the user to be able to just cancel data entry no matter if
they entered data on the main form or the subform, but I don't want to have
to have a "cancel" button the subform as this will be too confusing for them.

I have a cancel button on the main form with the standard code to cancel (if
dirty, then acmenu, undo, etc.) and this works really well for the
fields/controls on the main form. Not so much for the subform. The subform
has several fields that have default values.

Any help will be most appreciated.

Any record on the subform that's been "dirtied" will be saved to disk as soon
as you set focus to the main form (i.e. by clicking your undo button) and must
be explicitly deleted - it cannot be "undone" since it's too late. UNDO
reverses uncommited changes still on the form; the subform record is already
committed.

This is doubly the case if the user can (has) entered multiple subform
records. Each record is written to disk as soon as the user moves to another
record.

You'll need to actually run a Delete query to delete the records corresponding
to the current form's master link field.

If you're using the obsolete acMenu syntax... don't. A single line

Me.Undo

does the same thing as your acmenu... undo... code. The Access wizards
unaccountably generate some really moldy old code!

 

[Tina Hudson]

Thanks John, for your quick response. I'll let you know how it goes!

Again, I can't tell you how much time you and all the other MVPs have saved
me over the years. You are all much appreciated!

Regards,
Tina Hudson

 

[Tina Hudson]

Worked like a charm! It took me a while to figure out the delete query, but
since I set up my relationships as cascading, referential integrity, etc, all
I had to do was delete the record from the table that the main form inserted
into (hence, the record was deleted also from the table that the subform
inserted into).

Question: In using the Me.Undo code, do I still need to include"If Dirty,
Then"?

Second Question: In the Code below, is it recommended to use the SetWarnings
to False? I have set up message boxes in most of the forms, to address
mistakes the user made because sometimes the automatic message boxes confuse
the user. So, I turned it off here.

For those of you wanting to know how to set up a delete query for this
situation, I placed a cmd button on the main form, "Cancel", and the
following is the code behind the cmd button (MS Access 2000):

Dim strSQL As String

Dim db As DAO.Database
Set db = CurrentDb
DoCmd.SetWarnings False 'Will not show automatic access messages

'This is the actual delete query to delete data entered on the subform
strSQL = "DELETE tblPerson.* FROM tblPerson WHERE Person_ID =
[Forms]![frmQuickAdd_Familymember]![sfrmQuickAdd_KinshipAssess]![Person_ID]"

'this will erase data that was entered by user on the main form
If Me.Dirty Then
Me.Undo
End If

'this runs the delete query
DoCmd.RunSQL strSQL

End Sub


Note that I also disabled the "Escape" Key on both the main form and subform
so that users have to press the cmdCancel.

 

[John W. Vinson]

Worked like a charm! It took me a while to figure out the delete query, but
since I set up my relationships as cascading, referential integrity, etc, all
I had to do was delete the record from the table that the main form inserted
into (hence, the record was deleted also from the table that the subform
inserted into).

Question: In using the Me.Undo code, do I still need to include"If Dirty,
Then"?

If there's nothing to be undone then Me.Undo will just be a no-operation. It's
not necessary to check.

I would certainly check before running a Delete query though.

Second Question: In the Code below, is it recommended to use the SetWarnings
to False? I have set up message boxes in most of the forms, to address
mistakes the user made because sometimes the automatic message boxes confuse
the user. So, I turned it off here.

Sorry - should have commented on this! DON'T use the RunSQL method - it
doesn't trap errors. Instead use the Execute method - it doesn't pop up
warning messages and does let you trap errors. E.g.


Dim strSQL As String

Dim db As DAO.Database
Set db = CurrentDb

On Error GoTo Proc_Err

'This is the actual delete query to delete data entered on the subform
strSQL = "DELETE tblPerson.* FROM tblPerson WHERE Person_ID =
[Forms]![frmQuickAdd_Familymember]![sfrmQuickAdd_KinshipAssess]![Person_ID]"

'this will erase data that was entered by user on the main form
If Me.Dirty Then
Me.Undo
End If

'this runs the delete query
db.Execute strSQL, dbFailOnError
Proc_Exit:
Exit Sub
Proc_Err:
MsgBox "Error " & Err.Num & " in cmdUndo_Click:" _
& vbCrLf & Err.Description
Resume Proc_Exit
End Sub

 

[Tina Hudson]

Thanks John. I did see where the Execute method was preferred over the
RunSQL but didn' t know how to go about doing it, so again, thanks for that!

Have a great weekend,
Tina

Worked like a charm! It took me a while to figure out the delete query, but
since I set up my relationships as cascading, referential integrity, etc, all
I had to do was delete the record from the table that the main form inserted
into (hence, the record was deleted also from the table that the subform
inserted into).

Question: In using the Me.Undo code, do I still need to include"If Dirty,
Then"?

If there's nothing to be undone then Me.Undo will just be a no-operation. It's
not necessary to check.

I would certainly check before running a Delete query though.

Second Question: In the Code below, is it recommended to use the SetWarnings
to False? I have set up message boxes in most of the forms, to address
mistakes the user made because sometimes the automatic message boxes confuse
the user. So, I turned it off here.

Sorry - should have commented on this! DON'T use the RunSQL method - it
doesn't trap errors. Instead use the Execute method - it doesn't pop up
warning messages and does let you trap errors. E.g.


Dim strSQL As String

Dim db As DAO.Database
Set db = CurrentDb

On Error GoTo Proc_Err

'This is the actual delete query to delete data entered on the subform
strSQL = "DELETE tblPerson.* FROM tblPerson WHERE Person_ID =
[Forms]![frmQuickAdd_Familymember]![sfrmQuickAdd_KinshipAssess]![Person_ID]"

'this will erase data that was entered by user on the main form
If Me.Dirty Then
Me.Undo
End If

'this runs the delete query
db.Execute strSQL, dbFailOnError
Proc_Exit:
Exit Sub
Proc_Err:
MsgBox "Error " & Err.Num & " in cmdUndo_Click:" _
& vbCrLf & Err.Description
Resume Proc_Exit
End Sub

 

[Tina Hudson]

Well, I know the problem was fixed but now that we've moved into the 21st
century and I now have Office 2007 installed (but still have Access 2000) the
fix no longer works.

I get "Runtime Error 438 Object doesn't support this property or method"
when the cmdbutton is clicked

Any suggestions?

--
Thanks,
Tina Hudson

Worked like a charm! It took me a while to figure out the delete query, but
since I set up my relationships as cascading, referential integrity, etc, all
I had to do was delete the record from the table that the main form inserted
into (hence, the record was deleted also from the table that the subform
inserted into).

Question: In using the Me.Undo code, do I still need to include"If Dirty,
Then"?

If there's nothing to be undone then Me.Undo will just be a no-operation. It's
not necessary to check.

I would certainly check before running a Delete query though.

Second Question: In the Code below, is it recommended to use the SetWarnings
to False? I have set up message boxes in most of the forms, to address
mistakes the user made because sometimes the automatic message boxes confuse
the user. So, I turned it off here.

Sorry - should have commented on this! DON'T use the RunSQL method - it
doesn't trap errors. Instead use the Execute method - it doesn't pop up
warning messages and does let you trap errors. E.g.


Dim strSQL As String

Dim db As DAO.Database
Set db = CurrentDb

On Error GoTo Proc_Err

'This is the actual delete query to delete data entered on the subform
strSQL = "DELETE tblPerson.* FROM tblPerson WHERE Person_ID =
[Forms]![frmQuickAdd_Familymember]![sfrmQuickAdd_KinshipAssess]![Person_ID]"

'this will erase data that was entered by user on the main form
If Me.Dirty Then
Me.Undo
End If

'this runs the delete query
db.Execute strSQL, dbFailOnError
Proc_Exit:
Exit Sub
Proc_Err:
MsgBox "Error " & Err.Num & " in cmdUndo_Click:" _
& vbCrLf & Err.Description
Resume Proc_Exit
End Sub

 

[John W. Vinson]

Well, I know the problem was fixed but now that we've moved into the 21st
century and I now have Office 2007 installed (but still have Access 2000) the
fix no longer works.

I get "Runtime Error 438 Object doesn't support this property or method"
when the cmdbutton is clicked

Please post your actual current code.

 

[Tina Hudson]

John,

You asked I show my code in your response, which I would have replied to,
but for some reason, it's appearing blank. I also would have posted the code
earlier, but I kept getting an error message.

I put this code in quite a few forms and none of them are working now. They
were working before I was upgraded to Microsoft Office 2007 (but still have
Access 2000, I know, it's sad).

Here is my code:

Private Sub cmdCancel_Click()
On Error GoTo Proc_Err

Dim strSQL As String
Dim db As DAO.Database
Set db = CurrentDb

'This will undo data entered on subform by deleting TDM from tblTDM and
cascade deleting from tblTDMChild
strSQL = "DELETE tblTDM.* FROM tblTDM WHERE TDM_ID =
[Forms]![frmQuickAdd_TDM]![sfrmTDMChildEdit]![TDM_ID]"

'This will undo data entered on main form
Me.Undo

'This will run the Delete query
db.Execute strSQL, dbFailOnError

'This will close the form without saving
DoCmd.Close acForm, "frmQuickAdd_TDM", acSaveNo


Proc_Exit:
Exit Sub

Proc_Err:

JOHN --- THIS IS WHERE I GET THE ERROR

MsgBox "Error " & Err.Num & " in cmdCancel_Click:" _
& vbCrLf & Err.Description
Resume Proc_Exit

End Sub
--
Thanks,
Tina Hudson

Well, I know the problem was fixed but now that we've moved into the 21st
century and I now have Office 2007 installed (but still have Access 2000) the
fix no longer works.

I get "Runtime Error 438 Object doesn't support this property or method"
when the cmdbutton is clicked

Any suggestions?

--
Thanks,
Tina Hudson

Worked like a charm! It took me a while to figure out the delete query, but
since I set up my relationships as cascading, referential integrity, etc, all
I had to do was delete the record from the table that the main form inserted
into (hence, the record was deleted also from the table that the subform
inserted into).

Question: In using the Me.Undo code, do I still need to include"If Dirty,
Then"?

If there's nothing to be undone then Me.Undo will just be a no-operation. It's
not necessary to check.

I would certainly check before running a Delete query though.

Second Question: In the Code below, is it recommended to use the SetWarnings
to False? I have set up message boxes in most of the forms, to address
mistakes the user made because sometimes the automatic message boxes confuse
the user. So, I turned it off here.

Sorry - should have commented on this! DON'T use the RunSQL method - it
doesn't trap errors. Instead use the Execute method - it doesn't pop up
warning messages and does let you trap errors. E.g.


Dim strSQL As String

Dim db As DAO.Database
Set db = CurrentDb

On Error GoTo Proc_Err

'This is the actual delete query to delete data entered on the subform
strSQL = "DELETE tblPerson.* FROM tblPerson WHERE Person_ID =
[Forms]![frmQuickAdd_Familymember]![sfrmQuickAdd_KinshipAssess]![Person_ID]"

'this will erase data that was entered by user on the main form
If Me.Dirty Then
Me.Undo
End If

'this runs the delete query
db.Execute strSQL, dbFailOnError
Proc_Exit:
Exit Sub
Proc_Err:
MsgBox "Error " & Err.Num & " in cmdUndo_Click:" _
& vbCrLf & Err.Description
Resume Proc_Exit
End Sub

 

[John W. Vinson]

JOHN --- THIS IS WHERE I GET THE ERROR

MsgBox "Error " & Err.Num & " in cmdCancel_Click:" _
& vbCrLf & Err.Description
Resume Proc_Exit

Verry odd... my machine with 2007 installed isn't up right now so I can't
check. Could they have changed the Err object in 2007!?

Try opening the VBA editor and selecting Tools... References from the menu.
Are any marked Missing?

If not, try browsing the Help file for the Err or Error object and see if the
syntax has changed.

 

[Tina Hudson]

John,

No, none of the references I had checked were missing. I went through all of
the references and didn't see any missing. I did notice there is a Microsoft
Office 12.0 Object Library. That is not checked. I have the 9.0 object
library checked.

I had changed my code from DoCmd.RunSQL to the db.execute command, which
isn't working. I changed the code back to DoCmd.RunSQL and that works.

I know that it was working prior to my conversion as my notes reflect this.

 

[Douglas J. Steele]

Sorry, but that couldn't have worked before. It's always been Err.Number,
not Err.Num

And if you're using Access 2007, you should have Office 12 checked. Office 9
was Access 2000.

 

[John W. Vinson]

Sorry, but that couldn't have worked before. It's always been Err.Number,
not Err.Num

<D'OH!>

Sorry, My mistake in my original answer.

 

[Kari]

Lots of good info here--Thank You!

I have a similar situation, form with subform (and sub-sub-form) pulling
data from three tables. I would like to put an "Undo" button on the main
form. Using the help here I could make a button to delete records, but what
about just undoing edits? The only way I can see to do this is to create a
temp-table to store the initial values, and use the "Undo" button to
overwrite any edits with those initial values. Is there an easier way? Any
pitfalls I should look for?

I'd appreciate any insight on this--I've been thinking about it in the back
on my mind for months, and I haven't come up with any better ideas.

Kari

Worked like a charm! It took me a while to figure out the delete query, but
since I set up my relationships as cascading, referential integrity, etc, all
I had to do was delete the record from the table that the main form inserted
into (hence, the record was deleted also from the table that the subform
inserted into).

Question: In using the Me.Undo code, do I still need to include"If Dirty,
Then"?

If there's nothing to be undone then Me.Undo will just be a no-operation. It's
not necessary to check.

I would certainly check before running a Delete query though.

Second Question: In the Code below, is it recommended to use the SetWarnings
to False? I have set up message boxes in most of the forms, to address
mistakes the user made because sometimes the automatic message boxes confuse
the user. So, I turned it off here.

Sorry - should have commented on this! DON'T use the RunSQL method - it
doesn't trap errors. Instead use the Execute method - it doesn't pop up
warning messages and does let you trap errors. E.g.


Dim strSQL As String

Dim db As DAO.Database
Set db = CurrentDb

On Error GoTo Proc_Err

'This is the actual delete query to delete data entered on the subform
strSQL = "DELETE tblPerson.* FROM tblPerson WHERE Person_ID =
[Forms]![frmQuickAdd_Familymember]![sfrmQuickAdd_KinshipAssess]![Person_ID]"

'this will erase data that was entered by user on the main form
If Me.Dirty Then
Me.Undo
End If

'this runs the delete query
db.Execute strSQL, dbFailOnError
Proc_Exit:
Exit Sub
Proc_Err:
MsgBox "Error " & Err.Num & " in cmdUndo_Click:" _
& vbCrLf & Err.Description
Resume Proc_Exit
End Sub