Cancel still adds record

No false modesty. I think that what I have learned I have learned pretty
well, but there is much about which I haven't the slightest idea.

"Gina Whipp" wrote in message
...
While I am nowhere near being a guru...

Ohhhh come on Bruce ;-)


"BruceM" wrote in message
...
While I am nowhere near being a guru, I beieve that the DoMenuItem line
of code you have provided can be replaced with:
Me.Undo
except that I think it needs to be added within the If statement, just
before DoCmd.Close. If it is placed after the If statement then the code
will run, and the record will be undone, no matter whether the user
selects OK or Cancel. I think you could avoid that by using vbOK instead
of vbCancel, and adding Cancel = True:

If IsNull(Me![CustomerIDCode]) Then
If MsgBox("'CustomerIDCode' must contain a value." _
& Chr(13) & Chr(10) _
& "Press 'OK' to return and enter a value." _
& Chr(13) & Chr(10) _
& "Press 'Cancel' to abort the record.", _
vbOKCancel, "A Required field is Null") = _
vbOK Then
Cancel = True
Else
Me.Undo
DoCmd.Close
End If
End If

Just as a thought, I find it easier to work with the code if I am not
dealing with a long string in the middle of an If statement or something
like that. I like to start with:

dim strMsg as String
strMsg = "CustomerIDCode must contain a value...etc."

Then in the msgbox line of code:

If msgbox strMsg = vbCancel Then
etc.
"Gina Whipp" wrote in message
...
All it is Un-Do. I had the same problem you did. I realized that the
record is created as soon as the form to add new is opened. So I was
trying to cancel a record that in a sense was already added and it
couldn't. So I had to Un-Do to get rid of the new record and then
cancel to cancel the event.

I finally figured it out by reading about how and when form events are
triggered.

It probably could be better explained by one of the gurus but I hope
that helps.

Gina

"Tee See" wrote in message
.. .
Thanks Gina that seems to work .... Would you be able to explain
briefly what that line of code is about?
Greatly appreciate the help.

"Gina Whipp" wrote in message
...
Try adding:

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70

one space downs after the last End If

HTH,
Gina

"Ed Robichaud" wrote in message
...
Looks like it should work. This code is fired on the form's
BeforeUpdate event? And CustomerID is not an auto-number with one or
more other fields defaulted?
-Ed

"Tee See" wrote in message
.. .
On a popup form I have a button to close the form. If the form is
open to ADD a record and the user changes his/her mind I'd like to
close the form without adding a blank record. The following bit of
code (I thought) would handle this but I still get a blank rcord
added. The code by the way comes right from an example page within
access help.

If IsNull(Me![CustomerIDCode]) Then
If MsgBox("'CustomerIDCode' must contain a value." _
& Chr(13) & Chr(10) _
& "Press 'OK' to return and enter a value." _
& Chr(13) & Chr(10) _
& "Press 'Cancel' to abort the record.", _
vbOKCancel, "A Required field is Null") = _
vbCancel Then
DoCmd.Close
End If
End If

Any advice sincerely appreciated

While you two are trying to decide who is a guru and who isn't .... This
ABSOLUTE NOVICE would like to thank all four of you who have responded to
this initial question. I may be wrong but the more input each question gets
the more knowledge is imparted, and thought processes stimulated.

A sincere thanks to all!

"BruceM" wrote in message
...
No false modesty. I think that what I have learned I have learned pretty
well, but there is much about which I haven't the slightest idea.

"Gina Whipp" wrote in message
...
While I am nowhere near being a guru...

Ohhhh come on Bruce ;-)


"BruceM" wrote in message
...
While I am nowhere near being a guru, I beieve that the DoMenuItem line
of code you have provided can be replaced with:
Me.Undo
except that I think it needs to be added within the If statement, just
before DoCmd.Close. If it is placed after the If statement then the
code will run, and the record will be undone, no matter whether the user
selects OK or Cancel. I think you could avoid that by using vbOK
instead of vbCancel, and adding Cancel = True:

If IsNull(Me![CustomerIDCode]) Then
If MsgBox("'CustomerIDCode' must contain a value." _
& Chr(13) & Chr(10) _
& "Press 'OK' to return and enter a value." _
& Chr(13) & Chr(10) _
& "Press 'Cancel' to abort the record.", _
vbOKCancel, "A Required field is Null") = _
vbOK Then
Cancel = True
Else
Me.Undo
DoCmd.Close
End If
End If

Just as a thought, I find it easier to work with the code if I am not
dealing with a long string in the middle of an If statement or something
like that. I like to start with:

dim strMsg as String
strMsg = "CustomerIDCode must contain a value...etc."

Then in the msgbox line of code:

If msgbox strMsg = vbCancel Then
etc.
"Gina Whipp" wrote in message
...
All it is Un-Do. I had the same problem you did. I realized that the
record is created as soon as the form to add new is opened. So I was
trying to cancel a record that in a sense was already added and it
couldn't. So I had to Un-Do to get rid of the new record and then
cancel to cancel the event.

I finally figured it out by reading about how and when form events are
triggered.

It probably could be better explained by one of the gurus but I hope
that helps.

Gina

"Tee See" wrote in message
.. .
Thanks Gina that seems to work .... Would you be able to explain
briefly what that line of code is about?
Greatly appreciate the help.

"Gina Whipp" wrote in message
...
Try adding:

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70

one space downs after the last End If

HTH,
Gina

"Ed Robichaud" wrote in message
...
Looks like it should work. This code is fired on the form's
BeforeUpdate event? And CustomerID is not an auto-number with one
or more other fields defaulted?
-Ed

"Tee See" wrote in message
.. .
On a popup form I have a button to close the form. If the form is
open to ADD a record and the user changes his/her mind I'd like to
close the form without adding a blank record. The following bit of
code (I thought) would handle this but I still get a blank rcord
added. The code by the way comes right from an example page within
access help.

If IsNull(Me![CustomerIDCode]) Then
If MsgBox("'CustomerIDCode' must contain a value." _
& Chr(13) & Chr(10) _
& "Press 'OK' to return and enter a value." _
& Chr(13) & Chr(10) _
& "Press 'Cancel' to abort the record.", _
vbOKCancel, "A Required field is Null") = _
vbCancel Then
DoCmd.Close
End If
End If

Any advice sincerely appreciated

Bruce ... You're "Just as a thought" issue makes alot of sense to me in
spite of the fact that the code I submitted came from MS access HELP

"BruceM" wrote in message
...
While I am nowhere near being a guru, I beieve that the DoMenuItem line of
code you have provided can be replaced with:
Me.Undo
except that I think it needs to be added within the If statement, just
before DoCmd.Close. If it is placed after the If statement then the code
will run, and the record will be undone, no matter whether the user
selects OK or Cancel. I think you could avoid that by using vbOK instead
of vbCancel, and adding Cancel = True:

If IsNull(Me![CustomerIDCode]) Then
If MsgBox("'CustomerIDCode' must contain a value." _
& Chr(13) & Chr(10) _
& "Press 'OK' to return and enter a value." _
& Chr(13) & Chr(10) _
& "Press 'Cancel' to abort the record.", _
vbOKCancel, "A Required field is Null") = _
vbOK Then
Cancel = True
Else
Me.Undo
DoCmd.Close
End If
End If

Just as a thought, I find it easier to work with the code if I am not
dealing with a long string in the middle of an If statement or something
like that. I like to start with:

dim strMsg as String
strMsg = "CustomerIDCode must contain a value...etc."

Then in the msgbox line of code:

If msgbox strMsg = vbCancel Then
etc.
"Gina Whipp" wrote in message
...
All it is Un-Do. I had the same problem you did. I realized that the
record is created as soon as the form to add new is opened. So I was
trying to cancel a record that in a sense was already added and it
couldn't. So I had to Un-Do to get rid of the new record and then cancel
to cancel the event.

I finally figured it out by reading about how and when form events are
triggered.

It probably could be better explained by one of the gurus but I hope that
helps.

Gina

"Tee See" wrote in message
.. .
Thanks Gina that seems to work .... Would you be able to explain briefly
what that line of code is about?
Greatly appreciate the help.

"Gina Whipp" wrote in message
...
Try adding:

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70

one space downs after the last End If

HTH,
Gina

"Ed Robichaud" wrote in message
...
Looks like it should work. This code is fired on the form's
BeforeUpdate event? And CustomerID is not an auto-number with one or
more other fields defaulted?
-Ed

"Tee See" wrote in message
.. .
On a popup form I have a button to close the form. If the form is
open to ADD a record and the user changes his/her mind I'd like to
close the form without adding a blank record. The following bit of
code (I thought) would handle this but I still get a blank rcord
added. The code by the way comes right from an example page within
access help.

If IsNull(Me![CustomerIDCode]) Then
If MsgBox("'CustomerIDCode' must contain a value." _
& Chr(13) & Chr(10) _
& "Press 'OK' to return and enter a value." _
& Chr(13) & Chr(10) _
& "Press 'Cancel' to abort the record.", _
vbOKCancel, "A Required field is Null") = _
vbCancel Then
DoCmd.Close
End If
End If

Any advice sincerely appreciated

MS Access Help can be a bit fragmented sometimes. There may be a code
example, for instance, but with no explanation close at hand about the
syntax. I regard Help as one resource among many, and not necessarily the
best one in all cases. Much of what I have learned has come from reading
messages here, and following links that are in some of them. Good luck with
your project.

"Tee See" wrote in message
.. .
Bruce ... You're "Just as a thought" issue makes alot of sense to me in
spite of the fact that the code I submitted came from MS access HELP

"BruceM" wrote in message
...
While I am nowhere near being a guru, I beieve that the DoMenuItem line
of code you have provided can be replaced with:
Me.Undo
except that I think it needs to be added within the If statement, just
before DoCmd.Close. If it is placed after the If statement then the code
will run, and the record will be undone, no matter whether the user
selects OK or Cancel. I think you could avoid that by using vbOK instead
of vbCancel, and adding Cancel = True:

If IsNull(Me![CustomerIDCode]) Then
If MsgBox("'CustomerIDCode' must contain a value." _
& Chr(13) & Chr(10) _
& "Press 'OK' to return and enter a value." _
& Chr(13) & Chr(10) _
& "Press 'Cancel' to abort the record.", _
vbOKCancel, "A Required field is Null") = _
vbOK Then
Cancel = True
Else
Me.Undo
DoCmd.Close
End If
End If

Just as a thought, I find it easier to work with the code if I am not
dealing with a long string in the middle of an If statement or something
like that. I like to start with:

dim strMsg as String
strMsg = "CustomerIDCode must contain a value...etc."

Then in the msgbox line of code:

If msgbox strMsg = vbCancel Then
etc.
"Gina Whipp" wrote in message
...
All it is Un-Do. I had the same problem you did. I realized that the
record is created as soon as the form to add new is opened. So I was
trying to cancel a record that in a sense was already added and it
couldn't. So I had to Un-Do to get rid of the new record and then
cancel to cancel the event.

I finally figured it out by reading about how and when form events are
triggered.

It probably could be better explained by one of the gurus but I hope
that helps.

Gina

"Tee See" wrote in message
.. .
Thanks Gina that seems to work .... Would you be able to explain
briefly what that line of code is about?
Greatly appreciate the help.

"Gina Whipp" wrote in message
...
Try adding:

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70

one space downs after the last End If

HTH,
Gina

"Ed Robichaud" wrote in message
...
Looks like it should work. This code is fired on the form's
BeforeUpdate event? And CustomerID is not an auto-number with one or
more other fields defaulted?
-Ed

"Tee See" wrote in message
.. .
On a popup form I have a button to close the form. If the form is
open to ADD a record and the user changes his/her mind I'd like to
close the form without adding a blank record. The following bit of
code (I thought) would handle this but I still get a blank rcord
added. The code by the way comes right from an example page within
access help.

If IsNull(Me![CustomerIDCode]) Then
If MsgBox("'CustomerIDCode' must contain a value." _
& Chr(13) & Chr(10) _
& "Press 'OK' to return and enter a value." _
& Chr(13) & Chr(10) _
& "Press 'Cancel' to abort the record.", _
vbOKCancel, "A Required field is Null") = _
vbCancel Then
DoCmd.Close
End If
End If

Any advice sincerely appreciated

The code you posted belongs in the form's BeforeUpdate event. Not in the
events suggested by the others. The BeforeUpdate event is the ONLY place
where you are certain to trap the record save since no matter what causes
the record save, all roads lead to the BeforeUpdate event for dirty records.
An even better solution for required fields is to set their Required
property to Yes in the table definition. That way, no matter how the record
gets added, Jet will not allow the record to be saved if the required fields
are null. In your forms, you can trap the standard error message and
replace it with your own if you prefer.

If IsNull(Me![CustomerIDCode]) Then
If MsgBox("'CustomerIDCode' must contain a value." _
& Chr(13) & Chr(10) _
& "Press 'OK' to return and enter a value." _
& Chr(13) & Chr(10) _
& "Press 'Cancel' to abort the record.", _
vbOKCancel, "A Required field is Null") = _
vbCancel Then
Cancel = True
Me.Undo
End If
End If

"Tee See" wrote in message
.. .
On a popup form I have a button to close the form. If the form is open to
ADD a record and the user changes his/her mind I'd like to close the form
without adding a blank record. The following bit of code (I thought) would
handle this but I still get a blank rcord added. The code by the way comes
right from an example page within access help.

If IsNull(Me![CustomerIDCode]) Then
If MsgBox("'CustomerIDCode' must contain a value." _
& Chr(13) & Chr(10) _
& "Press 'OK' to return and enter a value." _
& Chr(13) & Chr(10) _
& "Press 'Cancel' to abort the record.", _
vbOKCancel, "A Required field is Null") = _
vbCancel Then
DoCmd.Close
End If
End If

Any advice sincerely appreciated

Bet you wish I had finished with this ..... One last piece of advice please.
I'v changed the code based on all the previous discussions as below. Now I
can't even get the syntax correct in defining the strMsg. I can do it when I
type one long continuous line but can't get it when I use "_&".
Also the OK button on the message box will not allow entry into the form it
just closes and when I do add data to the form the button will not close the
form. Here's the code.
Private Sub Command20_Click()
On Error GoTo Err_Command20_Click

Dim strMsg As String
strMsg = "'CustomerIDCode' must contain a value."_
& Chr(13)& Chr(10)_
& "Press 'OK' to return and enter a value."_
& Chr(13) & Chr(10)_
& "Press 'Cancel' to abort."

If IsNull(Me![CustomerIDCode]) Then
If MsgBox(strMsg, vbOKCancel, "A Required Field is Null") = vbOK Then
Cancel = True
Else
Me.Undo
DoCmd.Close
End If

End If
Forms!frmMaterialMasterMain!SISItemCode.SetFocus

Exit_Command20_Click:
Exit Sub

Err_Command20_Click:
MsgBox Err.Description
Resume Exit_Command20_Click



"BruceM" wrote in message
...
MS Access Help can be a bit fragmented sometimes. There may be a code
example, for instance, but with no explanation close at hand about the
syntax. I regard Help as one resource among many, and not necessarily the
best one in all cases. Much of what I have learned has come from reading
messages here, and following links that are in some of them. Good luck
with your project.

"Tee See" wrote in message
.. .
Bruce ... You're "Just as a thought" issue makes alot of sense to me in
spite of the fact that the code I submitted came from MS access HELP

"BruceM" wrote in message
...
While I am nowhere near being a guru, I beieve that the DoMenuItem line
of code you have provided can be replaced with:
Me.Undo
except that I think it needs to be added within the If statement, just
before DoCmd.Close. If it is placed after the If statement then the
code will run, and the record will be undone, no matter whether the user
selects OK or Cancel. I think you could avoid that by using vbOK
instead of vbCancel, and adding Cancel = True:

If IsNull(Me![CustomerIDCode]) Then
If MsgBox("'CustomerIDCode' must contain a value." _
& Chr(13) & Chr(10) _
& "Press 'OK' to return and enter a value." _
& Chr(13) & Chr(10) _
& "Press 'Cancel' to abort the record.", _
vbOKCancel, "A Required field is Null") = _
vbOK Then
Cancel = True
Else
Me.Undo
DoCmd.Close
End If
End If

Just as a thought, I find it easier to work with the code if I am not
dealing with a long string in the middle of an If statement or something
like that. I like to start with:

dim strMsg as String
strMsg = "CustomerIDCode must contain a value...etc."

Then in the msgbox line of code:

If msgbox strMsg = vbCancel Then
etc.
"Gina Whipp" wrote in message
...
All it is Un-Do. I had the same problem you did. I realized that the
record is created as soon as the form to add new is opened. So I was
trying to cancel a record that in a sense was already added and it
couldn't. So I had to Un-Do to get rid of the new record and then
cancel to cancel the event.

I finally figured it out by reading about how and when form events are
triggered.

It probably could be better explained by one of the gurus but I hope
that helps.

Gina

"Tee See" wrote in message
.. .
Thanks Gina that seems to work .... Would you be able to explain
briefly what that line of code is about?
Greatly appreciate the help.

"Gina Whipp" wrote in message
...
Try adding:

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70

one space downs after the last End If

HTH,
Gina

"Ed Robichaud" wrote in message
...
Looks like it should work. This code is fired on the form's
BeforeUpdate event? And CustomerID is not an auto-number with one
or more other fields defaulted?
-Ed

"Tee See" wrote in message
.. .
On a popup form I have a button to close the form. If the form is
open to ADD a record and the user changes his/her mind I'd like to
close the form without adding a blank record. The following bit of
code (I thought) would handle this but I still get a blank rcord
added. The code by the way comes right from an example page within
access help.

If IsNull(Me![CustomerIDCode]) Then
If MsgBox("'CustomerIDCode' must contain a value." _
& Chr(13) & Chr(10) _
& "Press 'OK' to return and enter a value." _
& Chr(13) & Chr(10) _
& "Press 'Cancel' to abort the record.", _
vbOKCancel, "A Required field is Null") = _
vbCancel Then
DoCmd.Close
End If
End If

Any advice sincerely appreciated

If the MsgBox = vbCancel(The cancel button) then the form closes, however if
the MsgBox = vbOK (the OK button) then I'm supposed to get the opportunity
to actually input Data
If I move all this code to the BeforeUpdate event the only code I have under
my buttons OnClick event is DoCmd.Close and I don't get the opportunity to
enter data in the form it just closes.
Thanks for getting involved. This is a learning opportunity.
"Pat Hartman(MVP)" wrote in message
...
The code you posted belongs in the form's BeforeUpdate event. Not in the
events suggested by the others. The BeforeUpdate event is the ONLY place
where you are certain to trap the record save since no matter what causes
the record save, all roads lead to the BeforeUpdate event for dirty
records. An even better solution for required fields is to set their
Required property to Yes in the table definition. That way, no matter how
the record gets added, Jet will not allow the record to be saved if the
required fields are null. In your forms, you can trap the standard error
message and replace it with your own if you prefer.

If IsNull(Me![CustomerIDCode]) Then
If MsgBox("'CustomerIDCode' must contain a value." _
& Chr(13) & Chr(10) _
& "Press 'OK' to return and enter a value." _
& Chr(13) & Chr(10) _
& "Press 'Cancel' to abort the record.", _
vbOKCancel, "A Required field is Null") = _
vbCancel Then
Cancel = True
Me.Undo
End If
End If

"Tee See" wrote in message
.. .
On a popup form I have a button to close the form. If the form is open to
ADD a record and the user changes his/her mind I'd like to close the form
without adding a blank record. The following bit of code (I thought)
would handle this but I still get a blank rcord added. The code by the
way comes right from an example page within access help.

If IsNull(Me![CustomerIDCode]) Then
If MsgBox("'CustomerIDCode' must contain a value." _
& Chr(13) & Chr(10) _
& "Press 'OK' to return and enter a value." _
& Chr(13) & Chr(10) _
& "Press 'Cancel' to abort the record.", _
vbOKCancel, "A Required field is Null") = _
vbCancel Then
DoCmd.Close
End If
End If

Any advice sincerely appreciated

A further question ... If the MsgBox=vbCancel why is the code "cancel=true"
required?

"Pat Hartman(MVP)" wrote in message
...
The code you posted belongs in the form's BeforeUpdate event. Not in the
events suggested by the others. The BeforeUpdate event is the ONLY place
where you are certain to trap the record save since no matter what causes
the record save, all roads lead to the BeforeUpdate event for dirty
records. An even better solution for required fields is to set their
Required property to Yes in the table definition. That way, no matter how
the record gets added, Jet will not allow the record to be saved if the
required fields are null. In your forms, you can trap the standard error
message and replace it with your own if you prefer.

If IsNull(Me![CustomerIDCode]) Then
If MsgBox("'CustomerIDCode' must contain a value." _
& Chr(13) & Chr(10) _
& "Press 'OK' to return and enter a value." _
& Chr(13) & Chr(10) _
& "Press 'Cancel' to abort the record.", _
vbOKCancel, "A Required field is Null") = _
vbCancel Then
Cancel = True
Me.Undo
End If
End If

"Tee See" wrote in message
.. .
On a popup form I have a button to close the form. If the form is open to
ADD a record and the user changes his/her mind I'd like to close the form
without adding a blank record. The following bit of code (I thought)
would handle this but I still get a blank rcord added. The code by the
way comes right from an example page within access help.

If IsNull(Me![CustomerIDCode]) Then
If MsgBox("'CustomerIDCode' must contain a value." _
& Chr(13) & Chr(10) _
& "Press 'OK' to return and enter a value." _
& Chr(13) & Chr(10) _
& "Press 'Cancel' to abort the record.", _
vbOKCancel, "A Required field is Null") = _
vbCancel Then
DoCmd.Close
End If
End If

Any advice sincerely appreciated

There needs to be a space before the underscore.

For the other part of your question, is the form in question
frmMaterialMasterMain? If so, try setting the focus to SISItemCode before
Cancel = True. Also, if it is a control on the form you should be able to
set focus with Me.SISItemCode.SetFocus.
When I mentioned using vbOK and Cancel = True it was not so much a
suggestion as an illustration of another approach. You could stick with
your original approach of:
If ... = vbCancel Then
Me.Undo
DoCmd.Close
Else
Me.SISItemCode.SetFocus
End If

That would eliminate the Cancel = True line of code. When confronted with
such choices I tend to go with the one that involves less code, not that
Cancel = True is a big deal. Either way should work. I'm not sure why your
form closes no matter what you do.

If SISItemCode is on another form, you have added a new wrinkle, and I don't
know what you intend. In any case you would do well when asking a question
in this forum to define forms and controls. People reading your postings do
not see your database, and must rely only on what you tell us.
I have another unsolicited suggestion. I find error handling to be much
more helpful if I use something like the following after Err_Command2_Click:
msgbox "Error #" & Err.Number & ", " & Err.Description & " - Command20"
It gives you more to work with when an error occurs. You haven't mentioned
an getting an error message, so this is just something else I'm tossing in
here.

I expect the reason you cannot close the form is the SetFocus command after
the If statement. Cancel = True cancels the rest of the event, but in the
case of clicking Cancel in the message box Access does not encounter Cancel
= True, and therefore continues with the rest of the code, which is to set
focus. That may be overriding the Close command. If setting focus is
supposed to accompany clicking OK, it should be part of the Then section of
the If statement.

One other thought is that there could be a default value or something that
prevents CustomerIDCode from being null. To test you could add a temporary
message box right after the If IsNull line:
msgbox "ID Is Null"
If leaving the field blank doesn't produce that message box, that would
explain why the next If statement always runs. You may need to use:
If Me.CustomerIDCode = ""

I am, as I think I have mentioned, relatively new at this stuff. Answering
questions gets me to thinking about things in my own projects, so I am
always glad when my suggestions can prove useful. If what I have suggested
doesn't work, post back. I'm learning too, and would like to know what
works and what doesn't.

"Tee See" wrote in message
news
Bet you wish I had finished with this ..... One last piece of advice
please.
I'v changed the code based on all the previous discussions as below. Now I
can't even get the syntax correct in defining the strMsg. I can do it when
I type one long continuous line but can't get it when I use "_&".
Also the OK button on the message box will not allow entry into the form
it just closes and when I do add data to the form the button will not
close the form. Here's the code.
Private Sub Command20_Click()
On Error GoTo Err_Command20_Click

Dim strMsg As String
strMsg = "'CustomerIDCode' must contain a value."_
& Chr(13)& Chr(10)_
& "Press 'OK' to return and enter a value."_
& Chr(13) & Chr(10)_
& "Press 'Cancel' to abort."

If IsNull(Me![CustomerIDCode]) Then
If MsgBox(strMsg, vbOKCancel, "A Required Field is Null") = vbOK Then
Cancel = True
Else
Me.Undo
DoCmd.Close
End If

End If
Forms!frmMaterialMasterMain!SISItemCode.SetFocus

Exit_Command20_Click:
Exit Sub

Err_Command20_Click:
MsgBox Err.Description
Resume Exit_Command20_Click



"BruceM" wrote in message
...
MS Access Help can be a bit fragmented sometimes. There may be a code
example, for instance, but with no explanation close at hand about the
syntax. I regard Help as one resource among many, and not necessarily
the best one in all cases. Much of what I have learned has come from
reading messages here, and following links that are in some of them.
Good luck with your project.

"Tee See" wrote in message
.. .
Bruce ... You're "Just as a thought" issue makes alot of sense to me in
spite of the fact that the code I submitted came from MS access HELP

"BruceM" wrote in message
...
While I am nowhere near being a guru, I beieve that the DoMenuItem line
of code you have provided can be replaced with:
Me.Undo
except that I think it needs to be added within the If statement, just
before DoCmd.Close. If it is placed after the If statement then the
code will run, and the record will be undone, no matter whether the
user selects OK or Cancel. I think you could avoid that by using vbOK
instead of vbCancel, and adding Cancel = True:

If IsNull(Me![CustomerIDCode]) Then
If MsgBox("'CustomerIDCode' must contain a value." _
& Chr(13) & Chr(10) _
& "Press 'OK' to return and enter a value." _
& Chr(13) & Chr(10) _
& "Press 'Cancel' to abort the record.", _
vbOKCancel, "A Required field is Null") = _
vbOK Then
Cancel = True
Else
Me.Undo
DoCmd.Close
End If
End If

Just as a thought, I find it easier to work with the code if I am not
dealing with a long string in the middle of an If statement or
something like that. I like to start with:

dim strMsg as String
strMsg = "CustomerIDCode must contain a value...etc."

Then in the msgbox line of code:

If msgbox strMsg = vbCancel Then
etc.
"Gina Whipp" wrote in message
...
All it is Un-Do. I had the same problem you did. I realized that the
record is created as soon as the form to add new is opened. So I was
trying to cancel a record that in a sense was already added and it
couldn't. So I had to Un-Do to get rid of the new record and then
cancel to cancel the event.

I finally figured it out by reading about how and when form events are
triggered.

It probably could be better explained by one of the gurus but I hope
that helps.

Gina

"Tee See" wrote in message
.. .
Thanks Gina that seems to work .... Would you be able to explain
briefly what that line of code is about?
Greatly appreciate the help.

"Gina Whipp" wrote in message
...
Try adding:

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70

one space downs after the last End If

HTH,
Gina

"Ed Robichaud" wrote in message
...
Looks like it should work. This code is fired on the form's
BeforeUpdate event? And CustomerID is not an auto-number with one
or more other fields defaulted?
-Ed

"Tee See" wrote in message
.. .
On a popup form I have a button to close the form. If the form is
open to ADD a record and the user changes his/her mind I'd like to
close the form without adding a blank record. The following bit of
code (I thought) would handle this but I still get a blank rcord
added. The code by the way comes right from an example page within
access help.

If IsNull(Me![CustomerIDCode]) Then
If MsgBox("'CustomerIDCode' must contain a value." _
& Chr(13) & Chr(10) _
& "Press 'OK' to return and enter a value." _
& Chr(13) & Chr(10) _
& "Press 'Cancel' to abort the record.", _
vbOKCancel, "A Required field is Null") = _
vbCancel Then
DoCmd.Close
End If
End If

Any advice sincerely appreciated

I agree that the form's Before Update event is the place to run the code.
That's where experience comes in. I should have picked up on that. Don't
know why I didn't. You can save the record at any time, which will cause
the Before Update event to run. If you do not explicitly save the record,
it will be saved when attempting to close the form or navigate away from the
record. If there is a Before Update event, it will run for every record you
add or modify.

"Tee See" wrote in message
. ..
If the MsgBox = vbCancel(The cancel button) then the form closes, however
if the MsgBox = vbOK (the OK button) then I'm supposed to get the
opportunity to actually input Data
If I move all this code to the BeforeUpdate event the only code I have
under my buttons OnClick event is DoCmd.Close and I don't get the
opportunity to enter data in the form it just closes.
Thanks for getting involved. This is a learning opportunity.
"Pat Hartman(MVP)" wrote in message
...
The code you posted belongs in the form's BeforeUpdate event. Not in the
events suggested by the others. The BeforeUpdate event is the ONLY place
where you are certain to trap the record save since no matter what causes
the record save, all roads lead to the BeforeUpdate event for dirty
records. An even better solution for required fields is to set their
Required property to Yes in the table definition. That way, no matter
how the record gets added, Jet will not allow the record to be saved if
the required fields are null. In your forms, you can trap the standard
error message and replace it with your own if you prefer.

If IsNull(Me![CustomerIDCode]) Then
If MsgBox("'CustomerIDCode' must contain a value." _
& Chr(13) & Chr(10) _
& "Press 'OK' to return and enter a value." _
& Chr(13) & Chr(10) _
& "Press 'Cancel' to abort the record.", _
vbOKCancel, "A Required field is Null") = _
vbCancel Then
Cancel = True
Me.Undo
End If
End If

"Tee See" wrote in message
.. .
On a popup form I have a button to close the form. If the form is open
to ADD a record and the user changes his/her mind I'd like to close the
form without adding a blank record. The following bit of code (I
thought) would handle this but I still get a blank rcord added. The code
by the way comes right from an example page within access help.

If IsNull(Me![CustomerIDCode]) Then
If MsgBox("'CustomerIDCode' must contain a value." _
& Chr(13) & Chr(10) _
& "Press 'OK' to return and enter a value." _
& Chr(13) & Chr(10) _
& "Press 'Cancel' to abort the record.", _
vbOKCancel, "A Required field is Null") = _
vbCancel Then
DoCmd.Close
End If
End If

Any advice sincerely appreciated