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?

 

[DJJ]

Jeanette,

The App is a continuous form with text boxes that provide numeric data for
calculation and totalizing. It is used to calculate blocks of time that are
applied to work orders in highly skilled manufacturing. The data is
voluntarily deleted completely by the user once the total time to complete a
work order has been calculated and stored in another database. Data entry on
this form needs to be fast and smooth otherwise workers won't use it.

Navigation is done by pressing the Enter key that follows the tab order from
one text box to another and then onto a new record. I have found that the
Exit event is the only event (so far) that successfully prevents a user from
passing by a text box and not entering data. The Before Update event does
not prevent a user from forgetting to change the default value of zero or
null when it is required, thus screwing up the calculation. However, the
Exit event runs whenever a control has focus even when the form is being
spontaneously closed, which is a little confusing for a non-technical user.
When the user closes the form I am assuming they are finished so I don’t want
to prompt them at that point to enter any missing data.

I have tried making the fields required fields but that error message tends
to run after the user has tabbed through all the text boxes which is too late
to be of much use. Validation code in the properties does not prevent any of
the above either. The only solution that seems to more or less work is to
shift focus to the previous control when the Exit event fires but it is still
cumbersome and freakish.

David JJ


Private Sub txtBegHour_BeforeUpdate(Cancel As Integer)

If IsNull(Me.txtBegHour) = True Then
MsgBox "Please enter a Time between 1 - 12", vbOKOnly, "Attention!"
Cancel = True
Me.txtBegHour.Undo
ElseIf Me.txtBegHour <= 0 Or Me.txtBegHour > 12 Then
MsgBox "Please enter a Time between 1 - 12", vbOKOnly, "Attention!"
Cancel = True
Me.txtBegHour.Undo
End If
End Sub

Private Sub txtBegHour_Exit(Cancel As Integer)

If Me.txtBegHour = 0 Then 'when the default value is set to zero
MsgBox "Please enter a Time between 1 - 12", vbOKOnly, "Attention!"
Cancel = True
Me.txtBegHour.Undo
Me.txtLineNum.SetFocus 'the previous control in the tab order

End If
End Sub

 

[Jeanette Cunningham]

David,

One thought is to check for the missing value for BegHour at the point where
the calculation runs.
I don't know how you calculate the totals, there may be a possibility to set
up the code to calculate the total before the user moves to the next line in
the subform. If this were possible, you could check for the missing value
and tell the user if there is a problem. Once again people usually use the
before update event for the form to do this sort of thing.

One advantage of using the before update event for the form is that you
could put all the missing value messages in just the one message, thus
smoothing the data entry for the user as they will not have annoying
messages popping up several times on the one row of the subform.

You have the option at the point where you tell the user about the problem
to cancel them moving to the next row on the subform, or just making it a
warning and allowing them to leave the control blank.

Another possibility is to provide a cancel button on the subform. If you
have your validation code on the before update event for the form instead of
the exit event for the textbox, then if they wish to cancel, you can code to
allow it, but if they don't want to cancel you can then force them to enter
the value before they move to the next row or close the form.

Otherwise the only option I can see is to leave your setup the way you
already have it.

Jeanette Cunningham