Message Box based on field entry

[FormerJarHead]

Hi all -



I have a form field I'm trying to add a message box to. When the user enters an amount greater than 1, I would like the message to notify the user. If no entry is made, no message is displayed. What I thought was relatively simple has sucked up about 3 hours so far. If I had hair, I'd pull it out! Any help will be greatly appreciated.

Thank you

[JoeM]

I think you want to use the "AfterUpdate" event of the field to return your Message Box. Just incorporate your logic/message box into that, and it will automatically run upon an entry being made to that field.

[ranman256]

SEE ALSO: the text box property of VALIDATION RULE, and VALIDATION TEXT

[June7]

I agree with ranman. This looks ideal for textbox ValidationRule property. Expression could be:

<=1 Or Is Null

ValidationText property could have:

"Enter value less than or equal to 1 or ESC to cancel entry."

[JoeM]

Yep, forgot about Validation on Forms.
Certainly easier than using VBA!

[FormerJarHead]

Thanks to all. Simple solution to my complex problem. What happens though with validation is it will not allow the user to chose either okay or cancel as a message box would. The user then has to remove their entry completely before proceeding through the remainder of the form. I basically want them to be aware of the entry and the field they are using, not completely prevent them from entering a value

[June7]

If you want to permit the entry even if it violates some threshold then ValidationRule is not appropriate. Use the VBA.

[FormerJarHead]

I know how to create the message box but not quite clear how I would write the code to do what I'm asking. Can you offer any assistance?

[June7]

Code in the control's BeforeUpdate or AfterUpdate.

Do you just want to notify the user? Use AfterUpdate:

MsgBox "You entered value " & Me.controlname & " which exceeds the threshold of 1."

Or do you want to give them choices? Use BeforeUpdate:

If MsgBox("You entered value " & Me.controlname & " which exceeds the threshold of 1. Do you want to keep this value?", vbYesNo) = vbNo Then
Cancel = True
End If

[FormerJarHead]

Thanks June! That did it!