Results 1 to 13 of 13

Cancelling the NotInList event

  1. #1
    Remster is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    192

    Cancelling the NotInList event

    Folks

    The TaskID field in MainForm in the attached database includes both a validation rule and a NotInList event. How do I get the NotInList event not to fire if the validation rule is disobeyed?

    At present, if I enter invalid text into the field, I get the validation message then the NotInList message then the standard 'The text you entered isn't an item in the list' message. This would be extremely annoying for the user. What I want instead is for the second and third messages to be cancelled or bypassed if the validation rule is disobeyed, so the user is given a chance to correct the invalid text.

    Thanks

    Remster
    Attached Files Attached Files

  2. #2
    Remster is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    192
    Okay, I've sorted out the validation rule:

    If Not NewData Like "##-##" Then MsgBox "Blah blah blah"

    I've put this at the top of the NotInList event. But I just can't work out the next bit how to exit the event immediately.

  3. #3
    Remster is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    192
    Hallelujah! On the stroke of lunchtime:

    If Not NewData Like "##-##" Then
    MsgBox "Blah blah blah"
    Response = acDataErrContinue
    Exit Sub
    End If

  4. #4
    Remster is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    192
    One final comment. I've added 'SendKeys "{ESCAPE}"' to a new line before 'Exit Sub' so that the dropdown list closes. If anyone can suggest a better way to achieve this, I'm all ears.

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    10,914
    Have you tried Me.YourControlName.Undo instead of Sendkeys?
    (RG for short) aka Allan Bunch MS Access MVP - WinXP Pro, Win7 Pro - acXP, ac07, ac10, ac13
    If your issue is resolved...go ahead and use the Thread Tools at the top of the thread and mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  6. #6
    Remster is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    192
    I think so, but that actually clears the field, whereas I'm happier with the dropdown just closing. That way the user can see what they've done wrong!

  7. #7
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    10,914
    Okay, just checking. SendKeys is not always reliable.
    (RG for short) aka Allan Bunch MS Access MVP - WinXP Pro, Win7 Pro - acXP, ac07, ac10, ac13
    If your issue is resolved...go ahead and use the Thread Tools at the top of the thread and mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  8. #8
    Remster is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    192
    So I gather, but this isn't going to be used by such a wide range of people that it will be a big issue if it sometimes fails.

  9. #9
    Remster is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    192
    Damn, I though I'd nailed this one, but I've come up with a new problem.

    If I delete an entry in the TaskID field, I get the error message "You tried to assign the Null value to a variable that is not a Variant data type". The thing is, I want users to be able to change their minds having entered a task number, and to leave the field blank. I've tried various things with On Not in List, On Error, Before Update and so on, all to no avail. Note that the default value of the field is TaskID 16, which has a blank task name.

    Help!

  10. #10
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    10,914
    You can always go back to the UnDo code.
    (RG for short) aka Allan Bunch MS Access MVP - WinXP Pro, Win7 Pro - acXP, ac07, ac10, ac13
    If your issue is resolved...go ahead and use the Thread Tools at the top of the thread and mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  11. #11
    Remster is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    192
    The problem is that the Undo code will just reinstate the incorrect entry. I'm thinking of a scenario where someone enters a task number, saves the record, and then realises there should be no task number. I can't work out how to clear the visible column and return to the default value of the bound column (TaskID 16).

  12. #12
    Remster is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    192
    I can't believe I've lost so much of my life over this, but I think I have the solution, which incorporates your suggestion:

    Code:
    Private Sub Form_Error(DataErr As Integer, Response As Integer)
        If DataErr = 3162 Then
        Me.TaskID.Undo
        Me.DocumentTable_TaskID.Value = 16
        Response = acDataErrContinue
        End If
    End Sub
    Error 3162 is "You tried to assign a Null value to a variable that is not a Variant data type". It seems that the error was kicking in before any of the control's events had a chance to fire. Is that a likely explanation? Does the above look all right (it seems to do the trick)?

  13. #13
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    10,914
    There is a lot to be said for "It Works!". Looks fine to me.
    (RG for short) aka Allan Bunch MS Access MVP - WinXP Pro, Win7 Pro - acXP, ac07, ac10, ac13
    If your issue is resolved...go ahead and use the Thread Tools at the top of the thread and mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

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

Similar Threads

  1. Replies: 0
    Last Post: 10-12-2010, 05:08 AM
  2. OnClick event
    By HotTomales in forum Forms
    Replies: 1
    Last Post: 12-24-2009, 06:10 PM
  3. Cancelling a query
    By P5C768 in forum Queries
    Replies: 0
    Last Post: 09-11-2009, 03:25 PM
  4. Replies: 21
    Last Post: 06-03-2009, 04:54 PM
  5. Troubleshoot NotInList Event Procedure
    By skyrise in forum Programming
    Replies: 4
    Last Post: 02-23-2009, 04:06 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
  •  
Tech Forums: Microsoft Office Forums