Cancel still adds record

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

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

Well now you've got me thinking!
This code is fired from OnClick under a command button and CustomerID is not
an auto-number.
If I want the user (ME) to click a button to close the form how would I get
the code under the BeforeUpdate event?

Thanks for the response Ed.

"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

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

That would be before the DoCmd.Close

"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

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

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

Here is some code that I might use to do this

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
Me.Undo 'Undoes all changes to the record
DoCmd.Close acForm, Me.Name 'Close the form running this code
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

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 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