Peter Hibbs said:What I normally do in this case is to only allow the user to enter the
required characters. To do this just paste the code below into the
KeyPress event of the FirstName and LastName text boxes.
If Chr(KeyAscii) Like "[!A-Z'-]" And _
KeyAscii <> vbKeyBack Then KeyAscii = 0
HTH
Peter Hibbs.
Need syntax for a validation rule to accept only letters, hyphen, and single
quote. These are the only characters I anticipate in fields to collect first
and last names.
Using Access 2007.
Peter,
I modified your Like statement and put it directly in the Validation Rule
property. It works. Not Like "*[!A-Z'-]*"
Thank you for your help.
Larry
Peter Hibbs said:What I normally do in this case is to only allow the user to enter the
required characters. To do this just paste the code below into the
KeyPress event of the FirstName and LastName text boxes.
If Chr(KeyAscii) Like "[!A-Z'-]" And _
KeyAscii <> vbKeyBack Then KeyAscii = 0
HTH
Peter Hibbs.
Need syntax for a validation rule to accept only letters, hyphen, and single
quote. These are the only characters I anticipate in fields to collect first
and last names.
Using Access 2007.
Peter Hibbs said:Larry.
Interesting, I have never used the Like operator in the Validation
Rule property before but, as you say, it works. You might also like to
consider adding Or Is Null to the end of the code in case the user
wants to delete all the text in the Text box (perhaps, for example,
where the FirstName information is not available).
Having said that I still think my original idea is preferable. Doing
it your way means that you allow the user to enter ANY character and
when they leave the field you then show them an error message to tell
them that what they have just entered is wrong (and presumably your
error message tells them what characters are allowed). They then have
to cancel that message box, delete what they have just entered and
then enter some new data (which is not very user friendly IMO). If, on
the other hand, you trap the errors in the KeyPress event they can
never enter an invalid character in the first place.
I get the impression from your other reply (and apologies if I'm
wrong) that you are not too comfortable with entering VBA code but it
is very easy to do, try this :-
With the form open in Design mode select the Text box.
Click on the small button with three dots that shows at the end of the
line on the On Key Press event property.
If the Choose Builder form appears, click on Code Builder and then
click OK (ignore this step if it doesn't).
The VBA form will appear with the cursor placed in the KeyPress event,
something like this (where Text1 is the name of your Text box).
Private Sub Text1_By_KeyPress(KeyAscii As Integer)
End Sub
Copy and paste the code I sent before into the form so that it appears
between the Private Sub... and End Sub lines.
Click the Save button on the main tool bar and you're done.
And don't forget to delete the Validation Rule and Validation Text
properties.
Peter Hibbs.
Peter,
I modified your Like statement and put it directly in the Validation Rule
property. It works. Not Like "*[!A-Z'-]*"
Thank you for your help.
Larry
Peter Hibbs said:What I normally do in this case is to only allow the user to enter the
required characters. To do this just paste the code below into the
KeyPress event of the FirstName and LastName text boxes.
If Chr(KeyAscii) Like "[!A-Z'-]" And _
KeyAscii <> vbKeyBack Then KeyAscii = 0
HTH
Peter Hibbs.
On Wed, 12 Mar 2008 18:32:01 -0700, tuesamlarry
Need syntax for a validation rule to accept only letters, hyphen, and single
quote. These are the only characters I anticipate in fields to collect first
and last names.
Using Access 2007.
Thank you Peter,
I succeeded with the VBA procedure On Key Press. However I added the
wildcard * inside the double quotes. Otherwise it literally allowed only 4
characters... A Z - '
Would be nice to have a gentle beep if keypuncher hits an unwanted key.
Yes, am a rank beginner on VBA, but the more samples I find, and how to
implement, the better I'm getting.
Thanks again!
Peter Hibbs said:Larry.
Interesting, I have never used the Like operator in the Validation
Rule property before but, as you say, it works. You might also like to
consider adding Or Is Null to the end of the code in case the user
wants to delete all the text in the Text box (perhaps, for example,
where the FirstName information is not available).
Having said that I still think my original idea is preferable. Doing
it your way means that you allow the user to enter ANY character and
when they leave the field you then show them an error message to tell
them that what they have just entered is wrong (and presumably your
error message tells them what characters are allowed). They then have
to cancel that message box, delete what they have just entered and
then enter some new data (which is not very user friendly IMO). If, on
the other hand, you trap the errors in the KeyPress event they can
never enter an invalid character in the first place.
I get the impression from your other reply (and apologies if I'm
wrong) that you are not too comfortable with entering VBA code but it
is very easy to do, try this :-
With the form open in Design mode select the Text box.
Click on the small button with three dots that shows at the end of the
line on the On Key Press event property.
If the Choose Builder form appears, click on Code Builder and then
click OK (ignore this step if it doesn't).
The VBA form will appear with the cursor placed in the KeyPress event,
something like this (where Text1 is the name of your Text box).
Private Sub Text1_By_KeyPress(KeyAscii As Integer)
End Sub
Copy and paste the code I sent before into the form so that it appears
between the Private Sub... and End Sub lines.
Click the Save button on the main tool bar and you're done.
And don't forget to delete the Validation Rule and Validation Text
properties.
Peter Hibbs.
Peter,
I modified your Like statement and put it directly in the Validation Rule
property. It works. Not Like "*[!A-Z'-]*"
Thank you for your help.
Larry
:
What I normally do in this case is to only allow the user to enter the
required characters. To do this just paste the code below into the
KeyPress event of the FirstName and LastName text boxes.
If Chr(KeyAscii) Like "[!A-Z'-]" And _
KeyAscii <> vbKeyBack Then KeyAscii = 0
HTH
Peter Hibbs.
On Wed, 12 Mar 2008 18:32:01 -0700, tuesamlarry
Need syntax for a validation rule to accept only letters, hyphen, and single
quote. These are the only characters I anticipate in fields to collect first
and last names.
Using Access 2007.
Peter Hibbs said:Larry,
To sound a beep when the user enters an invalid character just add the
Beep command at the end of the line like so :-
If Chr(KeyAscii) Like "[!A-Z'-]" And _
KeyAscii <> vbKeyBack Then KeyAscii = 0: Beep
However, I don't understand your statement about adding the *
characters, they should not be necessary if you use the code shown
here (although they are required when used in the Validation Rule
property because that acts in a different way).
The A-Z in the Like expression means 'match every character between A
and Z OR a and z. The ! character means NOT in this context so the
whole statement means :- set the KeyAscii code to 0 if the character
entered is NOT a to z OR NOT A to Z OR NOT ' OR NOT - (the - has to be
the last character because it means 'between' when within the square
brackets).
The Like operator is a useful function and can do all sorts of other
things as well. Have a look at the help file for more info or this Web
site :-
http://msdn.microsoft.com/archive/d...e/en-us/office97/html/output/F1/D2/S5A32E.asp
Peter Hibbs.
Thank you Peter,
I succeeded with the VBA procedure On Key Press. However I added the
wildcard * inside the double quotes. Otherwise it literally allowed only 4
characters... A Z - '
Would be nice to have a gentle beep if keypuncher hits an unwanted key.
Yes, am a rank beginner on VBA, but the more samples I find, and how to
implement, the better I'm getting.
Thanks again!
Peter Hibbs said:Larry.
Interesting, I have never used the Like operator in the Validation
Rule property before but, as you say, it works. You might also like to
consider adding Or Is Null to the end of the code in case the user
wants to delete all the text in the Text box (perhaps, for example,
where the FirstName information is not available).
Having said that I still think my original idea is preferable. Doing
it your way means that you allow the user to enter ANY character and
when they leave the field you then show them an error message to tell
them that what they have just entered is wrong (and presumably your
error message tells them what characters are allowed). They then have
to cancel that message box, delete what they have just entered and
then enter some new data (which is not very user friendly IMO). If, on
the other hand, you trap the errors in the KeyPress event they can
never enter an invalid character in the first place.
I get the impression from your other reply (and apologies if I'm
wrong) that you are not too comfortable with entering VBA code but it
is very easy to do, try this :-
With the form open in Design mode select the Text box.
Click on the small button with three dots that shows at the end of the
line on the On Key Press event property.
If the Choose Builder form appears, click on Code Builder and then
click OK (ignore this step if it doesn't).
The VBA form will appear with the cursor placed in the KeyPress event,
something like this (where Text1 is the name of your Text box).
Private Sub Text1_By_KeyPress(KeyAscii As Integer)
End Sub
Copy and paste the code I sent before into the form so that it appears
between the Private Sub... and End Sub lines.
Click the Save button on the main tool bar and you're done.
And don't forget to delete the Validation Rule and Validation Text
properties.
Peter Hibbs.
On Thu, 13 Mar 2008 16:33:03 -0700, tuesamlarry
Peter,
I modified your Like statement and put it directly in the Validation Rule
property. It works. Not Like "*[!A-Z'-]*"
Thank you for your help.
Larry
:
What I normally do in this case is to only allow the user to enter the
required characters. To do this just paste the code below into the
KeyPress event of the FirstName and LastName text boxes.
If Chr(KeyAscii) Like "[!A-Z'-]" And _
KeyAscii <> vbKeyBack Then KeyAscii = 0
HTH
Peter Hibbs.
On Wed, 12 Mar 2008 18:32:01 -0700, tuesamlarry
Need syntax for a validation rule to accept only letters, hyphen, and single
quote. These are the only characters I anticipate in fields to collect first
and last names.
Using Access 2007.
Peter Hibbs said:Larry.
Interesting, I have never used the Like operator in the Validation
Rule property before but, as you say, it works. You might also like to
consider adding Or Is Null to the end of the code in case the user
wants to delete all the text in the Text box (perhaps, for example,
where the FirstName information is not available).
Having said that I still think my original idea is preferable. Doing
it your way means that you allow the user to enter ANY character and
when they leave the field you then show them an error message to tell
them that what they have just entered is wrong (and presumably your
error message tells them what characters are allowed). They then have
to cancel that message box, delete what they have just entered and
then enter some new data (which is not very user friendly IMO). If, on
the other hand, you trap the errors in the KeyPress event they can
never enter an invalid character in the first place.
I get the impression from your other reply (and apologies if I'm
wrong) that you are not too comfortable with entering VBA code but it
is very easy to do, try this :-
With the form open in Design mode select the Text box.
Click on the small button with three dots that shows at the end of the
line on the On Key Press event property.
If the Choose Builder form appears, click on Code Builder and then
click OK (ignore this step if it doesn't).
The VBA form will appear with the cursor placed in the KeyPress event,
something like this (where Text1 is the name of your Text box).
Private Sub Text1_By_KeyPress(KeyAscii As Integer)
End Sub
Copy and paste the code I sent before into the form so that it appears
between the Private Sub... and End Sub lines.
Click the Save button on the main tool bar and you're done.
And don't forget to delete the Validation Rule and Validation Text
properties.
Peter Hibbs.
Peter,
I modified your Like statement and put it directly in the Validation Rule
property. It works. Not Like "*[!A-Z'-]*"
Thank you for your help.
Larry
Peter Hibbs said:What I normally do in this case is to only allow the user to enter the
required characters. To do this just paste the code below into the
KeyPress event of the FirstName and LastName text boxes.
If Chr(KeyAscii) Like "[!A-Z'-]" And _
KeyAscii <> vbKeyBack Then KeyAscii = 0
HTH
Peter Hibbs.
On Wed, 12 Mar 2008 18:32:01 -0700, tuesamlarry
Need syntax for a validation rule to accept only letters, hyphen, and
single
quote. These are the only characters I anticipate in fields to collect
first
and last names.
Using Access 2007.
Peter Hibbs said:Larry.
Interesting, I have never used the Like operator in the Validation
Rule property before but, as you say, it works. You might also like to
consider adding Or Is Null to the end of the code in case the user
wants to delete all the text in the Text box (perhaps, for example,
where the FirstName information is not available).
Having said that I still think my original idea is preferable. Doing
it your way means that you allow the user to enter ANY character and
when they leave the field you then show them an error message to tell
them that what they have just entered is wrong (and presumably your
error message tells them what characters are allowed). They then have
to cancel that message box, delete what they have just entered and
then enter some new data (which is not very user friendly IMO). If, on
the other hand, you trap the errors in the KeyPress event they can
never enter an invalid character in the first place.
I get the impression from your other reply (and apologies if I'm
wrong) that you are not too comfortable with entering VBA code but it
is very easy to do, try this :-
With the form open in Design mode select the Text box.
Click on the small button with three dots that shows at the end of the
line on the On Key Press event property.
If the Choose Builder form appears, click on Code Builder and then
click OK (ignore this step if it doesn't).
The VBA form will appear with the cursor placed in the KeyPress event,
something like this (where Text1 is the name of your Text box).
Private Sub Text1_By_KeyPress(KeyAscii As Integer)
End Sub
Copy and paste the code I sent before into the form so that it appears
between the Private Sub... and End Sub lines.
Click the Save button on the main tool bar and you're done.
And don't forget to delete the Validation Rule and Validation Text
properties.
Peter Hibbs.
Peter,
I modified your Like statement and put it directly in the Validation Rule
property. It works. Not Like "*[!A-Z'-]*"
Thank you for your help.
Larry
Peter Hibbs said:What I normally do in this case is to only allow the user to enter the
required characters. To do this just paste the code below into the
KeyPress event of the FirstName and LastName text boxes.
If Chr(KeyAscii) Like "[!A-Z'-]" And _
KeyAscii <> vbKeyBack Then KeyAscii = 0
HTH
Peter Hibbs.
On Wed, 12 Mar 2008 18:32:01 -0700, tuesamlarry
Need syntax for a validation rule to accept only letters, hyphen, and
single
quote. These are the only characters I anticipate in fields to collect
first
and last names.
Using Access 2007.
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.