Cancel Anticipated User Button Pushing

[MBoozer]

Trying to create a simple cancel code in case user gets crazy hitting
buttons. The code below works great EXCEPT that when user hits button a
"second" time, vberror message pops up saying command undo isn't availble and
asking to debug. Is my code too complex or is there something I can put in to
stop the error?

If Me.NewRecord And IsNull(Me.[SupplierName]) Then
DoCmd.CancelEvent
Me.Requery 'sets focus back to top of list
'when user hits add newrecord button but has not entered any data into field

ElseIf Me.NewRecord And Not IsNull(Me.[SupplierName]) Then
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
DoCmd.CancelEvent
Me.Requery
'when user hits newrecord and enters data into field

ElseIf Not IsNull(Me.SupplierName) Then
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
DoCmd.CancelEvent
Me.Requery
'when user edits an existing record and changes their mind
End If

 

[Albert D.Kallal]

Where exactly are you placing the code you posted?

code that verifies required fields needs to be placed in the before update
event of the form.

code that moves to a new record, or adds a new record simply needs to be
placed behind a button, or whatever you plan/are using.

Using the "standard" navigation buttons will even suffice. (or you own!!).
Either way, if the user moves to a new record, and does not enter any data,
then trying to move to another new record WILL NOT OCCUR.

So, put your field verification code in the before update event (and note
how it has a cancel event).

Right now you have code that is just going to tie you up in knots..and are
making this really difficult. Further, the user might not try and move to a
new record, but close the form..and you STILL want to verify those fields.

Step back here for a second...

If Me.NewRecord And IsNull(Me.[SupplierName]) Then

There is no need to use me.NewRecord here. You mean I can go and erase the
supplier name in a existing record (not a new one), then hit the add a
record button, and yet the supplier name is now blank? (this is not good).

The trick to making this all work as smooth as glass is to use the correct
events in the form, and when you do, this will all work, and work with a min
of code.

Note that the code in the before update event DOES NOT RUN if you don't
modify the record!

And, if you don't modify the record..then it does not need to be saved....

So, lets first build our add new record button. You simply need:

DoCmd.GoToRecord , , acNewRec

The above is ONE line of code. If the user hits the button more then once,
access will NOT navigate to a new record. So, this solves the duplication,
or simply prevents the user from be able to add a whole bunch of blank
records. Ok??? One of code!!! Further, our code that checks for required
fields (in the before update event) will CANCEL the move to the new record
for us if there is any problem (and, in fact will cancel a form close...and
a zillion other events that can occur that will want to SAVE the current
record. (you can't write your check code in 20 places....so, put it in the
before update event).

Note that you can set the required fields in the table design view, and not
have to write any code.

However, in most cases, writing some code to "verify" the input is a good
idea, an gives the user a nice message.

As mentioned, the before update event is what we want. Here what the code
could look like:


If IsNull(Me.[SupplierName]) = true Then
cancel = true
msgbox "suppler name is required",vbExclamation,"required"
me.SupplierName.SetFocus
exit sub
end if


So, if a person navigates to a new record, and does nothing..then the blank
record will be discarded.

If the person edits this new record, and changes their mind, then they must
either go edit->undo, or you provide a "delete" button.? (how are planning
to train, or give users the option to delete a record they accidentally
added? - how do they do this now???).

 

[MBoozer]

Thank you for your help Al. I understand waht you are saying. I put your code
in the before update event. The form has three buttons that I want to control
(add/edit/delete). When I now try any of these with your code, I can hit the
escape button (after editing a record but not saving it, to cancel a delete
prompt, or to cancel partial entry of a new record). However, the focus is
not returning to suppliername. Also, when you are on a new record and then
hit escape, the cursor just "sits" there in the new record field. Since some
users won't know about the escape key, I wanted to give them a cancel button
that onclick emulates the escape key while returning the focus back up to the
top of the list. Any ideas?

Where exactly are you placing the code you posted?

code that verifies required fields needs to be placed in the before update
event of the form.

code that moves to a new record, or adds a new record simply needs to be
placed behind a button, or whatever you plan/are using.

Using the "standard" navigation buttons will even suffice. (or you own!!).
Either way, if the user moves to a new record, and does not enter any data,
then trying to move to another new record WILL NOT OCCUR.

So, put your field verification code in the before update event (and note
how it has a cancel event).

Right now you have code that is just going to tie you up in knots..and are
making this really difficult. Further, the user might not try and move to a
new record, but close the form..and you STILL want to verify those fields.

Step back here for a second...

If Me.NewRecord And IsNull(Me.[SupplierName]) Then

There is no need to use me.NewRecord here. You mean I can go and erase the
supplier name in a existing record (not a new one), then hit the add a
record button, and yet the supplier name is now blank? (this is not good).

The trick to making this all work as smooth as glass is to use the correct
events in the form, and when you do, this will all work, and work with a min
of code.

Note that the code in the before update event DOES NOT RUN if you don't
modify the record!

And, if you don't modify the record..then it does not need to be saved....

So, lets first build our add new record button. You simply need:

DoCmd.GoToRecord , , acNewRec

The above is ONE line of code. If the user hits the button more then once,
access will NOT navigate to a new record. So, this solves the duplication,
or simply prevents the user from be able to add a whole bunch of blank
records. Ok??? One of code!!! Further, our code that checks for required
fields (in the before update event) will CANCEL the move to the new record
for us if there is any problem (and, in fact will cancel a form close...and
a zillion other events that can occur that will want to SAVE the current
record. (you can't write your check code in 20 places....so, put it in the
before update event).

Note that you can set the required fields in the table design view, and not
have to write any code.

However, in most cases, writing some code to "verify" the input is a good
idea, an gives the user a nice message.

As mentioned, the before update event is what we want. Here what the code
could look like:


If IsNull(Me.[SupplierName]) = true Then
cancel = true
msgbox "suppler name is required",vbExclamation,"required"
me.SupplierName.SetFocus
exit sub
end if


So, if a person navigates to a new record, and does nothing..then the blank
record will be discarded.

If the person edits this new record, and changes their mind, then they must
either go edit->undo, or you provide a "delete" button.? (how are planning
to train, or give users the option to delete a record they accidentally
added? - how do they do this now???).

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal

 

[Albert D.Kallal]

Thank you for your help Al. I understand waht you are saying. I put your
code
in the before update event. The form has three buttons that I want to
control
(add/edit/delete).

Ok...good...we will have a delete button (I usually provide a delete button
also - makes it easy for users).

When I now try any of these with your code, I can hit the
escape button (after editing a record but not saving it, to cancel a
delete
prompt, or to cancel partial entry of a new record).

Ok...so far so good...

However, the focus is
not returning to suppliername.
Hum...ok.

Also, when you are on a new record and then
hit escape, the cursor just "sits" there in the new record field. Since
some
users won't know about the escape key, I wanted to give them a cancel
button
that onclick emulates the escape key while returning the focus back up to
the
top of the list. Any ideas?

Good...I agree with the above too!!

So, if we need a cancel button, the code behind the cancel button can be:

if isnull(me!id) = false then
' we check for a primary key id...if the record is blank, then
' no primary key..and we don't need to do anything to cancel...

me.undo

end if

' you now said that when you cancel, you want to set the cursor to the top
of the form (I assuming this a continues form?).

me.requery
me.
So, the above code snip takes care of our cancel. And, we can also make the
Escape key do the same as above..but you do same to hint that users likely
will not know about his this key...so, we can forget about it...

' our delete key code

if isnull(me!id) = false then
' we check for a primary key id...if the record is blank, then
' no primary key..and we don't need to do anything to
' delete this record.

' however, the user MAY have editing this record, so we
' need to un-do the edits BEFORE we delete the record
' (or, we could force a disk write also...)
me.undo
if isnull(me!id) = false then
' the undo might have made the record blank, so, we
' do have to test for no id after the undo
currentdb.Execute "delete * from tblDetails where id = " & me!id
end if
end if

me.Requery

That should do it....

Also, if you wish, you can put the cursor back into the supplier field. In
the above two code examples, right after the

me.Requery

You an put a

me.SupplierName.SetFocus

We have not yet dealt with the Esc key, and we can also have that key run
our above "cancel" button code.

I would use the forms keydown event (and set the key preview to yes). The
code then would be

if keyCode = 27 then
keycode = 0 ' don't let ms-access see the key
Call "name of our cancel buttion code routine above"
end if

 

[Albert D.Kallal]

you now said that when you cancel, you want to set the cursor to the top
of the form (I assuming this a continues form?).

me.requery
me.

The above is a type o....it should read

me.requery

 

[MBoozer]

Thanks Albert. Just got done recoding. Works just the way I need (want) it
to. I really appreciate your help. Thanks.

Mike