check if a cmd button clicked within VBA for another control

[Guest]

I have a form that contains a required Date field. The OnExit event checks
for Null & if true displays a MsgBox saying “Date is required†and uses
Cancel=True to keep focus on the Date control. I also have a “Fail-Safeâ€
command button called “CANCEL†that undoes the record. The problem is once I
enter the Date control, I cannot get out unless I enter a date. Ideally, I
would like the Cancel button to be able to override the Null test in the
OnExit event of the Date control. Is there a way to see if the Cancel button
was “clicked†within the OnExit event of the Date control so that I can
bypass the VBA code that keeps you trapped in the date control?

Tanks Garry Gross

 

[Allen Browne]

Press Esc to get out of the control without entering a date.

You cannot use a command button to get out of the control if the date field
has its Required property set to Yes in the table, because the Click event
of the button cannot be processed until after the focus moves out of the
control, and it can't get out with an incomplete date.

Pressing Esc undoes the entry. You can also use the Undo button on the
toolbar, or the Undo on the Edit menu.

 

[Dirk Goldgar]

I have a form that contains a required Date field. The OnExit event
checks for Null & if true displays a MsgBox saying "Date is required"
and uses Cancel=True to keep focus on the Date control. I also have
a "Fail-Safe" command button called "CANCEL" that undoes the record.
The problem is once I enter the Date control, I cannot get out unless
I enter a date. Ideally, I would like the Cancel button to be able
to override the Null test in the OnExit event of the Date control.
Is there a way to see if the Cancel button was "clicked" within the
OnExit event of the Date control so that I can bypass the VBA code
that keeps you trapped in the date control?

Tanks Garry Gross

No, there's no good way to do that. The best way to handle required
fields, IMO, is to check them in the form's BeforeUpdate event and
cancel the update if they aren't filled in. I prefer to check all the
required fields, generate and display a message listing all the required
fields that are blank, and then set the focus to the first one in the
list.

Doing the validation in the form's BeforeUpdate event allows the user to
move around the form freely and not follow a rigid pattern of data entry
if she doesn't want to. And yet still the required data will have to be
filled in before the record can be saved.

 

[Guest]

Dirk, Thanks for the reply, but to do that I will have to do some major
redesign of the form. The problem is there is a sub form and once focus
moves to the sub form, I get the standard error msg about the date being
required - unless I can trap that and supply my own message.
Redesign is really probably a very good idea. I am fairly new to Access &
just started creating the form & fixing errors as they occurred - so there
are probably "patches" on top of "patches"

 

[Dirk Goldgar]

Dirk, Thanks for the reply, but to do that I will have to do some
major redesign of the form. The problem is there is a sub form and
once focus moves to the sub form, I get the standard error msg about
the date being required - unless I can trap that and supply my own
message.

That's because the main form's record will always be saved before the
subform can get the focus. And that means that the form's BeforeUpdate
event will fire, in which you can check for missing values and display
your own message.

Redesign is really probably a very good idea. I am fairly
new to Access & just started creating the form & fixing errors as
they occurred - so there are probably "patches" on top of "patches"

That's pretty common when you first start working with Access. I can't
tell you to redesign your form, but I can say that the approach you were
hoping to take either isn't going to work, or at the very least is going
to take some elaborate programming.

 

[Guest]

Dirk, Thanks I will try the Before Update event of the main form to trap the
missing Date field. (That had crossed my mind but haven't had a chance to
try it) In any case, my original question has been answered.

Thanks Garry