Limiting entry field to text

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an unbound field on a form that I use to make a search SQL statement.
How do I limit that field to only text? For example, in the name "O'brien," the " ' " causes an
error in my SQL statement so I want to limit the field to only letters.

Is it on the keypress event for each letter entered?

Thank

Da
 
Daniel Dickover said:
I have an unbound field on a form that I use to make a search SQL statement.
How do I limit that field to only text? For example, in the name "O'brien," the " ' " causes an
error in my SQL statement so I want to limit the field to only letters.

Is it on the keypress event for each letter entered?

Thanks

Dan
Click to expand...


Hi Dan...!
You have probably more then one way, i give you two ...!

1)On TAG property write the Limit number es.: 5

On KeyDown TextBox Event Procedure write:

Private Sub TextBoxName_KeyDown(KeyCode As Integer, Shift As Integer)

If Len(Me.TextBoxName.Text) > Me.TextBoxName.Tag - 1 Then KeyCode = 0

End Sub

This second way is not easy, but work better.

2)Always use TAG property to indicate Limit Number
On your TextBox Change Event Procedure

Private Sub txtTest_Change()
Call LimitChars(Me.TextBoxName, Val(TextBoxName.Tag))
End Sub


'******** PUT THIS CODE IN MODULE *********

Private Declare Function SendMessageLong _
Lib "user32" Alias "SendMessageA" _
(ByVal hwnd As Long, ByVal wMsg As Long, _
ByVal wParam As Long, lngValue As Long) As Long

Private Declare Function GetFocus _
Lib "user32" () As Long

Private Const EM_SETLIMITTEXT As Long = &HC5

Public Sub LimitChars(txt As TextBox, lngLimit As Long)

Dim hwnd As Long
Dim lngResult As Long
Dim lngNewMax As Long

' Get the window handle for the current window.
hwnd = GetFocus()

lngNewMax = Len(txt.Text)
If lngNewMax < lngLimit Then
lngNewMax = lngLimit
End If

' Send the message to the current text box
' to limit itself to lngNewMax characters.
SendMessageLong hwnd, EM_SETLIMITTEXT, lngNewMax, 0
End Sub


Alessandro(IT)
 
You didn't say what version of Access, but if it's A2000 or higher then you
can use the Replace function:

Replace("O'Brien","''")

will return O''brien (that's two single quotes, not one double quote)

If not, google on "ms access replace" and you'll find quite a few handy
replace functions.
 
Sorry, i don't understand good the question....!
Your problem is due to TextFormatting, so the good answer of Scott
or you can format like this:
StrCondition=chr(34) & "O'brien" & chr(34)

Like this will work good...!

Sorry for my English....!
Alessandro(IT)
 
Daniel said:
I have an unbound field on a form that I use to make a search SQL statement.
How do I limit that field to only text? For example, in the name "O'brien," the " ' " causes an
error in my SQL statement so I want to limit the field to only letters.
Click to expand...


You can use the text box's BeforeUpdate event to alert the
user that alpha characters are allowed/

If textbox.Text Like "*[!a-z]*" Then
MsgBox "Only alpha characters allowed"
Cancel = True
End If

BUT, wouldn't it be far better to fix your SQL construction
code to allow for these characters? The problem of the ' in
O'Brien can be simply(?) dealt with by using " around the
text value in the SQL. For example,

strSQL = "SELECT * FROM table " & _
"WHERE [person name] = """ & txtName & """"

If your situation is more complicated than what you've
described so far, there are additional techniques that can
be used to deal with other problems.
 
Back
Top