Prevent on Exit code when form is closed

[DJJ]

Is there anyway to prevent this code from firing when the form is closed and
this text box has focus?

Private Sub txtBegHour_Exit(Cancel As Integer)
If CInt(Nz(Me.txtBegHour.Text, 0)) = 0 Then
MsgBox "Please enter a number between 0 - 12 ", vbOKOnly, "Attention!"
Cancel = True
End If
End Sub

Thanks in advance...

DJ

 

[Klatuu]

Move it to the correct event. That would be the Before Update event.

 

[Nugimac]

With code on the exit event of a textbox - every time a user moves the
cursor off the textbox, the exit event for the
textbox is run.

You could move the msgbox code to the before update event of the textbox.
You could move the msgbox code to the after update event of the textbox.
Try both of these to see if it gives the result you want.


Do you want the message to appear every time except when the user closes the
form?

How does the user close this form?
- is there a close button and a cancel button?

If you move the msgbox code from the Exit Event of the textbox to the before
update event of the form, it is easier to cancel the msgbox from appearing
if you know that the user wants to cancel all changes before closing the
form.

If this sounds more like what you want - search for posts on validation.
There have been some excellent examples posted on this newsgroup.

Post back after you have tried it and if you still need help.

Jeanette Cunningham

 

[DJJ]

I know about the Before and After Update events which already have other more
complex validation code so, for that and other reasons I still prefer to use
the Exit event.

It works very well until the user unexpectedly closes the form while this
control has focus and then the code fires twice before the form closes.

I was hoping there might be a way to detect if the form is being closed and
then exit the sub before the code has a chance to run. Is that possible?

DJJ

 

[DJJ]

The form closes by pressing the close form 'X' button on the top right hand
side of the window.

Yes, I want the message to appear whenever the validation criteria is not
met except when the form is closed and the control has focus.

 

[Jeanette Cunningham]

Access doesn't work this way.

When the user has the cursor in the textbox, the only way the user can tell
access to close the form is to click another control on the form which tells
Access to close the form.

To click another control on the form, the user has to exit the textbox.

An alternative is to change the message the user sees on exit of the
textbox.
The message could also ask if the user wants to close the form. If you trap
the response from the user and if they choose 'close the form', you could
allow them to close the form without entering any data for txtBegHour.

If you need help with multiple validations on different controls before the
user closes the form, these is a way to put all these validations into a
single event that fires from the before update event of the form.

If this could help you situation, search the newsgroups for validation -
there are some good examples of checking all the controls that need
validating before the user closes the form. This is a bit more work to set
up the first time, but once you have mastered it, you will find it the
easiest way to do multiple validations for controls on any form.

 

[Klatuu]

It doesn't matter how much code you have in the before update event, that is
where the code belongs.

 

[DJJ]

This field is a number field so it automatically displays zero. If the user
presses enter and moves to the next field the validation code in the Before
Update event does not catch the zero and fails to display the message. When
I place the code in the On Exit event it does catch the zero and correctly
displays the message.

This is way I opted for the On Exit event.

DJJ

 

[Jeanette Cunningham]

Hi again DJJ,

This extra information makes it easier for us to give an answer.
Having fields that automatically display zero, causes these types of
problems.
Many of us design out tables without automatic zeros to avoid these types of
problems.

find the table that contains this number field and make a copy (in case any
disaster happens when fixing this zero problem)

open the table in design view and find the number field we are talking about

in the lower pane of the design view find the 6 th line down on the General
Tab
it will say Default Value on the left side
select the 0 next to Default Value and delete it
close the table and save changes
this will fix the issue of having a 0 in the BegHour before the user has
typed anything

You also need to fix any existing data in the table that has 0 for BegHour.
Open the table in normal view
For each 0 in BegHour, delete the 0.
If there are a lot of records, post back and we can give you a query to do
this quickly.

Now you can go back to the form and remove the code on the exit event of
txtBegHour

Post back if there is still an issue with this textbox

Jeanette Cunningham

 

[DJJ]

I did that but now the user can enter nulls even when there is a isNull
function in the Before Update event.

It does not seem to matter whether its is zeros or nulls.

DJJ

 

[Jeanette Cunningham]

Hi again DJJ,

About this textbox - do you want it set up so that the user must always
enter a number in txtBegHour?
If this is the case, put your code in the Before Update event of the form.
Every time the user tries to close the form,
if they haven't filled in a value for txtBegHour,
they will see the message and be forced to enter a value for txtBegHour in
order to close the form.
Is this what you want to happen?

What are you going to do when the user wants to cancel their changes,
meaning they want to close the form without saving their changes?
How will you know if this what the user wants to do?