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