Want to cancel prompt, select No and still closes form

Hi All,

I have a pop up form that requires me to fill out all 4 fields of the form. When I don't, I get a prompt telling me which field(s) I've forgotton to fill in and allows me the option to Cancel (Yes or No) and if i decide yes it wont save and will close the form. BUT when I say No (Don't cancel), it still closes out the form and I obviously don't want that. I don't know if it might have to do with my macro button either? Any clue?

Thanks in Advance!

MY CODE:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim blnError As Boolean
Dim strError As String

strError = "You are missing data for "

If IsNull(Me.[Account Number]) Or Me.[Account Number] = "" Then
blnError = True
strError = strError & "Account Number,"
End If

If IsNull(Me.Contact) Or Me.Contact = "" Then
blnError = True
strError = strError & " Contact,"
End If

If IsNull(Me.Department) Or Me.Department = "" Then
blnError = True
strError = strError & " Department,"
End If

If IsNull(Me.Address) Or Me.Address = "" Then
blnError = True
strError = strError & " Address,"
End If

If blnError Then
strError = Left(strError, Len(strError) - 1)
If MsgBox(strError & vbCrLf & "Are you sure you want to cancel." & vbCrLf & "If you do, the info will not be added.", vbQuestion + vbYesNo, "Close Confirmation") = vbNo Then
Cancel = True
End If
End If
End Sub

You need code in the form's Unload Event to prevent the form from closing if there is an error the user wants to correct. Search here for "a better mousetrap" for an example of how to do this.

I forgot I had to change the code to "before update" event because the unload wasn't working. Does that change your answer?

dejavu: I have seen the exact same question somewhere on this forum. Here's the same answer: The BeforeUpdate event isn't the event you have to worry about. when Cancel Set to true, it doesn't save the updated record.
Again, program the unload event.

I'm new to programming, I need some more guidance if your willing.

Like I have no idea what I would put in the unload event so that the form wouldn't cancel when I said no

Use a CanClose boolean and check it's value in the unload event of the form where you can use the Cancel=True

You do need to leave your edit code in the BeforeUpdate event because you want to give the user an option to cancel the update if there is an error. You need to add code to this event to save the result of the update. If the user wants to remain in the form to correct the errors, you need to cause the unload event to be cancelled. You can't cancel the unload event from here because you don't know why the BeforeUpdate is running. It runs whenenever Access determines that it is necessary to save the current record.

In addition to that, you need code in the form's unload event to check the results of the BeforeUpdate Event.

There are a number of pieces of very simple code that need to go into several events to get this to work properly. The example I referenced should help you sort it out.