Changing the Cancel Before Update Behavior

Question

Not sure if this is something anyone else has tried to do, but I'm getting a little stuck in how to make Access do as I'm asking, here is the behavior I want to intoduce:

1) When incorrect or invalid entry is made into a field a message box opens asking if the user wishes to cancel or retry.

2) When they select 'Retry' the normal 'cancel' procedure happens and they are put back into the field to retry entering the information.

3) On selecting 'Cancel' the entry is deleted.

This is all working ok in the normal fields, however one of the entry types I have is a list of items using a subform set to 'continuous forms', in this instance I need part 3 to delete the actual record.

I have tried to do this using DoCmd.RunCommand acCmdDeleteRecord, but this offered the user the opportunity to cancel the delete, and I don't want to suppress all warnings in case an error occurs when it deletes. Otherwise this method worked ok.

I also tried deleting it using a DELETE query through CurrentDB.Execute but that brought up a message stating 'Record Deleted' and left me with a record showing '#DELETED' in every field and refused to move it.

I'd like to use DoCmd.RunCommand acCmdDeleteRecord, but I don't know if it is possible to capture the confirmation message without suppressing all messages, is it possible?

Otherwise, does anyone have any other ideas?

PS: The reason I want to change the behavior like this is because most of the users I'm dealing with get confused when they try to cancel the entry by deleting it, and no one thinks to hit the Escape key, so they keep getting messages stating that they must enter something into the field, they get frustrated and call me over to explain one of the simplest key functions that they forget seconds later. Giving them a question box that does the cancellation for them seems to be the best option.

Answer 1

3) On selecting 'Cancel' the entry is deleted.

This is all working ok in the normal fields, however one of the entry types I have is a list of items using a subform set to 'continuous forms', in this instance I need part 3 to delete the actual record.

Hi Avan,

On this moment I do not see why you should delete e record from the sub form, when a wrong value is selected in a specific control in the main form.
Can't you just set the value of that control to Null?

Imb.

Answer 2

Sorry, I didn't explain very clearly.

The main form doesn't delete any records, the subform is used when we are recording multiple items per main record, good example would be invoices raised on a contract, you may one contract but several invoices.

Basically, when the user inputs a value into the field in the subform there is always the possibility that they will do it wrong, on selecting 'retry' they can correct the mistake just like the normal Cancel behavior. But in the event they have entered one two many or need to make other corrections elsewhere on the record, I don't want to leave a blank line, because I know 'someone' will repeatedly ask if it is ok to leave blank lines in the subform. It just seems like less hassle in the long run to delete the entry and only show the records that have been entered that need to be there.

The subform is actually linked to a temporary table specifically made for this subform. When the main record is completed it moves the finished data from the temporary table into the database. One of the reasons for this is due to the amount of linking that needs to be done between the tables. It is a whole lot easier in my situation, and requires less code, to do all of this in bulk once the record is considered 'completed' rather then one entry at a time.

Answer 3

Sorry, I didn't explain very clearly.

The main form doesn't delete any records, the subform is used when we are recording multiple items per main record, good example would be invoices raised on a contract, you may one contract but several invoices.

Hi Avan,

Thank you for your explanation. If have met such a situation, bacause for the addition of records I use a (generalized) unbound "NewRecord"-form. You can only store the data in the table if all conditions are fullfilled.

In your situation, I thought there is some Undo mechanism in Access. Perhaps you can use that, but other specialists have to explain that to you, because I have never used it.

On the other hand, if you use a DoCmd.Execute "DELETE ..." to delete that one record, that should work to. Your subform will then show #Deleted in the controls, but that is easily removed by a Requery of the subform, or a simple re-assignment of the subform's RecordSource by itself.

Imb.

Answer 4

Running a DELETE statement failed to work when entering a new line, it did work when changing the data in an already existing field, towever it threw a runtime error when it tried to run the Requery that wasn't caught by my error trapping.

I took a gample and added a DoCmd.RunCommand acCmdSaveRecord before the SQL was executed and it works perfectly.

Thanks for you help.

Answer 5

Running a DELETE statement failed to work when entering a new line, it did work when changing the data in an already existing field, towever it threw a runtime error when it tried to run the Requery that wasn't caught by my error trapping.

I took a gample and added a DoCmd.RunCommand acCmdSaveRecord before the SQL was executed and it works perfectly.

Thanks for you help.

Hi Avan,

A typo from my side. Instead of DoCmd.Execute <sql delete string>, you have to use "CurrentDb.Execute <sql delete string>.

Imb.

Answer 6

Basically, when the user inputs a value into the field in the subform there is always the possibility that they will do it wrong, on selecting 'retry' they can correct the mistake just like the normal Cancel behavior. But in the event they have entered one two many or need to make other corrections elsewhere on the record, I don't want to leave a blank line, because I know 'someone' will repeatedly ask if it is ok to leave blank lines in the subform. It just seems like less hassle in the long run to delete the entry and only show the records that have been entered that need to be there.

I see that you've already come to a satisfactory solution here, but it seems inefficient to save a record only to delete it.  Your Cancel procedure could use code like this, which just undoes the record if it hasn't been saved yet, but undoes and deletes it if it has:

    Me.Undo
    If Not Me.NewRecord Then
        Me.Recordset.Delete
    End If

Calling the form's recordset's .Delete method won't display any message, and you don't have to requery the form to make the #DELETED record disappear.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html