Cancelling the NotInList event

[Remster]

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

[Remster]

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.

[Remster]

Hallelujah! On the stroke of lunchtime:

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

[Remster]

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.

[RuralGuy]

Have you tried Me.YourControlName.Undo instead of Sendkeys?

[Remster]

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!

[RuralGuy]

Okay, just checking. SendKeys is not always reliable.

[Remster]

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.

[Remster]

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!

[RuralGuy]

You can always go back to the UnDo code.

[Remster]

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).

[Remster]

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:

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)?

[RuralGuy]

There is a lot to be said for "It Works!". Looks fine to me.