Create Disable Alphabetic Keys Function Based on Case Statement

A

andigirlsc

Registered User.
Local time
Today, 08:48
Joined
Jun 4, 2014
Messages
59
Details
I have a Profile form that tracks the expiration date for each client’s various certifications. These dates are set up in the Short Date format in the table design of Access.

Problem
When a user accidentally presses an alphabetic key while updating an expiration date, an Access error message is triggered. This is confusing to my users as these messages are written in Access lingo. I would rather that nothing occurs at all. I wrote a case statement to disable each letter of the alphabet and applied it to the On Key Down Event for each expiration date control on my Profile form to solve this problem, but this must be applied to 28 separate controls. I would rather call a function that disables alphabetic keys for each date control in my form when called.

Questions
How do I transform my Disable Alphabetic Keys Case Statement into a function that I can call for each expiration date control? I know that when writing a function certain variables have to be declared and/or initialized, but I need help with this.

Also, will I need to create a function to re-enable alphabetic keys or is this unnecessary because the disable alpha keys function will only be called for specific controls, not the entire form?

What I Have Tried
I have tried copying and pasting my Disable Alphabetic Keys Case Statement into a module to attempt to create a function, but it needs work. Any help that can be provided would be greatly appreciated.

Below I have included 2 types of code:
(1) The original On Key Down code applied to each date control on my form
(2) The same code written as an attempt at a function

Original Profile Form Code to Disable Alphabetic Keys in the On Key Down event for each date control
Code: 
Private Sub txtCert1ExpDate_KeyDown(KeyCode As Integer, Shift As Integer)

    Select Case KeyCode
   
   'All message box text is for me to test the code, not for the user to see

    Case vbKeyA
        MsgBox ("you pressed the A key")
        KeyCode = 0

    Case vbKeyB
        MsgBox ("you pressed the B key")
        KeyCode = 0

    Case vbKeyC
        MsgBox ("you pressed the C key")
        KeyCode = 0

    Case vbKeyD
        MsgBox ("you pressed the D key")
        KeyCode = 0

    Case vbKeyE
        MsgBox ("you pressed the E key")
        KeyCode = 0

    Case vbKeyF
        MsgBox ("you pressed the F key")
        KeyCode = 0

    Case vbKeyG
        MsgBox ("you pressed the G key")
        KeyCode = 0

    Case vbKeyH
        MsgBox ("you pressed the H key")
        KeyCode = 0

    Case vbKeyI
        MsgBox ("you pressed the I key")
        KeyCode = 0

    Case vbKeyJ
        MsgBox ("you pressed the J key")
        KeyCode = 0

    Case vbKeyK
        MsgBox ("you pressed the K key")
        KeyCode = 0

    Case vbKeyL
        MsgBox ("you pressed the L key")
        KeyCode = 0

    Case vbKeyM
        MsgBox ("you pressed the M key")
        KeyCode = 0

    Case vbKeyN
        MsgBox ("you pressed the N key")
        KeyCode = 0

    Case vbKeyO
        MsgBox ("you pressed the O key")
        KeyCode = 0

    Case vbKeyP
        MsgBox ("you pressed the P key")
        KeyCode = 0

    Case vbKeyQ
        MsgBox ("you pressed the Q key")
        KeyCode = 0

    Case vbKeyR
        MsgBox ("you pressed the R key")
        KeyCode = 0

    Case vbKeyS
        MsgBox ("you pressed the S key")
        KeyCode = 0

    Case vbKeyT
        MsgBox ("you pressed the T key")
        KeyCode = 0

    Case vbKeyU
        MsgBox ("you pressed the U key")
        KeyCode = 0

    Case vbKeyV
        MsgBox ("you pressed the V key")
        KeyCode = 0

    Case vbKeyW
        MsgBox ("you pressed the W key")
        KeyCode = 0

    Case vbKeyX
        MsgBox ("you pressed the X key")
        KeyCode = 0

    Case vbKeyY
        MsgBox ("you pressed the Y key")
        KeyCode = 0

    Case vbKeyZ
        MsgBox ("you pressed the Z key")
        KeyCode = 0

End Select
End Sub

Function to Disable Alphabetic Keys Code

Code: 
Option Compare Database

Public Function DisableAlphaKeys()
On Error GoTo TheError

  'Disable alphabetic keys listed below by setting KeyCode to 0.
  'All message box text is for me to test the code, not for the user to see
 
   Select Case KeyDown

    Case vbKeyA
        MsgBox ("you pressed the A key")
        KeyCode = 0

    Case vbKeyB
        MsgBox ("you pressed the B key")
        KeyCode = 0

    Case vbKeyC
        MsgBox ("you pressed the C key")
        KeyCode = 0

    Case vbKeyD
        MsgBox ("you pressed the D key")
        KeyCode = 0

    Case vbKeyE
        MsgBox ("you pressed the E key")
        KeyCode = 0

    Case vbKeyF
        MsgBox ("you pressed the F key")
        KeyCode = 0

    Case vbKeyG
        MsgBox ("you pressed the G key")
        KeyCode = 0

    Case vbKeyH
        MsgBox ("you pressed the H key")
        KeyCode = 0

    Case vbKeyI
        MsgBox ("you pressed the I key")
        KeyCode = 0

    Case vbKeyJ
        MsgBox ("you pressed the J key")
        KeyCode = 0

    Case vbKeyK
        MsgBox ("you pressed the K key")
        KeyCode = 0

    Case vbKeyL
        MsgBox ("you pressed the L key")
        KeyCode = 0

    Case vbKeyM
        MsgBox ("you pressed the M key")
        KeyCode = 0

    Case vbKeyN
        MsgBox ("you pressed the N key")
        KeyCode = 0

    Case vbKeyO
        MsgBox ("you pressed the O key")
        KeyCode = 0

    Case vbKeyP
        MsgBox ("you pressed the P key")
        KeyCode = 0

    Case vbKeyQ
        MsgBox ("you pressed the Q key")
        KeyCode = 0

    Case vbKeyR
        MsgBox ("you pressed the R key")
        KeyCode = 0

    Case vbKeyS
        MsgBox ("you pressed the S key")
        KeyCode = 0

    Case vbKeyT
        MsgBox ("you pressed the T key")
        KeyCode = 0

    Case vbKeyU
        MsgBox ("you pressed the U key")
        KeyCode = 0

    Case vbKeyV
        MsgBox ("you pressed the V key")
        KeyCode = 0

    Case vbKeyW
        MsgBox ("you pressed the W key")
        KeyCode = 0

    Case vbKeyX
        MsgBox ("you pressed the X key")
        KeyCode = 0

    Case vbKeyY
        MsgBox ("you pressed the Y key")
        KeyCode = 0

    Case vbKeyZ
        MsgBox ("you pressed the Z key")
        KeyCode = 0
End Select

TheError:
MsgBox Err.Description

End Function
 
Why do users hit an alpha key? Have you considered a calendar from which the user selects a Date?
Just thinking of a way to prevent the error at source.

You might try (air code --untested)
Code: 
Select Case KeyDown
  Case 48 to 55   'numeric key but not necessarily a date???

  Case else
   Msgbox "You pressed a non Numeric key..... and your action  follows"
   KeyCode = 0

End select
 
Last edited:
jdraw said:
Why do users hit an alpha key? Have you considered a calendar from which the user selects a Date?
Just thinking of a way to prevent the error at source.
Click to expand...

@jdraw

Thank you for the suggestion of using values instead of individual letters for the case statement. This does shorten the code, but I am still interested in writing a function.

The Date Picker is already set up as a part of the control, but it is not always used. Some users use the keypad while some use the numeric keyboard above the alphabetic keys. Everyone is different.

Users hit alpha keys accidentally and sometimes even out of curiosity to test the database, although this is not their job. My goal is to turn my existing case statement into a function.

Any ideas?
 
Last edited:
Is there any particular reason for using the Select/Case Statement Here? I offer my apologies in advance if I misunderstood your intent, but I believe that the following could be a simpler approach:

Code: 
 IF ((KeyDown between 65 and 90) or (KeyDown between 97 and 122)THEN
     MsgBox ("you pressed the " & Chr(KeyDown) & " key")
    KeyCode = 0
END IF
As an additional point, jdraw is right about using the calendar to select dates, but if you still want/need to enter by hand, try the approach described above.

-- Rookie

beaten to the "jdraw" again?
 
Last edited:
MSAccessRookie said:
Is there any particular reason for using the Select/Case Statement Here? I offer my apologies in advance if I misunderstood your intent, but I believe that the following could be a simpler approach:

Code: 
 IF ((KeyDown between "A" and "Z") or (KeyDown between "a" and "z") THEN
     MsgBox ("you pressed the " & KeyDown & " key")
    KeyCode = 0
END IF
As an additional point, jdraw is right about using the calendar to select dates, but if you still want/need to enter by hand, try the approach described above.

-- Rookie

beaten to the "jdraw" again?
Click to expand...


@MSAccessRookie

Thank you for your help, but as I mentioned in my reply to jdraw, I am trying to write a function. I am not looking for a shorter case statement.

Please re-read my original post.
 
Perhaps you could tell us more about the date being entered.
If the user types a "/" as a separator or a "-", that would be acceptable with your coode, but rejected by what I suggested.
I think Rookie's question re the Select Case should be investigated.

OOps: I'm a slow typer, you answered while I was thinking/writing.

I still think you should be thinking in terms of preventing non intended keys/responses from being entered/considered.

Perhaps the use of validation rules would help or led to a practical solution.
The IsDate() function may apply.

Do you have the logic set up for a proposed function?
 
Last edited:
jdraw said:
Perhaps you could tell us more about the date being entered.
If the user types a "/" as a separator or a "-", that would be acceptable with your coode, but rejected by what I suggested.
I think Rookie's question re the Select Case should be investigated.
Click to expand...

@jdraw

My current case statement works fine. This was never the problem. I am only asking to re-write my case statement from Post #1 as a function so that it can be called when needed for date controls. I don't want users to see Access error messages because they are confused by them, that's all.

The "/" and "-" symbols are not an issue with entering the dates. Acceptable dates include: 1/1/2012 and 1-1-2012

So, back to my original question...

Can you turn my case statement from Post #1 into a function? This is all I want to do. I have 28 date controls, one for each expiration date, and I do not want 28 separate case statements spread across my code when I could simply call a function with one line of code: "Call DisableAlphaKeys"

Any thoughts on writing the function?
 
I did add a few comments to my last - again slow typing/editing on my part.

I don't think disabling the alpha keys is the solution. It may be part of solution, but I think IsDate() and a calendar is involved.

Some thoughts:

user must enter a valid date
-validate on exit of control, and on before update of form
--must be valued
--must be a date
--must be in a range or some criteria
----Do you really have to check each keystroke????
 
Last edited:
From your many control event handler you can call a generic function like this . . .
Code: 
Public Function MyCustomKeyCodeFilter(KeyCode as integer)
[COLOR="Green"]'  Filters out unwanted keycodes[/COLOR]
   If KeyCode = 13 Or KeyCode = 10 Then
[COLOR="Green"]      'use your own If block expression here[/COLOR]
      MyCustomKeyCodeFilter = 0
   Else
      MyCustomKeyCodeFilter = KeyCode
   End If
End Function
Each of your controls will have a handler like this, from which you call your public function . . .
Code: 
Private Sub txtCert1ExpDate_KeyDown(KeyCode As Integer, Shift As Integer)
   KeyCode = MyCustomKeyCodeFilter(KeyCode)
End Sub
hth
 
jdraw said:
I did add a few comments to my last - again slow typing/editing on my part.

I don't think disabling the alpha keys is the solution. It may be part of solution, but I think IsDate() and a calendar is involved.
Click to expand...

@jdraw

The expiration date fields in my Access table are in fact set up as date/time fields using the Short Date format. But as I mentioned before, any non-numeric keys (except for "/" and "-") generate an Access validation rule error message which only confuses my users. Disabling the keys I do not want users to touch (for whatever reason) is a quick solve to this problem.

My users are not computer savvy and to avoid a 5 minute error message explanation times "x" number of users per day, I would rather disable keys that are not useful for certain controls.

I understand that you want me to think about changing the setup of the field from the original table, but that isn't really what I would like to do. I would rather make changes to the form, because this is what gets distributed as a Front End to users each time there is an update. Making changes to the original table design in the Back End is a rare sacrifice as it inhibits usage for all users and puts enormous pressure on me to "hurry up and fix the problem", which is not an ideal environment for debugging and testing. In my case, I have to make changes to the form controls.

Thank you for the additions to Post #7. I have looked into them. The problem that remains is that I do not know how to properly write a function, hence my original post.

With that in mind, is there any way to make my case statement into a function?
 
MarkK said:
From your many control event handler you can call a generic function like this . . .
Code: 
Public Function MyCustomKeyCodeFilter(KeyCode as integer)
[COLOR="Green"]'  Filters out unwanted keycodes[/COLOR]
   If KeyCode = 13 Or KeyCode = 10 Then
[COLOR="Green"]      'use your own If block expression here[/COLOR]
      MyCustomKeyCodeFilter = 0
   Else
      MyCustomKeyCodeFilter = KeyCode
   End If
End Function
Each of your controls will have a handler like this, from which you call your public function . . .
Code: 
Private Sub txtCert1ExpDate_KeyDown(KeyCode As Integer, Shift As Integer)
   KeyCode = MyCustomKeyCodeFilter(KeyCode)
End Sub
hth
Click to expand...

@MarkK

Thank you for your post. I will try this and report back.
 
In post #9, Markk has shown a function as per your thoughts.

Not meaning to prolong a discussion unnecessarily, but could you tell us about your current validation rule and message.
 
MarkK said:
From your many control event handler you can call a generic function like this . . .
Code: 
Public Function MyCustomKeyCodeFilter(KeyCode as integer)
[COLOR="Green"]'  Filters out unwanted keycodes[/COLOR]
   If KeyCode = 13 Or KeyCode = 10 Then
[COLOR="Green"]      'use your own If block expression here[/COLOR]
      MyCustomKeyCodeFilter = 0
   Else
      MyCustomKeyCodeFilter = KeyCode
   End If
End Function
Each of your controls will have a handler like this, from which you call your public function . . .
Code: 
Private Sub txtCert1ExpDate_KeyDown(KeyCode As Integer, Shift As Integer)
   KeyCode = MyCustomKeyCodeFilter(KeyCode)
End Sub
hth
Click to expand...

@MarkK

A million thanks! This is PRECISELY what I was asking for originally. You have solved my problem. I can see now that my original code would have worked had I used the following line of code to call the function:
Code: 
KeyCode = DisableAlphaKeys(KeyCode)

Thank you so much! I did revise my code to account for my entire case statement which includes all alphabetic keys, but it works seamlessly now! I can't thank you enough. This was EXACTLY what I asked for. Here are the revisions I made in order to get the code to work for me. Using your function as a guide, I can now write other short functions to disable other keys that are accidentally pressed to avoid triggering error messages.

Your Original Code
Code: 
Public Function MyCustomKeyCodeFilter(KeyCode as integer)
[COLOR="Green"]'  Filters out unwanted keycodes[/COLOR]
   If KeyCode = 13 Or KeyCode = 10 Then
[COLOR="Green"]      'use your own If block expression here[/COLOR]
      MyCustomKeyCodeFilter = 0
   Else
      MyCustomKeyCodeFilter = KeyCode
   End If
End Function

My Revised Code
Code: 
Option Compare Database

Public Function DisableAlphaKeys(KeyCode As Integer)
'  Filters out alphabetic keys
    [COLOR="Blue"]Select Case KeyCode
        Case 65 To 90
        MsgBox ("you pressed an alpha key")  'this is for testing only
        KeyCode = 0
    End Select[/COLOR]
End Function

The On Key Down Event Used to Call the Function
Code: 
Private Sub txtCert1ExpDate_KeyDown(KeyCode As Integer, Shift As Integer)

     [COLOR="Blue"]KeyCode = DisableAlphaKeys(KeyCode)[/COLOR]

End Sub
 
Correction
Post #13 has an error in it on my part. Revising the original code in Post #9 to make it more like a Case statement caused all keys to be disabled, not just alpha keys. Here is the corrected code based on the If/Then statement from Post #9 with the entire list of VBA key codes needed to disable all of the alphabetic keys. The line of code from Post #13 to call the function still works properly and does not need to be corrected and re-posted.

Enjoy!

Corrected Code Using Post #9
The blue indicates the corrections made.

Code: 
Option Compare Database

Public Function DisableAlphaKeys(KeyCode As Integer)

'Disables all alphabetic keys when called on the On Key Down event of a date control
[COLOR="Blue"]If KeyCode = 65 Or KeyCode = 78 Or _
   KeyCode = 66 Or KeyCode = 79 Or _
   KeyCode = 67 Or KeyCode = 80 Or _
   KeyCode = 68 Or KeyCode = 81 Or _
   KeyCode = 69 Or KeyCode = 82 Or _
   KeyCode = 70 Or KeyCode = 83 Or _
   KeyCode = 71 Or KeyCode = 84 Or _
   KeyCode = 72 Or KeyCode = 85 Or _
   KeyCode = 73 Or KeyCode = 86 Or _
   KeyCode = 74 Or KeyCode = 87 Or _
   KeyCode = 75 Or KeyCode = 88 Or _
   KeyCode = 76 Or KeyCode = 89 Or _
   KeyCode = 77 Or KeyCode = 90 Then[/COLOR]
   
    MsgBox ("you pressed an alpha key")  'This comment is for testing only
    DisableAlphaKeys = 0
Else
    DisableAlphaKeys = KeyCode
End If
End Function
 
Your error in post #13 is that your custom function is never assigned a value, so always returned zero, so always cancelled all keystrokes. Amend as follows . . .
Code: 
Public Function DisableAlphaKeys(KeyCode As Integer)[COLOR="Red"] As Integer[/COLOR]
[COLOR="Green"]'   Filters out alphabetic keys[/COLOR]
    Select Case KeyCode
        Case 65 To 90
[COLOR="Red"]            DisableAlphaKeys[/COLOR] = 0
[COLOR="Red"]        Case Else
            DisableAlphaKeys = KeyCode
[/COLOR]    End Select
End Function
I think that verbose if block should be avoided.
 
MarkK said:
Your error in post #13 is that your custom function is never assigned a value, so always returned zero, so always cancelled all keystrokes. Amend as follows . . .
Code: 
Public Function DisableAlphaKeys(KeyCode As Integer)[COLOR="Red"] As Integer[/COLOR]
[COLOR="Green"]'   Filters out alphabetic keys[/COLOR]
    Select Case KeyCode
        Case 65 To 90
[COLOR="Red"]            DisableAlphaKeys[/COLOR] = 0
[COLOR="Red"]        Case Else
            DisableAlphaKeys = KeyCode
[/COLOR]    End Select
End Function
I think that verbose if block should be avoided.
Click to expand...

@MarkK

Thank you for bringing this to my attention. I now fully understand what error was made. As a result, I have revised my code with the corrections you have shown in Post #15, tested it and found that it works perfectly.

Thank you once again!
 
You must log in or register to reply here.

Similar threads

T
Solved Create Function for all comboboxes to lock them for typing new values
Replies
5
Views
733
MajP
MajP
oxicottin
Disable arrow keys
Replies
14
Views
1,598
cheekybuddha
cheekybuddha
Garren.Shannon
10 key keypad
Replies
1
Views
545
June7
June7
Z
key entry trapping using On KeyDown
Replies
8
Views
808
Zedster
Z
S
Solved How to create a dynamic search filter using text box and multiple filters on a continuous form
Replies
6
Views
3,860
Estuardo416
E

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom