Event other than BeforeUpdate

[pbbriggs]

Hi,

I have a pop-up form on which I need to perform a simple validation (verify that one item in an option group is checked, if another option button is checked) before the form is closed.

The form may or may not have changes on it before it is closed.

I first tried just putting my validation rule in the OnClick event of a "close" button (and disabled the "X" box close option). However, the validation rule would only trigger if the user clicked in a field (any field) on the form before clicking "close." I'm not sure why. I also tried moving the validation code to the OnClose and OnUnload events of the form. Same result both times. I also tried adding "Me.Requery" before running the validation in case that would help. No luck.

Obviously, the BeforeUpdate event will also not work because there may not be any changes to the form.

Is there an event that will be triggered no matter what, any time I close the form?

Just as a side note, I tried putting a break point in the code to make sure the validation was executing properly. Whenever a break point was present, the error box popped up (i.e., I could not duplicate the non-validating scenario with break points in the code).

Here is the code:

Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click
Me.Requery
If chkAdjust.Value = -1 Then
If optWho.Value = "" Then
MsgBox "You must select the responsible party.", vbExclamation, "Responsible Party"
Else
DoCmd.Close
End If
Else
DoCmd.Close
End if

Exit_cmdClose_Click:
Exit Sub

Err_cmdClose_Click:
MsgBox Err.Description
Resume Exit_cmdClose_Click

End Sub

Thanks in advance,
Patricia

 

[JeffAlton]

I am assuming that chkAdjust is a checkbox linked to a yes/no field in a table or query.
I am also assuming that optWho is an option group linked to a number field in a table or query.
and also that if the check box is checked one of the options in the option group also needs to be selected.
if so try this change.

Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click
If Me!chkAdjust = True And Me!optWho = 0 Then
MsgBox "You must select the responsible party.", vbExclamation, "Responsible Party"
Else
DoCmd.Close
End If


Exit_cmdClose_Click:
Exit Sub

Err_cmdClose_Click:
MsgBox Err.Description
Resume Exit_cmdClose_Click
End Sub

 

[pbbriggs]

Thanks for the reply, JeffAlton.

You were correct in your assumptions and I am sorry I did not explain that very well in the beginning.
I tried your code - which was a little cleaner than my code by combining the two "Ifs" - and could not get the validation to trigger at all. Then I changed the line And Me!optWho = 0 to And Me!optWho = "". Once I did this, the validation will trigger again when it should (when chkAdjust is true and when no option is chosen in optWho). However, I'm still stuck with the same problem... it seems that the code will not accurately run this check unless I've done something else on the form.

What happens is a query runs (based upon another form) which checks the chkAdjust box, then the form opens to allow the user to choose one of the items in the option group. However, if the window pops up and the user clicks "Close," the form will close without prompting him to check one of the options. The only time it will actually trigger is if the user makes some other change on the form before clicking close (e.g., unchecking and re-checking chkAdjust, or altering another text field). When that happens, your code (as well as my code) will accurately call the message box.

It's like somehow the form does not know to check the "If" statement unless some change has been made to the form. I am not sure why. That is why I attempted to utilize other events (Unload of the form, etc.) for the code, but I got the same result every time.

Thanks for trying, though.
Patricia

 

[CajunCenturion]

Have you tried using the Deactivate event for the form?

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[pbbriggs]

Ahh, it gets even stranger. I tried Cajun's suggestion and put the following code into the Deactivate event for the form (alternatively replacing the blue text with Me!):

If Forms!frmGLDetail!chkAdjust = True And Forms!frmGLDetail!optWho = "" Then
MsgBox "You must select the responsible party.", vbExclamation, "Responsible Party"
End If

Then I put doCmd.Close into the Click event for the close button.

Now, the validation won't trigger at all. I read that the Deactivate event occurs every time you close a form, but I tried putting a break point on every line of code in the Deactivate event, and my form closed without the code ever breaking. I don't understand it. I do have all forms in this project as Pop-up, so I will investigate whether that might have something to do with them calling different events when they close, but I wouldn't think that would be the case.

Thanks for trying, Cajun.