Consulting

Results 1 to 8 of 8

Thread: SOLVED: Need an error message

  1. #1
    VBAX Tutor GaryB's Avatar
    Joined
    Jun 2004
    Location
    Stockton, California
    Posts
    270
    Location

    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
    Last edited by GaryB; 07-22-2004 at 01:09 PM. Reason: problem solved

  2. #2
    Banned VBAX Contributor Cosmos75's Avatar
    Joined
    May 2004
    Location
    Alabama, USA
    Posts
    118
    Location

    Validating Numerical Data Entry

    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.

  3. #3
    VBAX Tutor GaryB's Avatar
    Joined
    Jun 2004
    Location
    Stockton, California
    Posts
    270
    Location
    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

  4. #4
    Banned VBAX Contributor Cosmos75's Avatar
    Joined
    May 2004
    Location
    Alabama, USA
    Posts
    118
    Location
    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).

  5. #5
    Banned VBAX Contributor Cosmos75's Avatar
    Joined
    May 2004
    Location
    Alabama, USA
    Posts
    118
    Location
    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.

  6. #6
    VBAX Tutor GaryB's Avatar
    Joined
    Jun 2004
    Location
    Stockton, California
    Posts
    270
    Location
    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

  7. #7
    VBAX Tutor SJ McAbney's Avatar
    Joined
    May 2004
    Location
    Glasgow
    Posts
    243
    Location
    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.

  8. #8
    VBAX Tutor GaryB's Avatar
    Joined
    Jun 2004
    Location
    Stockton, California
    Posts
    270
    Location
    Tried that and for some reason it didn't work. The code did do what I needed so who knows!

    Gary

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •