Limit text box to any character except \, /, :, ?, <, >, !

[SurveyorinVA via AccessMonster.com]

Hello-
I am currently working in a form which contains a text box that a user will
use to create a job number and another text box for the job name. Later in
the same form, the user will have the ability to create certain directories
based upon this job number and another directory based upon the job name. I
would like to put a function behind the after_update event of each event that
will review this text box and make sure that the string entered does not
contain any character that would cause a conflict when creating a directory,
namely the following \ / : ? < > ! characters.

What would be the best approach to creating this code?

Thank you in advance.
Chris

 

[J_Goddard via AccessMonster.com]

Hi -

I use this function to do what you are looking for:

Function Valid_Field_Name(NameText As Variant) As Boolean
Dim K As Integer ' loop counter
Valid_Field_Name = True
For K = 1 To Len(Trim(NameText))
If InStr(".!`[]""", Mid(NameText, K, 1)) <> 0 Then
Valid_Field_Name = False
Exit Function
End If
Next K

End Function


I use it like this:
'
' check for non-valid characters in the column name
'
If Not Valid_Field_Name([text8]) Then
FormattedMsgBox "The column title contains character(s) that are not
valid.@" & _
"The column name cannot contain a period (.), exclamation point (!),
accent grave (`), square brackets ([]) or double quotes ("")@ ", , "Column
Name is not valid"
Cancel = -1
Exit Sub
End If


You might want to put the check in the Before Update or On Exit event of your
control, so that you can take advantage of the Cancel = -1 to cancel the
event.

These could easily be modified to suit your purposes. FormattedMsgBox is a
function used to provide a message box (3 parts to the text) in the old
format of Access 97, i.e. one bold and 2 normal paragraphs:

Function FormattedMsgBox( _
prompt As String, _
Optional buttons As Variant = vbOKOnly, _
Optional title As String = "PC - CODAP", _
Optional HelpFile As Variant, _
Optional context As Variant) As Integer

Dim strMsg As String, quote As String
Dim sTemp As String
sTemp = strtran(prompt, """", """""")
quote = Chr(34)

If IsMissing(HelpFile) Or IsMissing(context) Then
strMsg = "msgbox(" & _
quote & sTemp & quote & _
", " & buttons & _
", " & quote & title & quote & _
")"
Else
strMsg = "msgbox(" & _
quote & sTemp & quote & _
", " & buttons & _
", " & quote & title & quote & _
", " & quote & HelpFile & quote & _
", " & context & _
")"
End If
FormattedMsgBox = Eval(strMsg)
End Function

Enter this in the immediate window to see how the eval function works here:

?eval ("msgbox(""A@B@C"")")

HTH

John

 

[Marshall Barton]

I am currently working in a form which contains a text box that a user will
use to create a job number and another text box for the job name. Later in
the same form, the user will have the ability to create certain directories
based upon this job number and another directory based upon the job name. I
would like to put a function behind the after_update event of each event that
will review this text box and make sure that the string entered does not
contain any character that would cause a conflict when creating a directory,
namely the following \ / : ? < > ! characters.

Typically that kind of thing is done in the control's
BeforeUpdate event, but maybe you're doing more than the
usual.

If Me.textbox Like "*[\/:?<>!]*" Then
MsgBox "invalid character, try again"
End If

You could also use the text box's ValidationRule property:
Not Like "*[\/:?<>!]*"
and ValidationText:
"invalid character, try again"

 

[tobesurveyor via AccessMonster.com]

Thank you all for your quick response. I appreciate the help and it works
great.
Regards,
CF

I am currently working in a form which contains a text box that a user will
use to create a job number and another text box for the job name. Later in

[quoted text clipped - 4 lines]

contain any character that would cause a conflict when creating a directory,
namely the following \ / : ? < > ! characters.

Typically that kind of thing is done in the control's
BeforeUpdate event, but maybe you're doing more than the
usual.

If Me.textbox Like "*[\/:?<>!]*" Then
MsgBox "invalid character, try again"
End If

You could also use the text box's ValidationRule property:
Not Like "*[\/:?<>!]*"
and ValidationText:
"invalid character, try again"