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 said:
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 said:
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.
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
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.
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
Thanks Gina that seems to work .... Would you be able to explain
briefly what that line of code is about?
Greatly appreciate the help.
Try adding:
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
one space downs after the last End If
HTH,
Gina
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
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
