Sub routine jumps to wrong place

[DubboPete]

Hi all,

I have this bit of code to check for empty fields in a record before closing
the form, and prompts users to complete the record to avoid it being
deleted.
It should (during testing) check the [focusobjective] field, and kick in
from there, because I am intentionally leaving it blank (and all other field
apart from [focus].) For some reason when I click close it jumps to field
[strategies]. I then have to click close again before coming up with the
error message box?

I know it's gonna be simple.... but can someone run a different set of eyes
over it to check out why?

tia
DubboPete

<code>
Private Sub Form_Close()

' SERIES OF CHECKS
' To test the data before exiting
' the recordset, to ensure data
' integrity

If Not IsNull(Me![Focus]) Then
If IsNull(Me![FocusObjective]) Or IsNull(Me![Strategies]) Or
IsNull(Me![Indicators]) Or IsNull(Me![When]) Then
If MsgBox("Some fields have not been completed." & vbCrLf &
"Quitting now would delete this focus completely." & vbCrLf & "Are you sure
you want to close?", vbYesNo) = vbNo Then
If IsNull(Me![FocusObjective]) Then
MsgBox "Please provide a valid focus objective before
proceeding", vbInformation
Me![FocusObjective].SetFocus
Exit Sub
End If
If IsNull(Me![Strategies]) Then
MsgBox "Please provide a valid strategy before proceeding",
vbInformation
Me![Strategies].SetFocus
Exit Sub
End If
If IsNull(Me![Indicators]) Then
MsgBox "Please provide a valid Focus Objective before
proceeding", vbInformation
Me![FocusObjective].SetFocus
Exit Sub
End If
If IsNull(Me![When]) Then
MsgBox "Please provide a valid expected completion date
before proceeding", vbInformation
Me![When].SetFocus
Exit Sub
End If
Else
DoCmd.SetWarnings False
DoCmd.OpenQuery "QryDelBadFocus"
DoCmd.SetWarnings True
End If
End If
End If

End Sub
</code>

 

[Andreas]

I am surprised the form does not close regardless.
I think the Unload event might have a Cancel argument and would
therefore be better suited.

I also don't like the names used. Apart from the name [Focus], which I
would neither use as a field nor as a control name, I guess you should
at least use the proper naming conventions for controls (txtFocus),
which would go some way to overcome problems caused by name conflicts.

Apart from that, I can't see anything immediately wrong with the code
itself that would cause this problem. The only thing that that could
come into play is your data types and default values (which are not
obvious to me). Step through the code and make sure [focusobjective] is
in fact Null and not a zero length string.

Regards,
Andreas

 

[DubboPete]

Hi Andreas

I am surprised the form does not close regardless.

Sorry, I thought this was a help group, not a 'patronise other less educated
users' group...

I think the Unload event might have a Cancel argument and would therefore
be better suited.

I know that the Unload event doesn't yet have a Cancel argument, please
elaborate

I also don't like the names used. Apart from the name [Focus], which I
would neither use as a field nor as a control name, I guess you should at
least use the proper naming conventions for controls (txtFocus), which
would go some way to overcome problems caused by name conflicts.

Point taken, but I have to work with databases created in part by other
people, and my job is to clean them up.
What, for example, would you think of a field called:
tblactivitynameunitdatestartedandwhy
which is a valid field name in a table in another database which I now have
to clean up....

Regards,
Andreas

still no wiser...

Regards
DubboPete

 

[Andreas]

See inserts.

Sorry, I thought this was a help group, not a 'patronise other less educated
users' group...

Nothing to do with patronising - I am just surprised.
I would have expected the form to close as there is nothing stopping it.
And since it is late and I should really go to bed, I sometimes just
write it as I see it rather than thinking how you might interpret it.

I know that the Unload event doesn't yet have a Cancel argument, please
elaborate

I don't have Access in front of me.
To the best of my knowledge, if you use the Unload event, you get
something like: Sub Form_Unload (Cancel as Integer)
Might be one of the other events. Check the help on which events fire
when you close a form. Anyway, you can set Cancel to true, which stops
the form from closing. Try moving your code in there and see what happens.

I also don't like the names used. Apart from the name [Focus], which I
would neither use as a field nor as a control name, I guess you should at
least use the proper naming conventions for controls (txtFocus), which
would go some way to overcome problems caused by name conflicts.

Point taken, but I have to work with databases created in part by other
people, and my job is to clean them up.
What, for example, would you think of a field called:
tblactivitynameunitdatestartedandwhy
which is a valid field name in a table in another database which I now have
to clean up....

<a sad smile on my face> yes, indeed, all fun and games.

Regards,
Andreas