Test conditions on Close

[Guest]

I would like to test for a value in one text box if another text box is
empty. This is the code I have tried to place in the On Close event for the
form:

If IsNull(Me.cboResponsePerson) And Not IsNull(Me.txtResponse) Then
strmessage = "The text in the Response section will not be saved." & _
vbCrLf & "Press OK to exit without saving." & vbCrLf & _
"Press Cancel to return to form."
If MsgBox(strmessage, vbOKCancel, "Information Not Saved") = vbOK Then
GoTo Exit_Form_Close
Else
Me.cboResponsePerson.SetFocus
End If
End If

Exit_Form_Close:
Me.txtResponse = Null
DoCmd.Close

The idea, in case it is not clear (it certainly is not clear to Access) is
that if the combo box cboResponsePerson is empty then the text in text box
txtResponse will not be saved. Pressing OK should enable the user to
continue with exiting the form. Pressing OK should set the focus to the
combo box in case the user wants to take another crack at that. If the user
goes ahead and closes the form then txtResponse will be cleared.
"Response" is the record source for txtResponse, and ResponsePerson is the
record source for cboResponsePerson.
Maybe it's just getting late in the day, but something is certainly wrong.
I can generate the message box, but if I click OK I receive an error message
that the Close action was cancelled (I have error trapping code which I
didn't include above). However, I cannot make the message box go away.
Clicking OK does nothing. I have to Ctrl + Alt + Del. Pressing Cancel (to
the message box generated by the code above) produces the nugget of
information that I cannot assign a value to this object, but try as I may I
cannot learn to what object I cannot assign a value. Again, Ctrl + Alt + Del
is the only way out.

 

[Ken Snell [MVP]]

The Close action is too late to try to change data values ... the record is
already saved and likely the record source has been disconnected at that
point as well.

You'll need to identify an earlier event to do this... perhaps the form's
BeforeUpdate event?

 

[Guest]

Ken,
Thanks for the reply. I didn't realize (although I should have) that the On
Close event was too late for what I was trying to do. I added a Close
command button and attached the code (with some modification) to that. It
works as intended. Thanks again for the insight.

 

[Ken Snell [MVP]]

Glad it's working for you! You're welcome.