Results 1 to 15 of 15
  1. #1
    Jerry Call is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    Champaign, Illinois
    Posts
    85

    Replace default error message code with message box of my own

    Very much a beginner with vba, although I used to do FoxPro. But I find vba,


    by comparison, very intimidating and frustrating to work with. However, everything I figure out on my
    own I record. So be assured that I will carefully record any successful code I can glean from the
    community.

    A text box control named "NoInFull" on my frmSodaCatalogue is set to require a numeric entry.
    It the user enters text instead Error 2113 default message swoops down. It's very user-unfriendly
    to my taste. Worse, it fails to undo the bad entry. So I want to suppress the default behavior
    and instead do the following things: (1) Cancel the error; (2) Undo the entry;
    (3) change NoInFull's value to 0 (thus, numeric), and (4) set focus back on the NoInFull box,
    so all the user has to do is type a numeric value.

    Can't make anything work so far. For openers, I'm not sure where to put the code. Seems that
    AfterUpdate() is the preferred spot, but I haven't been able to prevent the default function
    from running first. Also tried the form's On Error event, but nothing works there either.

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    No, not AfterUpdate - too late. Assuming you want to use vba and not table level validation? You don't want to cancel the error as that would be akin to ignoring it. What you want to do is either trap it in the form BeforeUpdate event and cancel the save or validate the control in the control BeforeUpdate event. Some advocate the form; I'm not that fussy about it but think sometimes it's better than waiting until the end because if you Undo against the form, everything that was entered can be lost.

    Code:
    Private Sub NoInFull_BeforeUpdate(Cancel As Integer)
    
    If Not IsNumeric(Me.NoInFull) Then
       msgbox "Some message here"
       Me.NoInFull.Undo
       Cancel = True
    End Sub
    I think that will revert the contents if there was any before and leave the focus on the control.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Jerry Call is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    Champaign, Illinois
    Posts
    85
    Jerry Call coming back. (Finally figured out how to Reply). Alas, that code never gets a chance to run.
    The default error operation fires and prevents my/your code from running.

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I suspect you have a validation rule set at the table level. If yes, I think you'd have to remove it so that Access does not default to it. If not, then there must be something pertinent that you have not revealed. If you leave the control (tab or click out), control events should fire before form update even if that 'leaving' action is to click a save button. Thus I don't think it can be a form event that's at play so my money is on a validation rule in your table.

    If not, then consider posting a compacted and zipped copy of your db for analysis. Last but not least, if you did not create the sub by choosing BeforeUpdate in the property sheet Event tab for the control, but just pasted it in as written here, then it is possible that the sub is not 'connected' to the control. I don't rely on code that's just pasted into the editor to be linked to a control.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    Jerry Call is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    Champaign, Illinois
    Posts
    85
    I fear my photographic memory indeed has no film! I checked my table setup. The Validation Rule for NoInFull is blank.
    But since I'm forced to set the type as Number, I think Error 2113 will still bang in.
    I didn't just paste in your suggested code. I retyped it from scratch.

    Would be glad to send you a compacted and zipped copy of the db if I knew how to do it.

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Look at the very top menu bar in this forum where there are 5 menu items, the rightmost being How to Attach Files.
    I checked it out and was kind of surprised as to what it links to. That should help you to post a db copy here, which is the preferred approach.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    Jerry Call is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    Champaign, Illinois
    Posts
    85
    I now have the zipped file as a shortcut on my desktop.

    How can I send it to you?

  8. #8
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You post it here. See post 6.

  9. #9
    Jerry Call is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    Champaign, Illinois
    Posts
    85
    So sorry I'm such a novice at both Access vba and this forum. But little by little I'm learning.
    You seem so quick to reply! However, I'm thinking you've probably let my case go for
    awhile. Just so you know, I'm not going to check the forum until tomorrow (Sunday).
    So no need to send any response to my sending-zip question until then (or even
    later, if you're off on Sundays).

  10. #10
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Sorry, I don't understand your last post but no problem; we're all learning something every day - hopefully. If you post a db copy here, I will look at it as soon as I can. If I'm not quick, someone else here will pick up the ball - it's such a team effort after all! As for being off on Sunday, every day is Saturday when you're retired.
    Just post your zipped db file and if you haven't figured out how yet, use the link I pointed you to.

  11. #11
    Jerry Call is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    Champaign, Illinois
    Posts
    85
    CGVending.zip

    I think I've done it! See if this attachment is the right one. It should be C&GVENDING.zip

  12. #12
    Jerry Call is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    Champaign, Illinois
    Posts
    85
    In case I was right and successfully sent you my C&G Vending zipped database, here's what you need to know
    before you tear into it.

    Open frmSodaCatalogue. In there you'll see exactly one record, and its NoInUnit field is set to 24.
    Since that's numeric, all is well. Eventually you'll want to challenge the program by typing a letter into that field
    and pressing Tab. But don't do that first thing. First, go to the BeforeUpdate event code (or maybe it's
    AfterUpdate event. Rats! Should have written it down). Anyway, it's one of those. When you get there you'll find no real
    code--just a note from me to tell me what to put there so that when you type a letter into NoInFull textbox
    and press Tab, the program will suppress the regular error, run a MsgBox that says "This field
    requires a numeric value," undoes the wrong entry, and returns focus to the textbox with a value
    of 0 installed.

    I've learned how to zip files. (At first I managed to zip only the desktop shortcut to my
    file, but I now should be able to get it right. On my computer I've created a special
    folder entitled ACCESS FORUM ZIP FILES, which should facilitate my sending you
    anything in the future. Making a little progress, I think. Lucky for me you guy(s)/gals are patient!

  13. #13
    CarlettoFed is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    255
    Take advantage of the event Change:
    Code:
    Private Sub NoInFull_Change()
        If Not IsNumeric(Me.NoInFull.Text) Then
           MsgBox "Only numeric values are allowed"
           Me.NoInFull.Undo
        End If
    End Sub

  14. #14
    Jerry Call is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    Champaign, Illinois
    Posts
    85
    Wow! After several full days of fruitless attempts on my part, you changed only a couple of things
    in my code, but they did the job. Is there a way to give prizes/praise/glory to forum responders?
    At the moment I've got everything else under control in my app, but there is one more
    hurdle I haven't tackled yet, so, like Arnold Schwarzenegger, "I (may) be back!"

  15. #15
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Haven't looked yet, but would not ordinarily use an event that fires for every key stroke - even backspace. If you're happy with that, I won't bother...

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 05-13-2018, 12:59 AM
  2. Replies: 7
    Last Post: 03-17-2016, 05:53 PM
  3. Replies: 15
    Last Post: 11-01-2013, 03:24 PM
  4. Error message code
    By FJM in forum Access
    Replies: 11
    Last Post: 09-09-2013, 04:42 AM
  5. Replies: 1
    Last Post: 03-25-2012, 01:53 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums