Closing a single form

[elliottpt via AccessMonster.com]

Hi

Currently I have a single form with a close button with the following code
attached to it( It is the default code generated from wizard for the close
button.)

Private Sub Close_Click()
On Error GoTo Err_Close_Click

DoCmd.Close

Exit_Close_Click:
Exit Sub

Err_Close_Click:
Msgbox Err.Description
Resume Exit_Close_Click

End Sub

I have vbCritical checks on all my fields on the on exit event to ensure the
correct data is entered.eg for the season field:

Private Sub Season_Exit(Cancel As Integer)
'Ensuring that the correct value is entered
If IsNull(Me.SEASON) Or Me.SEASON = "" Or Me.SEASON < 2008 Then
Msgbox ("Please enter the correct season"), vbCritical
Cancel = True
End If
End Sub

A problem arises when the user needs to exit and or abort the record. The
close does not give the user the option of exiting and overiding the
compulsory entry in the field; created by the vbCritical statement.

A MVP suggested the following code but 1stly I don't understand it, and 2ndly
it doesnt close the form.

if me.dirty then
'save record
me.dirty = false
end if

'if record could not be saved
if me.dirty then
if msgbox("OK to Close the form and discard edit on current record" _
& vbCrLf & " Cancel to return to record and make more changes"
vbOkCancel, _
"Abort " & iif(me.NewRecord,"New ","") & "record?") _
= vbCancel then
exit sub
end if
me.undo
end if

Some help would be much appreciated.

Kind regards
Philip

 

[Allen Browne]

There are several issues with what you are trying to do. For example if the
user never enters the Season text box, it's Exit event will not fire, and
your record will save without running your code. You need to move the code
into the BeforeUpdate event of the *form*. Access will call this regardless
of how the save occurs.

Further, if you do cancel the Exit event of the control (as in your code),
the user can't get out of the box to click the Undo button, so you're
snookered.

Here's the suggested code for running the test, and providing buttons to
save'n'exit, or to undo'n'exit:

1. Set the BeforeUpdate property of your form (not control) to:
[Event Procedure]
Click the Build button (...) alongside the property.
Access opens the code window.
Set up the code like this:

Private Sub Form_BeforeUpdate(Cancel as Integer)
If IsNull(Me.Season) Or (Me.Season < Year(Date)) Then
Cancel = True
MsgBox "Enter a valid season, or press <Esc> to undo."
End If
End Sub

2. If you want a button to save the record and close, set up the button's
Click event procedure like this:
Private Sub cmdSaveAndClose_Click()
If Me.Dirty Then RunCommand acCmdSaveRecord
DoCmd.Close acForm, Me.Name
End Sub

3. If you want a button to discard the record and close, set up the button's
Click event procedure like this:
Private Sub cmdCancelAndClose_Click()
If Me.Dirty Then Me.Undo
DoCmd.Close acForm, Me.Name
End Sub

 

[elliottpt via AccessMonster.com]

Hi Allen

Thanks so much.

I have moved my error codes like you suggested to the before update and it
works well thanks.

When I close the form(using the close button) and an error is present, the
error msg appears but the code does not stall or cancel the close, it simply
closes the form after the error msg has been displayed; not giving the user
time to respond.. I had a look at the undo method but it doesnt seem to work.


On Error GoTo Err_Close_Click

DoCmd.Close

Exit_Close_Click:
Exit Sub

Err_Close_Click:
Msgbox Err.Description
Resume Exit_Close_Click* *****I don't want it to resume the close but
rather to set the
focus to the error or
alt just stall the close process sothat
the user can rectify
the error.

Regards
Philip

There are several issues with what you are trying to do. For example if the
user never enters the Season text box, it's Exit event will not fire, and
your record will save without running your code. You need to move the code
into the BeforeUpdate event of the *form*. Access will call this regardless
of how the save occurs.

Further, if you do cancel the Exit event of the control (as in your code),
the user can't get out of the box to click the Undo button, so you're
snookered.

Here's the suggested code for running the test, and providing buttons to
save'n'exit, or to undo'n'exit:

1. Set the BeforeUpdate property of your form (not control) to:
[Event Procedure]
Click the Build button (...) alongside the property.
Access opens the code window.
Set up the code like this:

Private Sub Form_BeforeUpdate(Cancel as Integer)
If IsNull(Me.Season) Or (Me.Season < Year(Date)) Then
Cancel = True
MsgBox "Enter a valid season, or press <Esc> to undo."
End If
End Sub

2. If you want a button to save the record and close, set up the button's
Click event procedure like this:
Private Sub cmdSaveAndClose_Click()
If Me.Dirty Then RunCommand acCmdSaveRecord
DoCmd.Close acForm, Me.Name
End Sub

3. If you want a button to discard the record and close, set up the button's
Click event procedure like this:
Private Sub cmdCancelAndClose_Click()
If Me.Dirty Then Me.Undo
DoCmd.Close acForm, Me.Name
End Sub

Currently I have a single form with a close button with the following code
attached to it( It is the default code generated from wizard for the close

[quoted text clipped - 55 lines]

Kind regards
Philip

 

[Allen Browne]

Undoing the record will be the best solution, assuming this is a bound form.

Or perhaps the error is triggered from the control, in which case you could
trap (but perhaps not solve) it in the form's Error event.

You could test the Err.Number in your error handler, and do something else.
(I'm not sure what you need there.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen

Thanks so much.

I have moved my error codes like you suggested to the before update and it
works well thanks.

When I close the form(using the close button) and an error is present, the
error msg appears but the code does not stall or cancel the close, it
simply
closes the form after the error msg has been displayed; not giving the
user
time to respond.. I had a look at the undo method but it doesnt seem to
work.


On Error GoTo Err_Close_Click

DoCmd.Close

Exit_Close_Click:
Exit Sub

Err_Close_Click:
Msgbox Err.Description
Resume Exit_Close_Click* *****I don't want it to resume the close
but
rather to set the
focus to the error or
alt just stall the close process sothat
the user can rectify
the error.

Regards
Philip

There are several issues with what you are trying to do. For example if
the
user never enters the Season text box, it's Exit event will not fire, and
your record will save without running your code. You need to move the code
into the BeforeUpdate event of the *form*. Access will call this
regardless
of how the save occurs.

Further, if you do cancel the Exit event of the control (as in your code),
the user can't get out of the box to click the Undo button, so you're
snookered.

Here's the suggested code for running the test, and providing buttons to
save'n'exit, or to undo'n'exit:

1. Set the BeforeUpdate property of your form (not control) to:
[Event Procedure]
Click the Build button (...) alongside the property.
Access opens the code window.
Set up the code like this:

Private Sub Form_BeforeUpdate(Cancel as Integer)
If IsNull(Me.Season) Or (Me.Season < Year(Date)) Then
Cancel = True
MsgBox "Enter a valid season, or press <Esc> to undo."
End If
End Sub

2. If you want a button to save the record and close, set up the button's
Click event procedure like this:
Private Sub cmdSaveAndClose_Click()
If Me.Dirty Then RunCommand acCmdSaveRecord
DoCmd.Close acForm, Me.Name
End Sub

3. If you want a button to discard the record and close, set up the
button's
Click event procedure like this:
Private Sub cmdCancelAndClose_Click()
If Me.Dirty Then Me.Undo
DoCmd.Close acForm, Me.Name
End Sub

Currently I have a single form with a close button with the following
code
attached to it( It is the default code generated from wizard for the
close

[quoted text clipped - 55 lines]

Kind regards
Philip