How to not allow entry of "Special Characters " in a data entry fo

[chris]

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

Thanks in advance for anyone that helps.

 

[John W. Vinson]

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

 

[chris]

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



--



.

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" ?

 

[chris]

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

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?

 

[John W. Vinson]

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

 

[David W. Fenton]

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.

 

[chris]

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

 

[John W. Vinson]

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]

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?

 

[chris]

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

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.

 

[James A. Fortune]

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?

See:

http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/791b330af688f878

It's another example of Michka's philosophy of trying to squeeze every
last iota of performance out of the software instead of fixing the
bugs . It's also an example of nearly meaningless pedantry.

James A. Fortune
(e-mail address removed)

 

[Guest]

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

 

[John W. Vinson]

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.

Please post your current code and the validation rule.

It's likely that you don't want *both* the code and the validation rule - they
are two ways to accomplish the same task; it may be that the belt is getting
in the way of fastening the suspenders.

 

[chris]

Please post your current code and the validation rule.

It's likely that you don't want *both* the code and the validation rule - they
are two ways to accomplish the same task; it may be that the belt is getting
in the way of fastening the suspenders.

Here are both codes for this field:

Private Sub company_AfterUpdate()
Me!company = UCase(Me!company)
End Sub



Private Sub company_BeforeUpdate(Cancel As Integer)
Dim Badlist As Variant
Dim i As Integer
Dim strIn As String
strIn = Me!company
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

 

[David W. Fenton]

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?

It's faster because the memory is already allocated, whereas ""
requires allocating memory on the fly, each time you use it (this is
what Michael Kaplan explained to me about it, back in the days
before I really had much understanding of VBA compilation).

Doesn't really matter for a single line, but in a loop it makes a
difference. But I try to practice efficient coding even when it
doesn't matter in order to insure that I have the habit of doing it
efficiently when it *does* matter.

 

[David W. Fenton]

:

See:

http://groups.google.com/group/comp.databases.ms-access/browse_frm/
thread/791b330af688f878

It's another example of Michka's philosophy of trying to squeeze
every last iota of performance out of the software instead of
fixing the bugs . It's also an example of nearly meaningless
pedantry.

In loops, it really can make a difference.

In this case, for a few lines of code, not so much, of course.

Also, the degree of efficiency is going to depend on whether the
parameters for Replace() are passed ByRef or ByVal. If the former,
vbNullString should be more efficent. I'd expect it to be the
latter, though.

The issues considered in that thread are not pedantic at all. They
are exactly the kinds of issues you have to consider when trying to
maximize performance in code that does a lot of string
comparison/handling in a loop.

The real takeaway for me from that thread was Dev's comment in
regard to comparing a string or string variable to vbNullString vs.
comparing it to "":

Moreover, the comparison, internally to VBA, is not the same as
for a ZLS, VBA will allocate a two byte space before doing the
comparison. WHen you compare to vbNullString, all VBA has to do
is to check if the first var is pointing to an invalid address
(zero).

In a loop, allocating that 2-byte space and comparing the two vs.
just checking the first variable alone seems to me like it could be
a major performance gain. But, of course, it would would have to be
a loop with thousands of iterations for it to matter.

It's not pedantry to use the most efficient methods all the time
such that you develop habits that insure you write efficient code
when it matters.

I mean, there's a reason there all these named constants defined
within Access's VBA environment. We should use them!

 

[James A. Fortune]

In loops, it really can make a difference.

O.K. I revise my statement to "It's also an example of nearly
meaningless pedantry unless a loop causes the minutiae to multiply to
the level of actual perception." I certainly don't want to discourage
good habits, but I also try to keep from mentally multiplying the size
of molehills. Put enough molehills in a loop and you indeed get a
mountain .

James A. Fortune
(e-mail address removed)

 

[John W. Vinson]

The error messsage is saying that the error is - apparently - due to the data
violating the *validation rule*. What data is being entered? What is the
validation rule?

Here are both codes for this field:

Private Sub company_AfterUpdate()
Me!company = UCase(Me!company)
End Sub

That should not cause this error...

Private Sub company_BeforeUpdate(Cancel As Integer)
Dim Badlist As Variant
Dim i As Integer
Dim strIn As String
strIn = Me!company
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

Try putting a breakpoint on the code by clicking in the grey bar to the left
of the VBA code window, next to the line strIn = Me!Company. Step through the
code using F8 (see the various step options in the Debug menu item). What
characters are being replaced? What was the value of strIn before the loop,
and after? At what point is the error message triggered?

 

[John W. Vinson]

It's faster because the memory is already allocated, whereas ""
requires allocating memory on the fly, each time you use it (this is
what Michael Kaplan explained to me about it, back in the days
before I really had much understanding of VBA compilation).

Doesn't really matter for a single line, but in a loop it makes a
difference. But I try to practice efficient coding even when it
doesn't matter in order to insure that I have the habit of doing it
efficiently when it *does* matter.

Thanks, David. Makes sense.

 

[De Jager]

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