Custom validation rule

[RipperT]

I have a combobox that gets it's values from a field in a table. Limit to
list and auto-expand are both set to true. The (desired) validation rule is
that the entry must be a 6 digit number (String) and not contain any letters
or special characters. If I use an input mask, it kills the auto fill-in
behavior that I want by forcing the user to click at the exact left hand
side of the text box portion of the combo box. Also, it fills in the first
entire number it finds as soon as they type the first character, whereas I
want Access to fill in the number as they enter it. Is there a way to write
a validation rule that will check that the number is 6 characters in length
and will make sure that they enter only numbers, no letters or special
characters? I've written code that checks for length, but how do I check for
numbers only?

Thanx,

Rip

 

[Allen Browne]

You could set the Validation Rule of the combo to:
Is Null OR Not Like "*[!0-9]*"
More examples of validation rules:
http://allenbrowne.com/ValidationRule.html

Another approach would be to set the combo's On KeyDown event property to:
[Event Procedure]
Click the Build button beside that.
Access opens the code window.
Between the "Private Sub..." and "End Sub" lines, enter this line:
Call DigitOnly(KeyAscii)

Now create a new module (Modules tab of Database window, and click New.) In
the new code window paste the procedure below.

What it does is to destroy all keystrokes except digits and the backspace
character. Any other key is just ignored. (A side effect is that hotkeys
such as Alt+Z to jump to another field do not work while you are in the
combo.)

Public Sub DigitOnly(KeyAscii As Integer)
On Error GoTo Err_DigitOnly
'Purpose: Disallow any keystroke except 0 ~ 9, and backspace.
'Usage: In a text box's KeyDown event procedure:
' Call DigitOnly(KeyAscii)

If KeyAscii < 48 Or KeyAscii > 57 Then
If KeyAscii <> vbKeyBack Then
KeyAscii = 0
End If
End If

Exit_DigitOnly:
Exit Sub

Err_DigitOnly:
Msgox Err.Description
Resume Exit_DigitOnly
End Sub

 

[RipperT]

Hi, Allen,
Thanks for the response. The validation rule you suggest is completely
ignored, as are my other attempts at validation rules.There must be other
code interfering. I have code that fires in the BeforeUpdate, AfterUpdate
and NotInList events. I'm not sure of the order of events, but would they be
interfering? Anything I type in the validation rule box in properties just
goes unnoticed by Access. Everything else works.

Also, the Sub DigitOnly and the call to it generated an error:

Compile error:
ByRef argument type mismatch

The code for the call looks like this:

Private Sub InmateId_KeyDown(KeyCode As Integer, Shift As Integer)
Call DigitOnly(KeyAscii)
End Sub

I don't know if maybe the two builder generated arguments have something to
do with it?

Thanks again,

Rip

You could set the Validation Rule of the combo to:
Is Null OR Not Like "*[!0-9]*"
More examples of validation rules:
http://allenbrowne.com/ValidationRule.html

Another approach would be to set the combo's On KeyDown event property to:
[Event Procedure]
Click the Build button beside that.
Access opens the code window.
Between the "Private Sub..." and "End Sub" lines, enter this line:
Call DigitOnly(KeyAscii)

Now create a new module (Modules tab of Database window, and click New.)
In the new code window paste the procedure below.

What it does is to destroy all keystrokes except digits and the backspace
character. Any other key is just ignored. (A side effect is that hotkeys
such as Alt+Z to jump to another field do not work while you are in the
combo.)

Public Sub DigitOnly(KeyAscii As Integer)
On Error GoTo Err_DigitOnly
'Purpose: Disallow any keystroke except 0 ~ 9, and backspace.
'Usage: In a text box's KeyDown event procedure:
' Call DigitOnly(KeyAscii)

If KeyAscii < 48 Or KeyAscii > 57 Then
If KeyAscii <> vbKeyBack Then
KeyAscii = 0
End If
End If

Exit_DigitOnly:
Exit Sub

Err_DigitOnly:
Msgox Err.Description
Resume Exit_DigitOnly
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a combobox that gets it's values from a field in a table. Limit to
list and auto-expand are both set to true. The (desired) validation rule
is that the entry must be a 6 digit number (String) and not contain any
letters or special characters. If I use an input mask, it kills the auto
fill-in behavior that I want by forcing the user to click at the exact
left hand side of the text box portion of the combo box. Also, it fills in
the first entire number it finds as soon as they type the first character,
whereas I want Access to fill in the number as they enter it. Is there a
way to write a validation rule that will check that the number is 6
characters in length and will make sure that they enter only numbers, no
letters or special characters? I've written code that checks for length,
but how do I check
for numbers only?

 

[Allen Browne]

The Validation Rule of the text box fires only if you type something in the
text box.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi, Allen,
Thanks for the response. The validation rule you suggest is completely
ignored, as are my other attempts at validation rules.There must be other
code interfering. I have code that fires in the BeforeUpdate, AfterUpdate
and NotInList events. I'm not sure of the order of events, but would they
be interfering? Anything I type in the validation rule box in properties
just goes unnoticed by Access. Everything else works.

Also, the Sub DigitOnly and the call to it generated an error:

Compile error:
ByRef argument type mismatch

The code for the call looks like this:

Private Sub InmateId_KeyDown(KeyCode As Integer, Shift As Integer)
Call DigitOnly(KeyAscii)
End Sub

I don't know if maybe the two builder generated arguments have something
to do with it?

Thanks again,

Rip

You could set the Validation Rule of the combo to:
Is Null OR Not Like "*[!0-9]*"
More examples of validation rules:
http://allenbrowne.com/ValidationRule.html

Another approach would be to set the combo's On KeyDown event property
to:
[Event Procedure]
Click the Build button beside that.
Access opens the code window.
Between the "Private Sub..." and "End Sub" lines, enter this line:
Call DigitOnly(KeyAscii)

Now create a new module (Modules tab of Database window, and click New.)
In the new code window paste the procedure below.

What it does is to destroy all keystrokes except digits and the backspace
character. Any other key is just ignored. (A side effect is that hotkeys
such as Alt+Z to jump to another field do not work while you are in the
combo.)

Public Sub DigitOnly(KeyAscii As Integer)
On Error GoTo Err_DigitOnly
'Purpose: Disallow any keystroke except 0 ~ 9, and backspace.
'Usage: In a text box's KeyDown event procedure:
' Call DigitOnly(KeyAscii)

If KeyAscii < 48 Or KeyAscii > 57 Then
If KeyAscii <> vbKeyBack Then
KeyAscii = 0
End If
End If

Exit_DigitOnly:
Exit Sub

Err_DigitOnly:
Msgox Err.Description
Resume Exit_DigitOnly
End Sub

I have a combobox that gets it's values from a field in a table. Limit to
list and auto-expand are both set to true. The (desired) validation rule
is that the entry must be a 6 digit number (String) and not contain any
letters or special characters. If I use an input mask, it kills the auto
fill-in behavior that I want by forcing the user to click at the exact
left hand side of the text box portion of the combo box. Also, it fills
in the first entire number it finds as soon as they type the first
character, whereas I want Access to fill in the number as they enter it.
Is there a way to write a validation rule that will check that the number
is 6 characters in length and will make sure that they enter only
numbers, no letters or special characters? I've written code that checks
for length, but how do I check
for numbers only?

 

[Jamie Collins]

The (desired) validation rule is
that the entry must be a 6 digit number (String) and not contain any
letters or special characters.

You could set theValidationRule of the combo to:
Is Null OR Not Like "*[!0-9]*"

This doesn't validate the number of characters. Consider using the
pattern:

'[0-9][0-9][0-9][0-9][0-9][0-9]'

which has the advantage of being ANSI Query Mode neutral, making it
most suitable for a field/record Validation Rule.

Jamie.

--