Text box must equal one of these characters or error (1 Viewer)

[hardhitter06]

Hi All,

Trying to figure out how I would do this without creating an additional table..

I have a field called "SurfCode".

I do not want this to be a combo box but instead allow the user to enter into a text field and if they enter a single character that doesn't equal

"A";"B";"C";"D";"G";"I";"L";"N";"O";"R";"T"

They receive an error.

I believe I would use my before update but could someone give me a little more than that?

Thank you,

 

[vbaInet]

Although, I would do it in VBA but a simple way is to use the Validation Rule:
[ABCDGILNORT]

That's it. Then you need to add a Validation Text too.

If you want to do this in VBA then yes you would use the Before Update event of the control or the form (preferrable) and check using something like this:

If Me.Textbox LIKE [ABCDGILNORT] = False Then
    Msgbox "Please enter a valid character"
End If

 

[hardhitter06]

I'm getting an error on the IF line..

"Run-time error '2465'

Missing Asset DB ca't find the field '|1' referred to in your expression

Private Sub SurfCode_BeforeUpdate(Cancel As Integer)
If Me.SurfCode Like [ABCDGILNORT] = False Then
    MsgBox "Please enter a valid SURF Code"
End If
End Sub

I did the beforeupdate of the control because I already have beforeupdate code on the form and I'm not good at combinding code lol..here it is for giggles

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim ctrl As Control

For Each ctrl In Me.Controls
   If ctrl.ControlType = acTextBox Then
      If IsNull(ctrl) Then
         MsgBox ctrl.Name & " Cannot Be Left Empty!"
         Cancel = True
         ctrl.SetFocus
         Exit Sub
         Exit For
      End If
   ElseIf ctrl.ControlType = acBoundObjectFrame Then
      If ctrl.Value & "" = "" Then
         MsgBox ctrl.Name & " cannot Be Left Empty!"
         Cancel = True
         ctrl.SetFocus
         Exit Sub
         Exit For
      End If
   End If
Next

'you still get here if you dont EXIT SUB

MsgBox "Record Saved"

End Sub

 

[vbaInet]

Sorry the patter needs to be inside quotes:

If Me.Textbox LIKE [COLOR=Red]"[/COLOR][ABCDGILNORT][COLOR=Red]"[/COLOR] = False Then
    Msgbox "Please enter a valid character"
End If

 

[hardhitter06]

Yes much better vba.

Question, how do I clear the field with an invalid entry?

I added an "undo" line but it's not working...?

Private Sub SurfCode_BeforeUpdate(Cancel As Integer)
If Me.SurfCode Like "[ABCDGILNORT]" = False Then
    MsgBox "Please enter a valid SURF Code"
    Me.SurfCode.Undo
End If
End Sub

 

[vbaInet]

Delete the undo line and add Cancel = True before the Msgbox.

 

[hardhitter06]

That's even better...thanks!!

 

[vbaInet]

You're welcome!