Cancel closing form without error message

[chuckcoleman]

Hi, I have in the Unload property of a form the code shown. The code checks if there is an order without a Requested Due Date. If the Requested Due Date is blank it presents a vbMsgBox with OK or Cancel and the message shown. It all works perfectly, almost. If the person clicks on OK, it runs a query to remove that record and lets the form close. If the user clicks on Cancel it cancel's closing the and allows the user to enter the Requested Due Date. However, it also display an error, "No Current Record". And, I don't believe it's coming from the error handler because I prefaced the Msg with the number 19 just so I could be sure.

The question is how do I eliminate the popup error, No Current Record? Basically, I've just canceled closing the form and I don't understand why it thinks there isn't a current record. It's there, I can see it.

      Dim Response As VbMsgBoxResult
      If DCount("*", "Count Blank Work Orders for a Client-Count with Reason") > 0 Then
       Response = MsgBox("You have not specified a Requested Due Date. If you click on Cancel, you will then be able " _
       & "to cancel closing this form and add a Requested Due Date. If you click on OK, the form will close and " _
       & "this Work Order will be deleted.", vbQuestion + vbDefaultButton2 + vbOKCancel)
      If Response = vbCancel Then
       Cancel = True
      Exit Sub
      Else
      If Response = vbOK Then
       DoCmd.SetWarnings False
       DoCmd.OpenQuery "Delete Blank Work Orders for a Client"
       DoCmd.Close acQuery, "Delete Blank Work Orders for a Client"
       DoCmd.SetWarnings True
      End If
      End If
      End If
    
Form_Unload_Exit:
    Exit Sub
Form_Unload_Error:
  If Err.Number = 3021 Then
   MsgBox "19-No current Record"
   Else
    MsgBox Err.Description
    Resume Form_Unload_Exit
    End If

 

[The_Doc_Man]

Here is a link to the Unload event explanation.

Form.Unload event (Access)

Office VBA reference topic

learn.microsoft.com

In the paragraph just above "Syntax" there is a comment to the effect that when you cancel an Unload, it doesn't just act as though nothing happened. It reloads and reinitializes everything - I suspect as though a Form_Current has been executed (though I don't think it is an actual _Current event). It is the re-init part that probably is having issues with the "No Current Record." This recordset condition would probably spring from whatever happens immediately before this form-close occurs that triggers the Unload event.

 

[Gasman]

Should not be saving a record with invalid data.

 

[chuckcoleman]

Here is a link to the Unload event explanation.

Form.Unload event (Access)

Office VBA reference topic

learn.microsoft.com

In the paragraph just above "Syntax" there is a comment to the effect that when you cancel an Unload, it doesn't just act as though nothing happened. It reloads and reinitializes everything - I suspect as though a Form_Current has been executed (though I don't think it is an actual _Current event). It is the re-init part that probably is having issues with the "No Current Record." This recordset condition would probably spring from whatever happens immediately before this form-close occurs that triggers the Unload event.

Thanks Doc. I don't think it is being caused by the Current event. At the top of the Current code, I placed "Exit Sub", so I don't think anything ran there. I still received the same message, No Current Record.

 

[chuckcoleman]

Update. As I previously stated, the code was in the Unload event for the form. As you know, the Unload gets triggered when the form is closing/about to close. What triggers the closing is a command button on the form with DoCmd.Close. I removed that and with a minor modification moved the code I posted with this request to the command button, removed it from the Unload event, and it now works.

So, unless this is a major blunder, I'll keep it this way.

Thank you as always. Chuck

 

[Gasman]

DCount("*", "Count Blank Work Orders for a Client-Count with Reason")

That actually works?

 

[chuckcoleman]

That actually works?

Yes. Why do you think it wouldn't?

 

[Gasman]

Because any domain/field with spaces, needs to be surrounded with [ ], or used to? So that really surprises me. What version of Access are you running?

 

[The_Doc_Man]

So, unless this is a major blunder, I'll keep it this way.

OK, let's be clear... software engineering is the art of making code that does what it needs to do. So "major blunder"? Nope. If it does exactly what you wanted, I'll step away from the question.

However, the NORMAL place to do a record validation is in the Form_BeforeUpdate event, not during a form's close sequence of events. Oh, true, you will save a dirty record if you execute a CLOSE action. But unless the Close is explicitly intended to save something, you normally don't allow a SAVE to occur if you have missing or incorrect data. The code you showed us seems to relate to the "Missing Data" problem. Note that you CAN cancel the BeforeUpdate event, which leaves you with everything you had before plus the chance to "make it right" and save again. See also Gasman's comment earlier to this same point. But I'll specifically direct you to the BeforeUpdate event for this part of the discussion.

 

[chuckcoleman]

Because any domain/field with spaces, needs to be surrounded with [ ], or used to? So that really surprises me. What version of Access are you running?

Microsoft Access for Microsoft 365 MSO (Version 2407 Build 16.0.17830.20056) 64-bit

 

[chuckcoleman]

OK, let's be clear... software engineering is the art of making code that does what it needs to do. So "major blunder"? Nope. If it does exactly what you wanted, I'll step away from the question.

However, the NORMAL place to do a record validation is in the Form_BeforeUpdate event, not during a form's close sequence of events. Oh, true, you will save a dirty record if you execute a CLOSE action. But unless the Close is explicitly intended to save something, you normally don't allow a SAVE to occur if you have missing or incorrect data. The code you showed us seems to relate to the "Missing Data" problem. Note that you CAN cancel the BeforeUpdate event, which leaves you with everything you had before plus the chance to "make it right" and save again. See also Gasman's comment earlier to this same point. But I'll specifically direct you to the BeforeUpdate event for this part of the discussion.

Thanks Doc. I'll internalize your feedback which is always valued. In my customers case, there are times when the customer places an order for what they want, (what they want is entered), but they request giving them an update later with the due date because they may need to check with someone in their organization. I have as a part of the reporting process orders that don't have due dates so someone can follow up with the customer. I will think through your feedback. - Chuck

 

[Pat Hartman]

Please follow Doc's advice. Saving an invalid record is always wrong. Validation code belongs in the form's BeforeUpdate event so you can cancel the save if you discover an error. By the time you get to the close event, the horses have left the barn as the saying goes and the bad data has already been committed.