if then msgbox on form

[RICKA]

Hello,
I have a timesheet form and i have an unbound calculation box on the form footer to give me the total number of hours booked during the day, however i want to put a validation on the box so if the value is greater than 24 then have a message box pop up and reset the value of the object that was changed to create the problem back to "0".

please can someone help?

 

[agehoops]

Hiya. You can do this a couple of different ways. The first, being the most simple, is the set a validation rule. If you go into the properties of the field you want this to happen on, you should find an option to set a validation rule. In here, just type in something like : <=24

This will force the user to enter a value that is less than or equal to 24. You should also be able to specify what message is displayed.


The other method which is SLIGHTLY more complex is through VB but is more customizable.

In the properties of the field, go into the AfterUpdate event of the box, and enter the following code:

If ([field] > 24) Then
 MsgBox "Message to be displayed",vbOkOnly,"Message Title"
 [field] = ""
 [field].SetFocus
End If

Obviously you need to change the [field] to the name of the field you're checking, and change the details in the message box to what you want it to say, but this will check if the value is greater then 24, display the message you want if it is, then clear the value in it and set the focus back to it after.

Hope this helps.
Aidy

 

[RICKA]

Will this work even if the box is a calculated control and not a user input?

 

[agehoops]

Yea should do but may not work under the same event. What you could do, is do the calculation for that field in VB first, check the value it comes out with with that IF statement, then if it fails display the error message, but if it is ok THEN put the value into the text box