Undoing an Add

[sja13]

Hi guys....

I have a Form, who'se data source is a Query.

There are text and combo boxes on the Form, all of which have a Control Source of a field name in the Query's underlying Table.

On the Form are some Command Buttons.

There is a Command Button for "Add", and a Command Button for "Save".

Clicking "Add" executes an Event Procedure, which executes

  DoCmd.GoToRecord , , acNewRec

As far as I can tell, this doesn't seem to actually add the record to the table - what it seems to do is set the default values for the text boxes and prepare the Form to accept the details of the putative next record.

When the "Save" button is clicked, if the Form is Dirty, I set the value of Dirty to False, which seems to actually write the record to the Table.
The write is also executed if I use navigation buttons to move to a different record.

However!

I also have an "Undo" Command Button.
I'm trying to handle the situation where a User has clicked "Add", realised their mistake, and then clicked "Undo" to 'cancel' the Add.

I've tried the following code options in a subroutine called when "Undo" is clicked.

  frm.Undo

(the subroutine is called with "Me" from the Command Button being received as "frm")
According to Microsoft's Help on Undo "... If the Undo method is applied to a form, all changes to the current record are lost. ..."
Not sure what that means when the Form is dealing with a new record!

  DoCmd.RunCommand acCmdUndo

(there doesn't seem to be any parameters for this, so presumably it assumes the current Form)

  Screen.ActiveForm.Undo

(at least this mentions ActiveForm)

All of them seem to leave a situation where a record is sort of partially added (sorry if that's not a brilliant description, but it's the best I can do!). The form seems to act as if there's a new record with only default values set.
As it happens, I know the record number of the record being viewed ('lngRecNo' in the following code) when "Add" was clicked, so my initial though was to include code like

  DoCmd.GoToRecord , , lngRecNo

This fails with "record can't be accessed".

Can any kind soul point me towards information to help me work out exactly what is happening?

My best guess at the moment is that I may need to delete the "new" record, using something like

    DoCmd.SetWarnings True
    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdDeleteRecord

but when I try that I get an error on the Select saying that the record can't be accessed (same error as above).

Any ideas?

 

[arnelgp]

when you execute gotorecord,, acNewRec, the New record is somewhere memory buffer and not physically committed to disk.
so you can't do any deletion of the New record since physically it does not exist yet.

in your case, bound form, explicitly saving the record (dirty=false), save the record to table physically. and setting focus away from the form when acNewRec is in effect, implicitly save that record.

your undo and save button has less use then.

you can however use Unbound Form so you can decide when to save the record and when to discard any changes.

 

[Minty]

When you go to a New Record, as you have noticed, the new record is "pre"-loaded with the default values.
However this new record is still exactly that - a new record, not a saved record.

At this point it will still be Dirty = False, until you enter some information into a bound field and then move to another record, or force a save.

So what it is telling you is correct, you can't undo that record as there is nothing to undo until it is saved.

To "Undo" the record you would need to use the Before_Update property that will fire before the record is saved.

 

[sja13]

If I understand what you're saying correctly, until I execute a "Save" (in some way), the record isn't actually committed to the Table.

Presumably if I attempt to abort an add by pointing the Form at the record which was being shown before the Add function was selected, I'll end up saving the record because this will count as "navigation", and therefore execute a commit?

I'll look into "Undo" button setting a flag, to be tested (and reset) in the Form's BeforeUpdate event, presumably therein setting Cancel to True.

And thanks!

 

[sja13]

Unfortunately, at record 4, during the "Undo" code, and with the "Form_BeforeUpdate" setting

  Cancel = True

when I subsequently execute

  DoCmd.GotoRecord , , lngRecNo

where lngRecno is 4,

  frm.CurrentRecord

(where frm is the received version of "Me")
has a value of 1.
The GoToRecord didn't error (I have no private error handler active), but didn't seem to work either.
Do I need to issue some sort of Refresh or Requery or Repaint or something to point the form back to record 4?

 

[Minty]

If you Undo / Cancel on a new record you will be left on a new, new record, if that makes sense.

On a form that record would normally be record 1. If you want to go back to the record you where originally on when you pressed the New Record command, but then cancelled, you will need to bookmark the current record number, then reload the original recordset to get back to that bookmark.

I would personally store the current PK identifier and simply go back to that after re-querying your existing recordset.

 

[sja13]

you will need to bookmark the current record number, then reload the original recordset to get back to that bookmark.

I would personally store the current PK identifier and simply go back to that after re-querying your existing recordset.

I'm a bit new to Access, and have never used bookmarks, so I'll look into that area.
Also, I don't know what you mean by "the current PK identifier", and how you would"simply go back to that".

Sorry, I said I was new! I'll try Mr. Google (unless you can elucidate, he said hopefully!)

 

[Minty]

Sorry - PK = Primary Key - probably an Autonumber unique field in your underlying data. If you saved this into a form wide variable (Declare it before any other code after the Option Compare Database/ Option Explicit statements at the top of your VBA code window) and setting it before any other code in your newRecord command.

Another very simple method if you find it easier, is to store the last PK ID on a an unbound hidden control on the form and refer back to that.

Bookmarks are a slightly more involved method, but worth a read up on.

 

[sja13]

Thanks Minty - I'll have a rummage round for a bit and see what I can come up with.

Thanks for confirming that the Add hadn't added a record - that was really puzzling me.

And thanks again for the (completely unnecessary) apology - it's refreshing when an expert can also talk to twits like me in a language I can understand!

 

[sja13]

Just in case anyone is avidly following this post (sad creatures!), and to prove that even Yorkshiremen can make mistakes, I offer the following solution.

GET THE SYNTAX RIGHT!

When I replaced

    DoCmd.GoToRecord , , glngOldCurRec

with

    DoCmd.GoToRecord , , acGoTo, glngOldCurRec

it all worked perfectly!

OK, maybe someone could have spotted my mistake, but the important thing is you guys gave up your time trying to help me, and that's an invaluable gift.

Once again, thanks to all responders.....