Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Forms Programming / November 2005

Tip: Looking for answers? Try searching our database.

Cancel Anticipated User Button Pushing

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
MBoozer - 30 Nov 2005 04:06 GMT
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 - 30 Nov 2005 04:38 GMT
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???).

Signature

Albert D. Kallal   (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com
http://www.members.shaw.ca/AlbertKallal

MBoozer - 30 Nov 2005 05:10 GMT
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?
>
[quoted text clipped - 69 lines]
> to train, or give users the option to delete a record they accidentally
> added? - how do they do this now???).
Albert D.Kallal - 30 Nov 2005 06:46 GMT
> 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
[quoted text clipped - 4 lines]
> 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

Signature

Albert D. Kallal   (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com
http://www.members.shaw.ca/AlbertKallal

Albert D.Kallal - 30 Nov 2005 06:52 GMT
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

Signature

Albert D. Kallal   (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com
http://www.members.shaw.ca/AlbertKallal

MBoozer - 30 Nov 2005 07:35 GMT
Thanks Albert. Just got done recoding. Works just the way I need (want) it
to. I really appreciate your help. Thanks.

Mike

>  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?).
[quoted text clipped - 5 lines]
>
> me.requery
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.