Field Validation When Closing a Form

[jrtaylor]

I have a form with an option box that says “Approve with Comments”, “Disapprove With Comments”, and “Approve Without Comments”. I want a message to pop-up when the reviewer clicks on the “Submit Comments” button or the “Exit” button of the form to remind the reviewer that comments are required if either the “Approve with Comments” or “Disapprove With Comments” is selected. Also until one of the comment options is unselected the reviewer may not close the form. Basically it would validate whether or not a comment was entered based on the checked option box.

Thanks,
jrtaylor

 

[WildHare]

Asumptions:

option box values are 1, 2 and 3. 1 and 2 are the ones "With Comments". Option group NAME is "Frame1". The comments field is named COMMENTS.

On your forms CLOSE event:

if Me!Frame1 = 1 or Me!Frame1 = 2 
   and ISNull(Me!Comments) then
   Msgbox "You MUST enter comments. yada yada yada", VbExclamationOnly, "Comments Required"
   DoCmd.CancelEvent
   Me!Comments.SetFocus
else
   if Me!Frame1 = 3 and Not ISNull(ME!Comments) then
   Msgbox "Comments are NOT required, but thanks for putting them in...", vbOkOnly, "Thanks!"
   End if
 End IF

or words to that effect. The important guy there is the CancelEvent, to cancel the close event and make them go back to enter comments. Is this what you were looking for?

If you have to deal with crafty users, you might need code like this as well:

if ISNull(Me!Comments) OR
Len(Me!Comments) < 4 then....

This keeps them from going to the comments box and just hitting spacebar a coupla' times to get around the ISNULL condition...




Ex-JimAtTheFAA
78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

More Neat Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[jrtaylor]

WildHare,

That worked great except if there are comments to be entered it still pops up with enter comments if there is a value in &quot;txtComment&quot;. I tried the following but it did not work. Please help!

Private Sub Form_Close()
If Me!Frame87 = 1 Or Me!Frame87 = 2 And IsNull(Me!txtComment) Then
MsgBox &quot;You MUST enter comments.&quot;, VbExclamationOnly, &quot;Comments Required&quot;
DoCmd.CancelEvent
Me!txtComment.SetFocus
Else
If Me!Frame87 = 3 And Not IsNull(Me!txtComment) Then
MsgBox &quot;Comments are NOT required, but thanks for putting them in...&quot;, vbOKOnly, &quot;Thanks!&quot;
If Me!Frame87 = 1 Or Me!Frame87 = 2 And (Me!txtComment) = Value Then
DoCmd.Close

End If
End If
End If
End Sub

Thanks!
Jackie

 

[WildHare]

What is this line doing here?

If Me!Frame87 = 1 Or Me!Frame87 = 2 And (Me!txtComment) = Value Then
DoCmd.Close



Ex-JimAtTheFAA
78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

More Neat Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[BoxHead]

jrtaylor,


I'm curious about that last line as well.

As far as the extra prompts for comments, I believe the following would call the message box whenever Me!Frame87 = 1.

If Me!Frame87 = 1 Or Me!Frame87 = 2 And IsNull(Me!txtComment)

Try

If Me!Frame87 = 1 And IsNull(Me!txtComment) 
Or 
Me!Frame87 = 2 And IsNull(Me!txtComment)

Might be simpler to use (assuming there are only the three options described)

If Me!Frame87 <> 3 And IsNull(Me!txtComment)

HTH John

Use what you have,
Learn what you can,
Create what you need.

 

[WildHare]

John - I agree about
If Me!Frame87 <> 3 And IsNull(Me!txtComment)

being simpler - but what if the option group could be NULL - that was why I thought it might be best to check explicitly for 1 or 2...

We'll have to wait for Jackie (jrtaylor) to get back to us....

Jim Ex-JimAtTheFAA
78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

More Neat Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[jrtaylor]

Thanks guys!

That fixed the problem. As for the last line I had in there before, it was my own shot at fixing the problem. As you might have noticed I am new at this code business but the more I learn from Tek-Tips the more I am finding out how much more flexibility it gives you to accomplish all of the requests I get from users.

Whenever I review my databases with the users they always have qusetions like &quot;I would like for it to do...&quot; or &quot;Can you fix it so it will...&quot; and I think to myself &quot;Ok Tek-Tips I really need your help now!&quot; I have told others about this forum because it has been a life saver for me. I never like to say &quot;I can't&quot; so I dig into the books or come here for help. Whenever I incorporate the tips I learn it is easy to refer back to them when the situation presents itself again.

Again Thanks a Bunch!!!

Jackie