Cancel data entry of a record (Access 2002/SP2)

Topic

New Reply

I want to be able to cancel the data entry of a record. For this question the user is entering data into a simple table containing a primary key field and a data field. The input form contains the two fields and a cancel button. No VBA code is currently in place as Access is handling all recordset activities.

Half way through adding a record’s data field the user is interrupted and, wishing to end the session, presses the cancel button. As this constitutes moving the cursor off of the current record, I know of no way to prevent Access from adding this bad record to the table.. I would like, I think, to put code into Cancel_Click to a) prevent Access from adding the incomplete record, or back up to the record just added and call the Delete method. I have tried simplistic things like Me!.MovePrevious but access couldn’t match the method with the object. How can I prevent this bad record from existing without the user having to manually open the underlying table and delete it?

Thank you.

Reply | Quote

Replies

Me.Undo cancels the changes in the current record. If that was a new record, the new record is canceled as a whole, i.e. it is not added to the underlying table.

Another good way to do this is to use an unbound form with an OK and Cancel button. The OK button invokes code to save the data that have been entered, the Cancel button just closes the form.

Reply | Quote

I don’t understand why clicking the Cancel button is “moving off the form” and causing Access to save the record. Where is this Cancel button? Even if this is a continuous form, a Cancel button in the form’s footer can still still be used to undo the current line being edited in the detail section.

Reply | Quote

Thank you for your replies. Your thoughts work in the simplistic table and form I created for this question but not in my real world application. I’m trying to figure out the difference.

Scenario 1a) As described above, the table has two fields: a primary key and a data field. On cmdCancel_Click has the following single statement: “DoCmd.Close acForm, “frmTable1″, acSaveNo”. I enter a key field (no further keys, that is I do not hit Enter or Tab or anything) then click cmdCancel. The record is written and must be manually deleted. Boo hiss.

Scenario 1b) As in 1a but the statement “Me.Undo” is added in front of the “DoCmd.Close

Reply | Quote

As noted in my previous reply, an alternative is to disable adding new records in the bound form, and use a separate unbound form to add new records. I use this technique regularly in situations where I don’t want the new record to be saved inadvertently.

Otherwise, the information in ACC2000: Unable to Undo or Rollback Main Form and Subform Changes may be useful.

Reply | Quote

Well, there is your problem. Your cancel button is closing the form! That automatically causes Access to save the current record. Why do you have that “DoCmd.Close” in the Cancel button? Take it out, and just use the “me.undo”. If you want to close the form, put in a 2nd button. In it, you can add code to check for whether or not the current record has been written, like this.
If me.Dirty then
if vbno = msgbox (“The current record will be saved before closing. Is this OK?”, vbquestion + vbyesno) then
me.undo
end if
end if
docmd.close

Reply | Quote

Thanks guys. I’ll run with your ideas.

John

Reply | Quote