Validation Rule and Validation Text Property?

[Charles]

I have a form that users use to enter order information. The primary key
for the orders table will allow the user to enter Alpha and numeric data. A
problem arises however when the user enters a blank space or a "-" anywhere
in the primary key field. Currently, I am merely telling the users not to
enter spaces or dash marks in the primary key field but for some reason,
whether by mistake or just not paying attention, I continue to find spaces
and dashes in this field. Is there a way to force the users to only type in
Alpha-Numeric characters?

Thank You

 

[Allen Browne]

A fairly simple approach is to destroy those keystrokes in the KeyPress
event of the text box.

The first example below disallows space or dash.
The second example allows only A-Z, 0-9, and a-z.

Private Sub txtPK_KeyPress(KeyAscii As Integer)
If KeyAscii = vbKeySpace Or KeyAscii = Asc("-") Then
KeyAscii = 0
End If
End Sub

Private Sub txtPK_KeyPress(KeyAscii As Integer)
Select Case KeyAscii
Case vbKeyA To vbKeyZ, vbKey0 To vbKey9, 97 To 122
'do nothing
Case Else
KeyAscii = 0
End Select
End Sub

It is actually possible for a user to paste text into the control that
contains other keystrokes, so you might want to also add other checking as
well, but the KeyPress event has the advantage of just ignoring bad typing.

 

[Charles]

Thanks Allen for your help. I went ahead and used your second example as
that seems to work the best for my application. The only problem left for
solving is how to include the "Backspace" key so if the user makes a typo
they can still "Backspace" and re-enter. Again, thanks for your help

Chuck

 

[Charles]

Also, sometimes a "Re-order" is made by a customer that is the same as their
previous order. In this case, a duplicate record is created that copies all
of the old order data into a new order. The user merely changes the order
number and is done. However, sometimes old orders will have spaces or dash
marks in the old order number and in this case the new order number is
"infected" with these old entry errors. In your previous suggestion you
said it was possible to do some error checking on the order number. Would
you mind expanding on this procedure?

Thank you.

 

[Allen Browne]

Add vbKeyBack to the list of allowed keystrokes:

Case vbKeyA To vbKeyZ, vbKey0 To vbKey9, 97 To 122, vbKeyBack

 

[Allen Browne]

You could add this Validation Rule to the field in your table (lower pane of
table design view):
Not Like "*[ -]*"

Add any other illegal characters inside the square brackets.

If you want to do it in code, use Replace() to lose the space or dash:

With Me.txtPK
If (.Value = .OldValue) Or IsNull(.Value) Then
'do nothing
Else
.Value = Replace(Replace(.Value, " ", vbNullString), "-",
vbNullString)
End If
End With