How to undo changes in a subform using a "Clear" form button so user can start over

Are any of the text boxes calculated controls?

mx

Also .... lets simply it down to this:

    Dim ctl As Control
    With Me.subFrmExamDetail.Form
        For Each ctl In .Controls
            If TypeOf ctl Is TextBox Or TypeOf ctl Is ComboBox Then
                ctl.Value = Null
            End If
        Next ctl
    End With

To cover the calculated control case:

    Dim ctl As Control
    With Me.subFrmExamDetail.Form
        For Each ctl In .Controls
            If TypeOf ctl Is TextBox Or TypeOf ctl Is ComboBox Then
                If Left(ctl.ControlSource, 1) <> "=" Then
                    ctl.Value = Null
                End If
            End If
        Next ctl
    End With

If this form is bounded form to a table
So Why dont u simlpy Say :

Me.Undo
DoCmd.GoToRecord , , acNewRec

Thanks DatabaseMX,

I used the non-calculated control case suggestion and it cleared all controls without an error, however when I cancelled out of the window it still saved a record in the table with no entries for the record except for the autoincrementing primary key field.

Is there code I can add that will prevent this from happening? Essentially if I click clear, it would clear all controls and remove anything it saved to the table.

I  want to mention I also have a cancel button, perhaps I should keep the "Clear" button just for clearing the form only and when a person clicks the "Cancel" button to cancel out of the record, then the record is deleted from the table. Can this be done instead?


zoom2000:
I tried your code, and it did clear all controls, but it did so to the subform and the parent form. I don't want to clear the parent form. Also I checked the table where the data is to be stored, and it still saved all data that was in the controls, so even though the form controls appeared to be reset, the data that was in them got saved.

The code I used from DatabaseMX, actually only cleared the controls in the subform I wanted to be affected, and even though it still created a record in the table, the fields were empty.

me.undo will only work if the form is in Dirty Mode, however when the subform loses focus (i.e. you move focus to the "undo" button) the record is immediately committed to the database and therefore no longer "dirty." Your "undo" functionality should therefore really become a delete function.

Identify the subform record and then issue a delete instruction to the subform's source table.

Ok.

Increased points as I will also need help to delete the record from the table. I have decided to keep the clear button to use only as a way to reset the form, not to delete the record (Thanks DatabaseMX for that).

I added a command button that is supposed to delete a record, but it didnt work. Access added the following code for this button..

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

I changed it to this but it still didnt work..

With Me.subFrmExamDetail.Form
      DoCmd.SetWarnings Warningsoff
      DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
      DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
      DoCmd.SetWarnings Warningson
      Me.Form.SetFocus
      DoCmd.Close
End With

What happens is that it clears the form text fields and comboboxes, including the parent (which I dont want to be affected), yet it still gets written into the database.

What do I have to do to remove the record it created from the table it is bound to?

how about

DoCmd.SetWarnings false
docmd.runsql "Delete * from subformtablesource where subformrecordid = " & Me.subformcontrolname.Form.subformidfield.value
DoCmd.SetWarnings true

This assume that the delete command button is attached to the parent, not the subform.

Hi Runrigger,

I have implemented the following code and still no sugar. The record remains in the table. Any ideas? What am I doing wrong?

Dim sqlStatement As String
sqlStatement = "DELETE * FROM Exam WHERE (((Exam.ExamID)=[Forms]![frmFullExam]![subFrmExamDetail].[Form]![txtExamID]));"
   
    With Me.subFrmExamDetail.Form
         DoCmd.SetWarnings False
         DoCmd.RunSQL sqlStatement
         MsgBox cancelled, vbExclamation, "Delete Notice"
         DoCmd.SetWarnings True
         Me.Form.SetFocus
         DoCmd.Close
    End With

"Your "undo" functionality should therefore really become a delete function."

Umm .. I don't think so.  You cannot delete a record that has not been saved yet ... and the Save is what we are trying to avoid.

The Me.Undo *should* work if executed right after the Clear function

And (no pun) I'm not that clear on what is needed here.  What do you see as the difference between Clearing and Canceling ?

Also all of these types of commands:

DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

were deprecated by Microsoft over 10 years ago and should not be used.  I know the wizards still generate these, but 1) they are unreliable and 2) about impossible to know what they do ... fyi.

mx

Dear mx,

I have been guessing at the problem because I had something similar;

A continuous subform embedded within the parent form

I did not want to display two delete/undo buttons (one on the parent, one on the sub) so chose instead to have one of each on the parent form.

When focus was lost from the subform and on to either the undo/delete buttons on the parent, the record that I was editing on the subform was automatically committed to the database. I was savvy enough to write a temporary record with the old values on the "BeforeUpdate" event of the subform

If undoing was required, I issued an update to the newly saved record using the old values, if delete required I simply deleted.

The author I guess, needs to lets us know where his various command buttons are before finally present a "correct" solution.

FYI, I have since moved on and have much more robust solutions in place, I am not however in a position (with time and effort) to re-write the authors database ;-))

Cheers
Dave

"the record that I was editing on the subform was automatically committed to the database."

That is certainly true :-)

mx

you taking the michael?

huh?

sorry, in England, when somebody asks you if you are "taking the micheal" they are really asking if you are being sarcastic.

hence

==========================================
"the record that I was editing on the subform was automatically committed to the database."

That is certainly true :-)
==========================================

looks like you are being sarcastic, meaning I may well have made a completely incorrect statement?

no, not being sarcastic.  Just agreeing with how it works ... as in You are correct.

mx

adding that it's highly unlikely I would be sarcastic ... unless we were all joking around about something ... which is not the case here.

mx

OK mx, thanks for the vote of confidence, I was not 100% certain, as I have really done that much development of late!

Hi Runrigger and DatabaseMX,

The only command buttons I have are 3 located on the parent form. These buttons are Save, Clear, and Cancel.

Runrigger, reading your post on what you have experienced on this, I think I need to change something on what my buttons are doing.

New Definitions:
Save - The record is saved to the DB.
Clear - Reset the form, and delete any entry the subform has saved to the DB that matches the ExamID on the subform.
Close - Close the parent form and nothing else.

I am changing my Cancel button to a Close. It makes more sense. Having a Cancel button that deletes a record doesn't make sense. The Clear button should handle all of this functionality.

So at this point, I can Save a record, and I can Close the parent form as it is.
I am also able to reset all controls to "Null" on the subform using the Clear button, but if I close out after clicking the Clear button it still saves a record with Null entries and of course the newly generated ExamID.

I hopes this helps.

"haven't"

"I am also able to reset all controls to "Null" on the subform using the Clear button"

Sooo ... I thought we were clearing controls on the main form?  Where exactly did you put that code I gave you above?

mx

Author, there is no easy solution I'm afraid;

Save does nothing with the subform entry because as soon as you click it, the subform loses focus and saves the record anyway.

Attached is an image of one of the fomrs in my database, parent with two subforms, you can see to the right of the image, that I have added a delete button on each of the continuous subform entries;

It was the easiest way around things for me at that time, it works though!
ee-invoicescreen.bmp

DatabaseMX,

I placed the code for the Clear function in the click event of the Clear command button. This button is on the parent form. It does work. It sets all the subform's controls to null. The only thing missing is removing the record from the Exam table. It still manages to save a record to it using Null values on all fields except for the tables ExamID field which of course auto-generates a new number.

The main form has some text controls but I'm not clearing those. These just show a persons name, and personal info which is not modifiable here and is not being saved to the Exam table.

Hi mx,

I tried both of the code above and it didnt work. The first code from your previous post cleared the controls but it got an error...
"You can't assign a value to this object."  I'm guessing its the textbox that displays the ExamID from the Exam table. This is the primary key of that table. Makes sense that we cant assign Null to that textbox.

The second code option you provided didn't seem to do anything. None of the textboxes or comboboxes were cleared and it still showed up in the Exam table.

This is so frustrating. I really thank both of you guys though for your patience.

Runrigger: The image you provided of a database you made, has a Delete Invoice. That would be similar to mine except for me it is called an Exam. How is that implemented?

Try just this for the Clear on the subform (called from button on main form):

Me.subFrmExamDetail.SetFocus
Me.subFrmExamDetail.Form.Undo   ' clear all controls and undo - cancel the Save.

or

Me.subFrmExamDetail.Form.SetFocus
Me.subFrmExamDetail.Form.Undo   ' clear all controls and undo - cancel the Save.

Hi mx,

The first code, nothing seemed to happen. Exam table has new record still.

The second code, I got this error..

There is an invalid method in an expression.

I double checked the code and its written properly.

Can you:

1) Compact & Repair (*** to shrink the size),

2) Zip up the MDB (*** to further shrink the size)

3) Attach the file for upload here (using the 'Attach File function below) ... removing any sensitive data of course.

4**** And please give a clear explanation of exactly how to reproduce the problem or what you are trying to do.

NOTE:  If you have an A2007 ACCDB, please convert to A2003 MDB.
mx

Hi mx,

I wish I could do that however the data contained in it is sensitive. My boss wont allow me to send it.

MX,

The button is on the parent, therefore.....

Me.subFrmExamDetail.Form.SetFocus
Me.subFrmExamDetail.Form.Undo   ' clear all controls and undo - cancel the Save.

......has no effect because the save on the subform has already been committed, so how about.

Me.subFrmExamDetail.Form.SetFocus
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord

Focus shifts to the subform, the record is selected and is then deleted?

In theory it should work.

Yep, you're right.  But ... really, this is not a clean way to handle this.  The record should never be saved in the first place.

mx

last line should read DoCmd.SetWarnings True

MX, I totally agree, its the bug within Access as you can not have a Parent and Subform both sitting in dirty mode!

The way to do it therefore is to get in to unbound forms etc etc.

Which is what I do now, but I do not have the time/inclination to re-write the author's database!

my proposed solution combined with your  "clean up" execution above should be enough on this occassion.

"MX, I totally agree, its the bug within Access as you can not have a Parent and Subform both sitting in dirty mode!"

Well, it's not a bug ... just how it is.  And except for specialized cases, unbound forms somewhat defeat the beauty of Access.

mx

I mistyped, as I so often do these days;

Its a bugbear!!!! We have to live with some of them.

There are many arguments both for and against bound/unbound, I am a total proponent of Bound forms, occasionally however, unbound works a treat for me.

Having thought about it, the user on this occassion will not need to redevelop, the above proposal to him will be enough.

Cheers
Dave

"There are many arguments both for and against bound/unbound, "

Yeah, lol.  But, lets not go down that road today :-)

mx

This is effectively the old "I want transactions in Forms" issue.
Though technically possible - they're really hard to make tidy.
(FWIW I understand that the level of driver configurability in MySQL's ODBC provider actually allows for some quite effective such implementation - but I don't use MySQL so I'll just leave that in there as a throwaway comment).
If you're interested - the transactions in forms issue was discussed in one of Markus' Expert Question series here.
Bear in mind though that transactions can cause concurrency issues and the thought of one hanging during the edit time of a user's form instance is a bit scary.
The standard alternatives are:
1) Transaction - Messy, problematic - see above
2) Prevent update of the subform at all until you confirm acceptance (BeforeUpdate canceling - which is a bit boring and likely not a satisfying UI in this instance - i.e. you'd be prevented from taking focus away from the subform until you agreed to the record being commited.
3) Caching the data in some way.
I could go into detail - but I'd be getting Deja Deja Vu then. Have a read of this thread (on another site) to see what I mean. :-s
Generally, once you're implementing such methods, you'd have a Save button rather than just a Clear. (i.e. because you're not really writing the live data, you need a point at which you decide to do that or not. Save or Cancel = Write or Do Nothing).
But there are choices in implementation naturally.
Without getting into the old bound/unbound debate (FWIW I think Unbound forms very much have a place in Access development - but to use them extensively or even exclusively you either need to really know what you're doing or have too much time on your hands)...
If your subform is for a single child record (as opposed to being a continuous/datasheet) then unbound would be a fourth option yes. (Again with the same issue of a point of decision to save or not).
Cheers.

Too funny Leigh. ... I was about to post your Transactions in Forms mdb example.

Runrigger,

"Author, this works see attached database test (sorry but it's a2010, installing 2010 beta has corrupted my older version, don't know enough about 2010 to convert to earlier version)

    DoCmd.SetWarnings False
    Me.subformChild.SetFocus
    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdDeleteRecord
    DoCmd.SetWarnings True"

This code did work. Thank you all with your brainstorming.

L.Purvis I will take a look at the link you offered. I'm always up for learning.

Thanks again everyone.