Create a Custom Auto Tab feature to speed data entry

Problem to resolve:

I want to make my user's data entry easier by simulating an auto tab feature with key strokes so they don't have to use the tab key or mouse when data entry is complete for long text strings such as addresses, cities, email addresses, etc.

Several solutions:

Note: This is ugly but it will work. I used 2 spaces in the example and declared a global variable to hold the value to generalize it for other future possibilities. One of the benefits of using spaces is that you don't have to strip them off because the system will do that for you automatically.

Simplest Case - Delimit with spaces

Step 1: Declare 2 global variables, one to hold the keyboard combination and the other to hold a string which we will build.

Public strHoldValue As String
Public strAutoTabIndicator As String

Step 2: Define your AutoTab key stroke combination in one of the forms early events such as the load or open events.

Private Sub Form_Open(Cancel As Integer)
strAutoTabIndicator = " "
End Sub

Step 3: Initialize the global string on the Got Focus event of your text based control.

Private Sub ControlName_GotFocus()
strHoldValue = ""
End Sub

Step 4: Build and test the global string in the Key Press event of your text based control.

Private Sub ControlName_KeyPress(KeyAscii As Integer)

strHoldValue = strHoldValue & Chr$(KeyAscii)

'Send the tab key if the autokey indicator is found
If Right(strHoldValue, Len(strAutoTabIndicator)) =
strAutoTabIndicator Then
SendKeys (Chr$(vbKeyTab))
End If

End Sub

More complicated case - Delimit with something else like ".."

The complication here is that you have to make sure you strip the autokey indicator key strokes off of your string. The code already shown will be used with any exceptions due to delimiting without spaces noted below.

Step 2: Use any key combination you want to, I used two
periods because it is very easy for a user to type

NOTE: BIG GOTCHA - If you try to use 3 periods, Access will
interpret that as an elipses and the code
will not work - Note to self: TEST FIRST

Private Sub Form_Open(Cancel As Integer)
strAutoTabIndicator = Chr$(Asc(".")) _
& Chr$(Asc ("."))
End Sub

Step 5: Use the After Update event to strip your delimiting
keystrokes off of your saved value

Private Sub ControlName_AfterUpdate()

'Strip the autotab indicator keystrokes off string
If Right$(ControlName, Len(strAutoTabIndicator)) =
strAutoTabIndicator Then
ControlName = Left$(txtName, Len(txtName) - Len(strAutoTabIndicator))
End If

End Sub

Masochistic Case - If you use the Key Down event instead of the Key Press event, the system passes you KeyCode instead of KeyAscii so you have to add 144 to your values when building the test string since the actual field value is not available to you. However, in the After Update event, you have to use the ASCII side of things. As above, only changes from the original code are noted here.

I'm not exactly sure on the +144 for key code values, so make sure you test your choices well (but of course we would have anyway)

Step 1: Declare 3 public variables

Public strHoldValue As String
Public strAutoTabIndicator As String
Public strASCIIAutoTabIndicator As String

Step 2:

Private Sub Form_Open(Cancel As Integer)

'Build the ASCII auto tab delimiting string
strASCIIAutoTabIndicator = Chr$(Asc(".")) _
& Chr$(Asc("."))

'Add 144 to the ASCII value to get the Keycode value
strAutoTabIndicator = Chr$(Asc(".") + 144) _
& Chr$(Asc(".") + 144) ' " "

End Sub

Step 4: Build and test the string using the KeyCode values

Private Sub txtName_KeyDown(KeyCode As Integer, _
Shift As Integer)

strHoldValue = strHoldValue & Chr$(KeyCode)

'Send the tab key if the autokey indicator is found
If Right$(strHoldValue, Len(strAutoTabIndicator))=
strAutoTabIndicator Then
SendKeys (Chr(vbKeyTab))
End If

End Sub

Step 5: Test and strip the delimiters based on ASCII values

Private Sub txtName_AfterUpdate()
Dim intASCIILength As Integer
Dim intLength As Integer

intASCIILength = Len(strASCIIAutoTabIndicator)
intLength = Len(txtName) -
Len(strASCIIAutoTabIndicator)

'Strip the autotab indicator keystrokes off string
If Right$(ControlName, intASCIILength) =
strASCIIAutoTabIndicator Then
txtName = Left$(txtName, intLength)
End If

End Sub

Have fun and enjoy!