Validating text box entry

[Malcy]

Hi
I must be needing a break already! I cannot see a quick way to do this and all my attempts have failed.
I have a textbox on a data entry form that can only be accepted if it meets one of three conditions:
It is null
It says "Yes" (or "yes")
It says "No" (or "no")

I can get around the capitalisation by using string conversion and vbProper in the before update event for the textbox so that is OK.
My latest failed attempt is (remarked out since it doesn't work!)

' Check entry boxes are correctly filled in
'    If Not IsNull(Me.txtSupColId) Or Me.txtSupColId <> "" Then
'        If Me.txtSupColId <> "Yes" Or Me.txtSupColId <> "No" Then
'            MsgBox "You must enter appropriate text.", vbCritical, "Appropriate entry required"
'                Me.txtSupColId.Undo
'                Me.txtSupColId.SetFocus
'            Exit Sub
'        End If
'    End If

I think I need to run a series of sequential tests rather than trying to do what I am doing. I wondered if anyone had a set piece for what cannot be that unusual.
Thanks

 

[MatMac]

This works...

Private Sub txtSupColId_AfterUpdate()
If (IsNull(Me.txtSupColId) Or (Me.txtSupColId = "Yes") Or (Me.txtSupColId = "No") Or (Me.txtSupColId = "yes") Or (Me.txtSupColId = "no")) Then
Else
Me.txtSupColId = ""
Me.txtSupColId.SetFocus
MsgBox "You must enter appropriate text.", vbCritical, "Appropriate entry required"
End If
End Sub

 

[stopher]

Too many “nots” will tie you up in knots. By using <> in combination with OR you will really fry your brain trying to get the logic right.

Consider:

Me.txtSupColId <> "Yes" Or Me.txtSupColId <> "No"

This will always equate to True!

Suppose txtSupColId=”yes”
Then me.txtSupColId <> "no" is True so the whole statement is true

Suppose txtSupColId=”no”
Then me.txtSupColId <> "yes" is True so the whole statement is true

Suppose txtSupColId=”anything else”
Then both sides are True so the whole statement is true!!

The following statement will be more confusing because of the fact that null doesn’t meet the criteria of Me.txtSupColId <> "" and therefore appears to work.
Not IsNull(Me.txtSupColId) Or Me.txtSupColId <> ""

Do this:

' Check entry boxes are correctly filled in
    If IsNull(Me.txtSupColId) Or Me.txtSupColId = "" Then
        'do nothing
    Else
        If Me.txtSupColId = "Yes" Or Me.txtSupColId = "No" Then
            'do nothing
        Else
            MsgBox "You must enter appropriate text.", vbCritical, "Appropriate entry required"
                Me.txtSupColId.Undo
                Me.txtSupColId.SetFocus
            Exit Sub
        End If
    End If

 

[stopher]

Just read Matmac. I simpler solution using the same logic.

I think checking for both "Yes" and "yes" is unnecessary since the = operator considers "Yes"="yes" to be true (I think).

Chris

 

[missinglinq]

stopher (before she hurts herself?) is absolutely correct; Access is case insensitive! "Yes" and "yes" are identical in Access' eyes. Actually, trying to make it case sensitive, as I've seen a few people try, is a real hairball!

 

[Malcy]

Brilliant as ever.
Thanks guys for the help. I can now see immediately where my logic went down the pan. Just breaking it out into more steps makes it easier to follow. Ok it possibly make the programme one milli-milli second longer to run but heck, it works!
Thank you

 

[neileg]

Or use an option group with three buttons, then you can only have one of three options and there's no validation needed.