Validation Rule in Access97

[jkurpias]

I have several forms of table data with a lot of text & combo boxes in access97 vba. I need to insure that the users don't enter characters such as \/"'&+ etc. I pass the control text or value to a function that checks all the characters and returns the same text/value if its ok or vbNull if it isn't ok. I call the function through the control's Validation Rule and use Validation Text to display the error message.

It works just fine for valid input and invalid input. But it doesn't work at all if an item is deleted...it won't accept an empty control.

I tried returning a String, then I tried a Varient. I tried returning an un-initialized variable, vbNull, vbEmpty & "". Nothing works! Got any ideas...other than writing separate code for each control?

[JHausmann]

In your field's keypress event, place the following:

Dim wasKeyGood As Boolean

wasKeyGood = CheckKeyPress(KeyAscii)
If Not wasKeyGood Then
    KeyAscii = 0
    your_field_here.SelStart = 0
    your_field_here.SelLength = Len(your_field_here)
    Exit Sub
End If

then create a code module that does this:

Note: in my example, the user is unable to enter a single or double quote.

Public Function CheckKeyPress(nASCIIValueofKey As Integer) As Boolean

Dim sTmp    As String

CheckKeyPress = True

If nASCIIValueofKey = Asc("'") Or nASCIIValueofKey = 34 Then ' 34 = "
    nASCIIValueofKey = 0 ' cancel the character
    Beep
    CheckKeyPress = False
Else 'the user has pressed some key that we want
    'nothing, we will allow these characters
End If
    
End Function

[This message has been edited by JHausmann (edited 10-12-1999).]