A Form's UnLoad Event - Can you cancel?

[ChemistZ]

Could someone please look at the code below and tell me why if the County No is blank it gives me the proper message box but then the form closes anyway? I thought Cancel=True was supposed to stop that.

Dim DataErr As Integer
On Error GoTo err_try

If Me.Dirty And IsNull(Me!COUNTY_NO) Then
Cancel = True
MsgBox "You can not leave COUNTY NO blank. It is a required field. Please enter the correct COUNTY NO.", , "Service of Process Error"
Me!COUNTY_NO.SetFocus
ElseIf Me.Dirty And IsNull(Me!COURT_TYPE_CODE) Then
MsgBox "You can not leave COURT TYPE CODE blank. It is a required field. Please enter the correct COURT TYPE CODE.", , "Service of Process Error"
Me!COURT_TYPE_CODE.SetFocus
Cancel = True
ElseIf Me.Dirty And IsNull(Me!COURT_NMBR) Then
MsgBox "You can not leave COURT NUMBER blank. It is a required field. Please enter the correct COURT NUMBER.", , "Service of Process Error"
Me!COURT_NMBR.SetFocus
Cancel = True
ElseIf Me.Dirty And IsNull(Me!CASHIER_NO) Then
MsgBox "You can not leave CASHIER NUMBER blank. It is a required field. Please enter the correct CASHIER NUMBER.", , "Service of Process Error"
Me!CASHIER_NO.SetFocus
Cancel = True
ElseIf Me.Dirty And IsNull(Me!DOCKET_NO) Then
MsgBox "You can not leave DOCKET NUMBER blank. It is a required field. Please enter the correct DOCKET NUMBER.", , "Service of Process Error"
Me!DOCKET_NO.SetFocus
Cancel = True
ElseIf Me.Dirty And IsNull(Me!EXCEPTION_CODE) Then
MsgBox "You can not leave EXCEPTION CODE blank. It is a required field. Please enter the correct EXCEPTION CODE.", , "Service of Process Error"
Me!EXCEPTION_CODE.SetFocus
Cancel = True
ElseIf Me.Dirty And IsNull(Me!TRANS_DATE) Then
MsgBox "You can not leave TRANSACTION DATE blank. It is a required field. Please enter the correct TRANSACTION DATE.", , "Service of Process Error"
Me!TRANS_DATE.SetFocus
Cancel = True
ElseIf Me.Dirty And IsNull(Me!BATCH_DATE) Then
MsgBox "You can not leave BATCH DATE blank. It is a required field. Please enter the correct BATCH DATE.", , "Service of Process Error"
Me!BATCH_DATE.SetFocus
Cancel = True
ElseIf Me.Dirty And IsNull(Me!MASTER_CODE) Then
MsgBox "You can not leave MASTER CODE blank. It is a required field. Please enter the correct MASTER CODE.", , "Service of Process Error"
Me!MASTER_CODE.SetFocus
Cancel = True
Else

Exit Sub
End If
exit_unload:
Exit Sub
err_try:
If DataErr = 2279 Then
MsgBox "The EXCEPTION CODE field and the TYPE CLAIM field both require the entry of two letters. Please enter two letters only.", , "Service of Process Error"
Cancel = True
ElseIf Err.Number = 3022 Then
MsgBox "The EXCEPTION CODE, CASHIER NUMBER and DOCKET NUMBER together make up the key to this form. You have entered a combination of those three that already exists in the EXCEPTIONS table. Please try again.", , "Service of Process Error"
Me!EXCEPTION_CODE.SetFocus
Cancel = True
ElseIf Err.Number = 3201 Then
MsgBox "The combination of COUNTY NO, COURT TYPE CODE and COURT NUMBER make up a unique field that identifies a specific court. The COUNTY NO you entered into this combination does not exist. Please re-enter the COUNTY NO.", , "Service of Process Error"
Me!COUNTY_NO.SetFocus
Cancel = True
Else
Resume exit_unload
End If
End Sub

 

[zerosandones]

Your If..then logic currently includes a test to check whether or not the current record is "Dirty". That's the mistake!

The Dirty property only applies to previously saved records. If provides a means by which you can compare the state of the current record (which you may be in the process of changing i.e. dirtying) to when it was last saved.

Here's another tip to handle unloading a form which has no pending record changes i.e. the user opens the form, does nothing, then closes the form. Although, the Dirty property was probably your strategy for detecting a "change of state for the record", here's a solution which will do the job.

Using the Form's "NewRecord" property, I have modified your first If..then statement as follows:

If Not Me.NewRecord And IsNull(Me!COUNTY_NO) Then
Cancel = True

Hope this helps you out.

 

[ChemistZ]

Okay, I am thoroughly confused. I tried altering teh code so that it reads If no me!.newrecord and isnull(county_no) then
cancel=true but here is the problem

When a user opens the form and does nothing and hits the close button, it closes which is great but it was doing that before with the Me!.Dirty code. The main problem is that when they start entering data and the hit the close button it gives the error message about not leaving things blank but then closes anyway instead of canceling. How do I stop that?

 

[zerosandones]

Okay, this one needs a little debugging.

First, I took the intrinsic part of your code as a proof of principle and ran a test form of mine. Testing involved a two field table, one of which was the County_No field. Once the form opened up, I entered data into the other field, then, before entering any data in the County_No field, I tried to close the form. I received the message regarding the missing data and the form's cancel event fired, leaving the form open ... just what you are looking for. Here's the code I ran ... it worked perfectly.

Private Sub Form_Unload(Cancel As Integer)

On Error GoTo err_try

If Not Me.NewRecord And IsNull(Me!County_No) Then
Cancel = True
MsgBox "You can not leave COUNTY NO blank. It is a required field. Please enter the correct County No.", , "Service of Process Error"
Me!COUNTY_NO.SetFocus
Else
Exit Sub
End If

exit_unload:
Exit Sub

err_try:
MsgBox "Error detected.", , "Simplified Error Handler"
Resume exit_unload

End Sub


At this point, I would suggest setting a breakpoint on the first If..Then statement in the Form's Unload event to trace your program execution thread.

My guess is that another event's code continues to execute after the Unload event finishes. Try searching through your code for any "Close Form" type of statement ...chances are you'll find that your Form's Unload event is now working okay, however, additional code continues to execute which causes the Form to ultimately close.

Either way, my test proves that the problem is no longer associated with the new Unload code ... remember, you can't use the Dirty property, you need to use the NewRecord property or a similar mechanism.

Let me know what happens.