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 

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"RipperT" <rippert@nOsPaM.net> wrote in message
news:%23ESW94AyHHA.4652@TK2MSFTNGP05.phx.gbl...
>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?

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

"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message 
news:OwpFoJByHHA.140@TK2MSFTNGP02.phx.gbl...
> 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
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "RipperT" <rippert@nOsPaM.net> wrote in message
> news:%23ESW94AyHHA.4652@TK2MSFTNGP05.phx.gbl...
>>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?
> 

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

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"RipperT" <rippert@nOsPaM.net> wrote in message
news:%23nrxKLNyHHA.4300@TK2MSFTNGP04.phx.gbl...
> 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
>
> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message 
> news:OwpFoJByHHA.140@TK2MSFTNGP02.phx.gbl...
>> 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" <rippert@nOsPaM.net> wrote in message
>> news:%23ESW94AyHHA.4652@TK2MSFTNGP05.phx.gbl...
>>>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? 

On Jul 17, 2:32 am, "Allen Browne" <AllenBro...@SeeSig.Invalid> wrote:
> > 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.

--