cancelling docmd.gotorecord , , acNewRec

[Eric]

What's the best way to cancel the new record when doCmd.GoToRecord , ,
acNewRec is executed and before the new record is actually written to
the database.

I'm maintaining an existing Access 97 application. The main order
form's recordsource is a query. On the form is an add button that uses
doCmd.GoToRecord , , acNewRec to display an empty record and place the
user in the first field.

If the user tabs out of the first field (assuming they've typed
something in and want to continue editting the record) the record is
actually added to the database and an autoincrement field sets the
record number.

However, there's also a find button on the form and it allows the user
to search for a record even after they have clicked the add button and
before the new record has been added to the database. If they actually
search for a record, the new record is added.

What's the simplest and safest way to protect the user from this
series of events? I cannot do to much rewriting of the code or I might
cause other issues.

Thanks,
Eric

[Larry Linson]

DoCmd.RunCommand acCmdUndo

or SendKeys ({Esc},{Esc})

or teach the users to press the Esc key twice.

None of the above work if they have already done something to save the
record, e.g., (1) move off it to another record, (2) click into a subform on
the form, (3) done an explicit Save, or (4) closed the Form.

Larry Linson
Microsoft Access MVP
"Eric" <ep******@hotmail.com> wrote in message
news:bf**************************@posting.google.c om...

What's the best way to cancel the new record when doCmd.GoToRecord , ,
acNewRec is executed and before the new record is actually written to
the database.

I'm maintaining an existing Access 97 application. The main order
form's recordsource is a query. On the form is an add button that uses
doCmd.GoToRecord , , acNewRec to display an empty record and place the
user in the first field.

If the user tabs out of the first field (assuming they've typed
something in and want to continue editting the record) the record is
actually added to the database and an autoincrement field sets the
record number.

However, there's also a find button on the form and it allows the user
to search for a record even after they have clicked the add button and
before the new record has been added to the database. If they actually
search for a record, the new record is added.

What's the simplest and safest way to protect the user from this
series of events? I cannot do to much rewriting of the code or I might
cause other issues.

Thanks,
Eric

[Allen Browne]

Moving into the new record should not automatically cause the entry of the
new record to begin. There must be something that is dirtying the new
record. The best solution would be to find out what it is, and avoid that.
For example, if you are programmatically setting a value, do that in the
form's BeforeInsert event instead of when you move to the new record.

If you want user confirmation before a new record is added, use the form's
BeforeUpdate event. To destroy the new entry without saving:
Me.Undo

Private Sub form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
If Msgbox("Ok to save?", vbOkCancel) <> vbOk Then
cancel = true
Me.Undo
End If
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Eric" <ep******@hotmail.com> wrote in message
news:bf**************************@posting.google.c om...

What's the best way to cancel the new record when doCmd.GoToRecord , ,
acNewRec is executed and before the new record is actually written to
the database.

I'm maintaining an existing Access 97 application. The main order
form's recordsource is a query. On the form is an add button that uses
doCmd.GoToRecord , , acNewRec to display an empty record and place the
user in the first field.

If the user tabs out of the first field (assuming they've typed
something in and want to continue editting the record) the record is
actually added to the database and an autoincrement field sets the
record number.

However, there's also a find button on the form and it allows the user
to search for a record even after they have clicked the add button and
before the new record has been added to the database. If they actually
search for a record, the new record is added.

What's the simplest and safest way to protect the user from this
series of events? I cannot do to much rewriting of the code or I might
cause other issues.

Thanks,
Eric

[Eric]

Thank you for your help.

I found the problem. I had added a line of code to edit a form field
before the new record was validated.

The new record was dirty and could not be discarded by the existing
code.

I moved the code into the validation and everything works fine.

Thanks,
Eric