How to check user input for illegal characters?

[deko]

Is there a way to check user input for illegal characters?

For example, a user enters something into a text box and clicks OK.
At that point I'd like to run code such as this:

illegal = Array(\, /, :, *, ?, ", <, >, |)

If Me.TextBox Contains illegal Then
MsgBox "You entered illegal characters. Please try again."
Me.TextBox = Null
Exit Sub
End If

Any suggestions welcome!

Thanks!

[PMB]

I'm not sure this is an answer, but if you use the ASCII character maps for
legal letters and numbers that would be a way to do this.

I can't help you anymore than that.

Michael
"deko" <dj****@hotmail.com> wrote in message
news:Lc*****************@newssvr27.news.prodigy.co m...

Is there a way to check user input for illegal characters?

For example, a user enters something into a text box and clicks OK.
At that point I'd like to run code such as this:

illegal = Array(\, /, :, *, ?, ", <, >, |)

If Me.TextBox Contains illegal Then
MsgBox "You entered illegal characters. Please try again."
Me.TextBox = Null
Exit Sub
End If

Any suggestions welcome!

Thanks!

[deko]

I suppose I cd put the illegal ascii codes in an array, but how to grep for
them in a string?
"PMB" <pm*****@megavision.com> wrote in message
news:mF****************@news.uswest.net...

I'm not sure this is an answer, but if you use the ASCII character maps for legal letters and numbers that would be a way to do this.

I can't help you anymore than that.

Michael
"deko" <dj****@hotmail.com> wrote in message
news:Lc*****************@newssvr27.news.prodigy.co m...

Is there a way to check user input for illegal characters?

For example, a user enters something into a text box and clicks OK.
At that point I'd like to run code such as this:

illegal = Array(\, /, :, *, ?, ", <, >, |)

If Me.TextBox Contains illegal Then
MsgBox "You entered illegal characters. Please try again."
Me.TextBox = Null
Exit Sub
End If

Any suggestions welcome!

Thanks!

[Trevor Best]

On Wed, 01 Oct 2003 22:05:31 GMT in comp.databases.ms-access, "deko"
<dj****@hotmail.com> wrote:

Is there a way to check user input for illegal characters?

For example, a user enters something into a text box and clicks OK.
At that point I'd like to run code such as this:

illegal = Array(\, /, :, *, ?, ", <, >, |)

If Me.TextBox Contains illegal Then
MsgBox "You entered illegal characters. Please try again."
Me.TextBox = Null
Exit Sub
End If

Any suggestions welcome!

what I do in my supplier shortname where I disallow special characters
to aid searching is use the KeyPress event, e.g.

(edited for brevity)
Private Sub txtShortname_KeyPress(KeyAscii As Integer)
Dim strchar As String
Dim strNoShortName As String
strNoShortName = ",.?/\¬`!"£$%^&*-+='@#~:;|<>¦-_{}[]()`¬'"
If InStr(1, strNoShortName, strchar) Then
KeyAscii = 0
End If
End Sub
--
A)bort, R)etry, I)nfluence with large hammer.

[deko]

Thanks... I'll give it a go and post back
"Trevor Best" <bouncer@localhost> wrote in message
news:qu********************************@4ax.com...

On Wed, 01 Oct 2003 22:05:31 GMT in comp.databases.ms-access, "deko"
<dj****@hotmail.com> wrote:

Is there a way to check user input for illegal characters?

For example, a user enters something into a text box and clicks OK.
At that point I'd like to run code such as this:

illegal = Array(\, /, :, *, ?, ", <, >, |)

If Me.TextBox Contains illegal Then
MsgBox "You entered illegal characters. Please try again."
Me.TextBox = Null
Exit Sub
End If

Any suggestions welcome!

what I do in my supplier shortname where I disallow special characters
to aid searching is use the KeyPress event, e.g.

(edited for brevity)
Private Sub txtShortname_KeyPress(KeyAscii As Integer)
Dim strchar As String
Dim strNoShortName As String
strNoShortName = ",.?/\¬`!"£$%^&*-+='@#~:;|<>¦-_{}[]()`¬'"
If InStr(1, strNoShortName, strchar) Then
KeyAscii = 0
End If
End Sub
--
A)bort, R)etry, I)nfluence with large hammer.

[Fletcher Arnold]

"deko" <dj****@hotmail.com> wrote in message
news:Lc*****************@newssvr27.news.prodigy.co m...

Is there a way to check user input for illegal characters?

For example, a user enters something into a text box and clicks OK.
At that point I'd like to run code such as this:

illegal = Array(\, /, :, *, ?, ", <, >, |)

If Me.TextBox Contains illegal Then
MsgBox "You entered illegal characters. Please try again."
Me.TextBox = Null
Exit Sub
End If

Any suggestions welcome!

Thanks!

You'll have to write your own function - create a new module and paste the
following code in. You will then be able to check IsClean("Whatever")
Public Function IsClean(strToCheck) As Boolean

Dim lng As Long
Dim bln As Boolean

If Len(strToCheck) > 0 Then

For lng = 1 To Len(strToCheck)

Select Case Asc(Mid$(strToCheck, lng, 1))

Case 48 To 57
' Numbers 0 to 9

Case 65 To 90
' Letters A to Z

Case 97 To 122
' Letters a to z

Case Else
bln = True
Exit For

End Select

Next lng

End If

IsClean = Not bln

End Function

[Rich P]

Private Sub TextBox_AfterUpdate()
Dim illegal As Variant, i As Integer, j As Integer

illegal = Array("\", "/", ":", "*", "?", """", _
"<", ">", "|")
For i = 0 to Ubound(illegal)
j = InStr(1, TextBox, illegal(i))
If j > 0 Then
msgbox "illegal character! " & illegal(i)
TextBox = Null
Exit For
End If
Next
End Sub

Here illegal is a variant (all variants are arrays), and I use the Array
function of VB to store the characters above). Note: You have to
delimit everything in the Array function with double quotes. You
delimit one double quote " with 3 double quotes """" (delimit 2 double
quotes "" with 3 double quotes """""). Then you use the InStr function
to check if any of the chars in the array are in the TextBox in a loop.
j would be the position of the illegal char. If j = 0 then there are no
illegal chars. If j > 0 then there is an illegal char and you specify
which illegal char was entered with illegal(i).

Is there a way to check user input for illegal characters?

For example, a user enters something into a text box and clicks OK.
At that point I'd like to run code such as this:

illegal = Array(\, /, :, *, ?, ", <, >, |)

If Me.TextBox Contains illegal Then
MsgBox "You entered illegal characters. Please try again."
Me.TextBox = Null
Exit Sub
End If

Any suggestions welcome!

Thanks!
<<

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[deko]

Outstanding!

Thanks for the helpful reply.

The issue is that Excel and Word files are generated from the database --
these files use First Name, Last Name and Company in the filename when
automatically saved to disk. If there are illegal characters in one of
those fields, the file export fails for that record.

I've put this validation routine behind the New Record entry form. I'm sure
it could be more elegant, but it works :)

thanks again for the help!

Dim i, j As Integer
Dim varFirst As Variant
Dim varLast As Variant
Dim varCo As Variant
Me.lblFirst.ForeColor = 0
Me.lblLast.ForeColor = 0
Me.lblCompany.ForeColor = 0
varNo = Array("\", "/", ":", "*", "?", """", "<", ">", "|")
If IsNull(Me.FirstName) = False Then
varFirst = Me.FirstName
For i = 0 To UBound(varNo)
j = InStr(1, varFirst, varNo(i))
If j > 0 Then
MsgBox "Cannot use character:" & Chr(13) & Chr(10) & Chr(13)
& Chr(10) & varNo(i), vbCritical, " Illegal Character"
Me.lblFirst.ForeColor = 255
Exit Sub
Exit For
End If
Next
End If
If IsNull(Me.LastName) = False Then
varLast = Me.LastName
For i = 0 To UBound(varNo)
j = InStr(1, varLast, varNo(i))
If j > 0 Then
MsgBox "Cannot use character:" & Chr(13) & Chr(10) & Chr(13)
& Chr(10) & varNo(i), vbCritical, " Illegal Character"
Me.lblLast.ForeColor = 255
Exit Sub
Exit For
End If
Next
End If
If IsNull(Me.Company) = False Then
varCo = Me.Company
For i = 0 To UBound(varNo)
j = InStr(1, varCo, varNo(i))
If j > 0 Then
MsgBox "Cannot use character:" & Chr(13) & Chr(10) & Chr(13)
& Chr(10) & varNo(i), vbCritical, " Illegal Character"
Me.lblCompany.ForeColor = 255
Exit Sub
Exit For
End If
Next
End If

"Rich P" <rp*****@aol.com> wrote in message
news:3f***********************@news.frii.net...

Private Sub TextBox_AfterUpdate()
Dim illegal As Variant, i As Integer, j As Integer

illegal = Array("\", "/", ":", "*", "?", """", _
"<", ">", "|")
For i = 0 to Ubound(illegal)
j = InStr(1, TextBox, illegal(i))
If j > 0 Then
msgbox "illegal character! " & illegal(i)
TextBox = Null
Exit For
End If
Next
End Sub

Here illegal is a variant (all variants are arrays), and I use the Array
function of VB to store the characters above). Note: You have to
delimit everything in the Array function with double quotes. You
delimit one double quote " with 3 double quotes """" (delimit 2 double
quotes "" with 3 double quotes """""). Then you use the InStr function
to check if any of the chars in the array are in the TextBox in a loop.
j would be the position of the illegal char. If j = 0 then there are no
illegal chars. If j > 0 then there is an illegal char and you specify
which illegal char was entered with illegal(i).

Is there a way to check user input for illegal characters?

For example, a user enters something into a text box and clicks OK.
At that point I'd like to run code such as this:

illegal = Array(\, /, :, *, ?, ", <, >, |)

If Me.TextBox Contains illegal Then
MsgBox "You entered illegal characters. Please try again."
Me.TextBox = Null
Exit Sub
End If

Any suggestions welcome!

Thanks!
<<

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[David W. Fenton]

bouncer@localhost (Trevor Best) wrote in
<qu********************************@4ax.com>:

On Wed, 01 Oct 2003 22:05:31 GMT in comp.databases.ms-access,
"deko"
<dj****@hotmail.com> wrote:

Is there a way to check user input for illegal characters?

For example, a user enters something into a text box and clicks
OK. At that point I'd like to run code such as this:

illegal = Array(\, /, :, *, ?, ", <, >, |)

If Me.TextBox Contains illegal Then
MsgBox "You entered illegal characters. Please try again."
Me.TextBox = Null
Exit Sub
End If

Any suggestions welcome!

what I do in my supplier shortname where I disallow special
characters to aid searching is use the KeyPress event, e.g.

(edited for brevity)
Private Sub txtShortname_KeyPress(KeyAscii As Integer)
Dim strchar As String
Dim strNoShortName As String
strNoShortName = ",.?/\¬`!"£$%^&*-+='@#~:;|<>¦-_{}[]()`¬'"
If InStr(1, strNoShortName, strchar) Then
KeyAscii = 0
End If
End Sub

Are there any RegExp libraries that can be used in Access?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Ross Presser]

dX********@bway.net.invalid (David W. Fenton) wrote in news:9408961E8dfentonbwaynetinvali@
24.168.128.90:

Are there any RegExp libraries that can be used in Access?

Of course, but you probably won't use it:

set objRegexp = createobject("Scripting.Regexp")

--
Ross Presser -- rpresser AT imtek DOT com
"... VB is essentially the modern equivalent of vulgar Latin in 13th Centurary Europe. Understand it, and
you can travel to places you never heard of and still understand some people." -- Alex K. Angelopoulos

[Rich P]

I thought about suggesting RegExp, but I believe there are more patterns
that can be entered OK than there are illegal characters to not enter.
So it seemed simpler to put the illegal chars in an array and use InStr,
otherwise you would have to check for a ton of patterns.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[deko]

Thanks for the tip. I currently don't need to create multiple worksheets,
but may need to someday.

For my purposes, which is essentially dumping a query into Excel, I use a
Make Table query (which is pretty much the same query that populates the
datasheet) and then the following code:

strXLfile = "user-defined path"
DoCmd.OutputTo acOutputTable, "tblXL_Tx", acFormatXLS, strXLfile
MsgBox "Excel file saved as:" & strXLfile, vbInformation, " Export To Excel
Complete"

It runs quickly, which is nice.

The one thing I haven't yet figured out is how to programatically set the
primay key on the newly created table.

Also, someday I'll figure out a better way to program carriage returns...
rather than "Chr(13) & Chr(10)"
"Rich P" <rp*****@aol.com> wrote in message
news:3f***********************@news.frii.net...

Just thought I would share one more quick tip (as for word, I presume
you are creating RTF files, maybe not) on passing data to Excel in a
robust manner: I would steer clear of TransferSpreadsheet. I have had
more heartache with that than I can shake a stick at. If you want to
reuse the Excel workbook that you transferspreadsheet to, you have to
delete that sheet. Plus, more times than not, if you have any
formatting on other sheets in the workbook, transferspreadsheet will
totally disrupt your formatting.

I use ADO from Access to Excel if I want to write directly to specific
worksheets and specific locations on the worksheet, or I invoke a
subroutine in Excel from Access (using
CreateObject("Excel.Application")) where in the subroutine, again, I use
ADO to retrieve the recordset from an Access MDB, or use DAO (if using
Excel97) and then use Sheets("Sheet1").Range("C5").CopyFromRecordset.
This is way more robust than using transferSpreadsheet. You can use ADO
from Access to Excel or if you use CopyFromRecordset method (works only
inside Excel), you can reference any sheet, any cell. I used "Sheet1"
Range("C5") as an example. Could have been "Sheet16" or "SheetBilly"
and Range("A1") or Range("Z25") - where ever you want to plant the data.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Bruce Rusk]

Also, someday I'll figure out a better way to program carriage returns...
rather than "Chr(13) & Chr(10)"

vbcrlf -- it's a VBA constant

[deko]

cool... thx!
"Bruce Rusk" <ho**********@spamless.ucla.edu> wrote in message
news:bl**********@gladiola.noc.ucla.edu...

Also, someday I'll figure out a better way to program carriage returns... rather than "Chr(13) & Chr(10)"

vbcrlf -- it's a VBA constant

[Rolf Østvik]

"deko" <dj****@hotmail.com> wrote in
news:Lc*****************@newssvr27.news.prodigy.co m:

Is there a way to check user input for illegal characters?

For example, a user enters something into a text box and clicks OK.
At that point I'd like to run code such as this:

Use Like and [].
[] indicates a charlist to match.

-----------------
Function StringOK(InputString As String) As Boolean
Const InvalidChars As String = "*[\/]*"
StringOK = Not InputString LIKE InvalidChars
End Function
-----------------
Explenation for InvalidChars:
The expression for InvalidChars will match anywhere in string for one of /
or \.
- the * matches any character
- [\/] matches either \ or /

Put all of the chacters you don't want inside the brackets
--
Rolf