I need the code for not allowing special characters in our data entry form.
Example: - < > ?

Thanks in advance for anyone that helps.

On Fri, 26 Feb 2010 09:33:01 -0800, (E-Mail Removed)
<(E-Mail Removed)> wrote:

>I need the code for not allowing special characters in our data entry form.
>Example: - < > ?
>
>Thanks in advance for anyone that helps.

There are a couple of ways to do this. One would be to put a validation rule
on the table field restricting the input to the set of valid characters;
another would be to use code in the textbox's KeyPress event:

Private Sub textboxname_KeyPress(KeyAscii As Integer)
Dim strChr As String
strChr = Chr(KeyAscii) ' convert ASCII code to string
If InStr(strChr, "-<>|\/") > 0 Then ' see if it's in the list of baddies
KeyAscii = 0 ' cancel the keystroke
End If
End Sub

--

John W. Vinson [MVP]

"KenSheridan via AccessMonster.com" wrote:

> Put the following in the control's KeyPress event procedure:
>
> Const EXCLUDELIST = "-<>?"
>
> If InStr(EXCLUDELIST, Chr(KeyAscii)) > 0 Then KeyAscii = 0
>
> Just add whatever other characters you want excluding to the EXCLUDELIST
> constant.
>
> Ken Sheridan
> Stafford, England
>
> (E-Mail Removed) wrote:
> >I need the code for not allowing special characters in our data entry form.
> >Example: - < > ?
> >
> >Thanks in advance for anyone that helps.
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/For...ccess/201002/1
>
> .
>







This code works great but how do I make it so they cannot copy paste, or
maybe if they do copy a "?" into the form that it would disappear or turn to
"Null" ?

"John W. Vinson" wrote:

> On Fri, 26 Feb 2010 09:33:01 -0800, (E-Mail Removed)
> <(E-Mail Removed)> wrote:
>
> >I need the code for not allowing special characters in our data entry form.
> >Example: - < > ?
> >
> >Thanks in advance for anyone that helps.
>
> There are a couple of ways to do this. One would be to put a validation rule
> on the table field restricting the input to the set of valid characters;
> another would be to use code in the textbox's KeyPress event:
>
> Private Sub textboxname_KeyPress(KeyAscii As Integer)
> Dim strChr As String
> strChr = Chr(KeyAscii) ' convert ASCII code to string
> If InStr(strChr, "-<>|\/") > 0 Then ' see if it's in the list of baddies
> KeyAscii = 0 ' cancel the keystroke
> End If
> End Sub
>
> --
>
> John W. Vinson [MVP]
> .
>




Here again, this works awesome as well if these are manually put in but if
they are copy-pasting, it does not work.

This is my fault for not thinking about this before hand and I apologize, is
there a code to turn those "Baddies" to null if they copy them into the field
and tab out?

On Fri, 26 Feb 2010 14:08:01 -0800, (E-Mail Removed)
<(E-Mail Removed)> wrote:

>This is my fault for not thinking about this before hand and I apologize, is
>there a code to turn those "Baddies" to null if they copy them into the field
>and tab out?

You'll need to use code in the AfterUpdate event of the textbox. It will work
a bit easier if you use an array rather than a string.

Private Sub textboxname_BeforeUpdate(Cancel as Integer)
Dim Badlist As Variant
Dim i As Integer
Dim strIn As String
strIn = Me!textboxname
Badlist = Array("<", ">", "?", "/", "\", etc etc)
For i = 0 to Ubound(Badlist) - 1
If InStr(strIn, Badlist(i)) > 0 Then
strIn = Replace(strIn, Badlist(i), "") ' replace with a null string
End If
Next i
Me!textboxname = strIn
End Sub

--

John W. Vinson [MVP]

"KenSheridan via AccessMonster.com" <u51882@uwe> wrote in
news:a43b50de135ae@uwe:

> In the control's AfterUpdate event procedure execute some code
> which calls the Replace function repeatedly to replace each of the
> offending characters with a zero-length string, "".

I've often wanted to try to replicate in Access VBA the PHP way of
doing things -- its replace function can accept arrays for the
find/replace arguments. It would be very useful to have a function
that did this in Access, but it's never been so useful that I've had
the time to code it up, so I end up with the string of Replace()
statements.

I never replace with "", though -- I always use vbNullString.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

"(E-Mail Removed)" wrote:

>
>
> "John W. Vinson" wrote:
>
> > On Fri, 26 Feb 2010 14:08:01 -0800, (E-Mail Removed)
> > <(E-Mail Removed)> wrote:
> >
> > >This is my fault for not thinking about this before hand and I apologize, is
> > >there a code to turn those "Baddies" to null if they copy them into the field
> > >and tab out?
> >
> > You'll need to use code in the AfterUpdate event of the textbox. It will work
> > a bit easier if you use an array rather than a string.
> >
> > Private Sub textboxname_BeforeUpdate(Cancel as Integer)
> > Dim Badlist As Variant
> > Dim i As Integer
> > Dim strIn As String
> > strIn = Me!textboxname
> > Badlist = Array("<", ">", "?", "/", "\", etc etc)
> > For i = 0 to Ubound(Badlist) - 1
> > If InStr(strIn, Badlist(i)) > 0 Then
> > strIn = Replace(strIn, Badlist(i), "") ' replace with a null string
> > End If
> > Next i
> > Me!textboxname = strIn
> > End Sub
> >
> > --
> >
> > John W. Vinson [MVP]
> > .
> >
>
>
>
>
> Ok, I think I've almost got this. This is only my second week messing around
> with coding. So here's what I've got.
>
>
> Private Sub company_BeforeUpdate(Cancel as Integer)
> Dim Badlist As Variant
> Dim i As Integer
> Dim strIn As String
> strIn = Me!textboxname
> Badlist = Array("<", ">", "?", "/", "\")
> For i = 0 to Ubound(Badlist) - 1
> If InStr(strIn, Badlist(i)) > 0 Then
> strIn = Replace(strIn, Badlist(i), "("<", ">", "?", "/", "\")") '
> replace with a null string
> End If
> Next i
> Me!company = strIn
> End Sub
>
> It keeps giving me an error and bring me to "replace". I am assuming it has
> something to do with "replace with a null string"
>
> Thanks Again John
>
> -Chris
>
> strIn = Me!company *I have this too

On Mon, 1 Mar 2010 12:08:01 -0800, (E-Mail Removed)
<(E-Mail Removed)> wrote:

>Private Sub company_BeforeUpdate(Cancel as Integer)
> Dim Badlist As Variant
>Dim i As Integer
> Dim strIn As String
> strIn = Me!textboxname
> Badlist = Array("<", ">", "?", "/", "\")
> For i = 0 to Ubound(Badlist) - 1
> If InStr(strIn, Badlist(i)) > 0 Then
> strIn = Replace(strIn, Badlist(i), "("<", ">", "?", "/", "\")") '
>replace with a null string
> End If
> Next i
>Me!company = strIn
>End Sub

Well, that isn't what I suggested. What I suggested would work fine -
replacing the current element of the Badlist array with an empty string "".
You changed it and now it won't work.

The text "replace with a null string" is after a ' character, which makes it a
comment in the VBA code. The comment is of course optional and can be omitted.

Replace that line with

strIn = Replace(strIn, Badlist(i), "")

What the code will do is loop through all the elements in the Badlist array -
using i as a subscript to check elements 0, 1, 2 and so on; it wil then
replace that character with an empty string "", so a text string

3<5

will be converted to

35

--

John W. Vinson [MVP]

On 1 Mar 2010 02:27:55 GMT, "David W. Fenton" <(E-Mail Removed)>
wrote:

>I never replace with "", though -- I always use vbNullString.

They're synonyms - any reason to prefer the builtin constant to the literal
constant? I guess it's easier to read, but is it faster, or just cleaner code?
--

John W. Vinson [MVP]

"John W. Vinson" wrote:

> On Mon, 1 Mar 2010 12:08:01 -0800, (E-Mail Removed)
> <(E-Mail Removed)> wrote:
>
> >Private Sub company_BeforeUpdate(Cancel as Integer)
> > Dim Badlist As Variant
> >Dim i As Integer
> > Dim strIn As String
> > strIn = Me!textboxname
> > Badlist = Array("<", ">", "?", "/", "\")
> > For i = 0 to Ubound(Badlist) - 1
> > If InStr(strIn, Badlist(i)) > 0 Then
> > strIn = Replace(strIn, Badlist(i), "("<", ">", "?", "/", "\")") '
> >replace with a null string
> > End If
> > Next i
> >Me!company = strIn
> >End Sub
>
> Well, that isn't what I suggested. What I suggested would work fine -
> replacing the current element of the Badlist array with an empty string "".
> You changed it and now it won't work.
>
> The text "replace with a null string" is after a ' character, which makes it a
> comment in the VBA code. The comment is of course optional and can be omitted.
>
> Replace that line with
>
> strIn = Replace(strIn, Badlist(i), "")
>
> What the code will do is loop through all the elements in the Badlist array -
> using i as a subscript to check elements 0, 1, 2 and so on; it wil then
> replace that character with an empty string "", so a text string
>
> 3<5
>
> will be converted to
>
> 35
>
> --
>
> John W. Vinson [MVP]
> .
>






Here's what I'm getting with that code:

Run-time error '2115':

The macro or function set to the BeforeUpdate or validationRule property for
this field is preventing Microsoft Office Accesss from saving the data in the
field.