SOLVED: Need an error message

Hi everyone,

This has got me stumped but that's easy to do! I have written an estimating program for a particular printing printing press. I have limited the number of press sheets to estimate at 500! Anything over 500 press sheets will not compute a final price and that's fine. What I am trying to do is to have an error message saying, let's say for now " Do Not Enter Over 500 Sheets" appear if the total of press sheet exceeds the 500 cut off quantity. The number of press sheets shows on the form in a text box that is formatted as a general number. I have tried variations of if and end if codes and I can get it to show the string, but it pops up on any amount, not just the amounts over 500. Well that's the jest of it. Any help would certainly be appreciated.

Thanks

Gary

To disallow any data entry problems, you can set the vaildation rule for a field (using the table design view). Set it to: <=500

In the textbox on the form where you do not want a user to enter a value >500, then in the BeforeUpdate event enter something like this
[vba]Private Sub txtValue_BeforeUpdate(Cancel As Integer)

If Me.txtValue > 500 Then
MsgBox "Do Not Enter Over 500 Sheets", vbCritical, "Please re-enter."
Me.txtValue.SetFocus
End If

End Sub[/vba]The value that was entered in the textbox will remain there but not update until a value <= 500 is entered. Pressing ESC will return it to the original value before the invalid value was entered.

I think it is better to enter data using a form and use the BefureUpdate event code as you have more control over the error message diplayed.

I hope I understood your question. If not, post back. I (or someone else) will try to help.

Thanks Cosmos,

You helped a lot. I had to modify what you gave me but it did end up doing what I needed. Rather than using before hand, I used Enter and made the following changes and it works great.

[vba]Private Sub C1_Enter()

If Me.C1 > 500 Then
MsgBox "Do Not Enter Over 500 Sheets", vbCritical, "Please re-enter."
Me.C1.SetFocus
End If

End Sub[/vba]

I also did set the validation as you suggest to <=500.

Thanks again.

Gary

GaryB,

Am happy that solved your problem. Always glad to help.
:)

It's not often I find an question that I can actually answer (usually I am doing the asking).
:D

Quote:

---

Originally Posted by GaryB

Rather than using before hand, I used Enter and made the following changes and it works great.

---

Just FYI, using the code in the Enter means the code only runs when the control first gets focus. This means, the user can enter 500, tab to the next control and you wouldn't get the error message. Only if the control got the focus again would you get an error message.

not so in this particular instance. The "500" is an automated field. What I mean is when they put in the quantity of finished pieces it automatically calculates the # of press sheets. Then when the tab or enter it goes to that text box and the message comes up telling them too many press sheets please re-enter a small quantity. It works like a charm. The C1 field is non-editable and if they are over 500 p-sheets it won't calculate a final price. Really, this worked out exactly as I hoped it would. Thank you again - very very much.

Gary

Rather than using code at all, you can just set the Validation Rule, as said by Cosmos, but then you just enter the message in the Validation Text property. ;)

Tried that and for some reason it didn't work. The code did do what I needed so who knows!

Gary