C
chris
I need the code for not allowing special characters in our data entry form.
Example: - < > ?
Thanks in advance for anyone that helps.
Example: - < > ?
Thanks in advance for anyone that helps.
J
John W. Vinson
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
C
chris
KenSheridan via AccessMonster.com said:
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" ?
C
chris
John W. Vinson said:
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?
J
John W. Vinson
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
D
David W. Fenton
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.
C
chris
[email protected] said:
J
John W. Vinson
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
J
John W. Vinson
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?
C
chris
John W. Vinson said:
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.
J
James A. Fortune
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)
G
Guest
[email protected] said:
J
John W. Vinson
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.
C
chris
John W. Vinson said:
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
D
David W. Fenton
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.
D
David W. Fenton
:
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!
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
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!
J
James A. Fortune
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)
J
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?
That should not cause this error...
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?
violating the *validation rule*. What data is being entered? What is the
validation rule?
That should not cause this error...
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?
J
John W. Vinson
Thanks, David. Makes sense.
D
De Jager
[email protected] said: