Microsoft Access: VBA Programming Code

Provided by Allen Browne, January 2008

Insert characters at the cursor

Copy the function below into a standard module in your database. You can then call it from anywhere in your database to insert characters into the active control, at the cursor position.

If any characters are selected at the time you run this code, those characters are overwritten. That is in keeping with what normally happens in Windows programs.

Usage examples

Here are some examples of how the function could be used.

1. Simulate the tab character

In Access, the Tab key does not insert a tab character as it does in Word. To simulate this, you could insert 4 spaces when the user presses the Tab key.

Use the KeyDown event procedure of your text box, like this:

Private Sub txtMemo_KeyDown(KeyCode As Integer, Shift As Integer)
    If (KeyCode = vbKeyTab) And (Shift = 0) Then
        If InsertAtCursor("    ") Then
            KeyCode = 0
        End If
    End If
End Sub

2. Insert boilerplate text

This example inserts preset paragraphs at the cursor as the user presses Alt+1, Alt+2, etc.

Private Sub Text0_KeyDown(KeyCode As Integer, Shift As Integer)
    Dim strMsg As String
    Dim strText As String
    
    If Shift = acAltMask Then
        Select Case KeyCode
        Case vbKey1
            strText = "Paragraph 1" & vbCrLf
        Case vbKey2
            strText = "Paragraph 2" & vbCrLf
        Case vbKey3
            strText = "Paragraph 3" & vbCrLf
        'etc for other paragraphs.
        End Select
        If InsertAtCursor(strText, strMsg) Then
            KeyCode = 0
        ElseIf strMsg <> vbNullString Then
            MsgBox strMsg, vbExclamation, "Problem inserting boilerplate text"
        End If
    End If
End Sub

3. Toolbar button

For a variation on the above, you could create a buttons on a custom toolbar/ribbon that insert the paragraphs. You could allow the user to define their own paragraphs (stored in a table), and use DLookup() to retrieve the values to insert.

Note that you cannot use a command button on the form to do this: when its Click event runs, it has focus, and the attempt to insert text into the command button cannot succeed.

4. Insert today's date on a new line

In a memo field, you may want to insert a new line and today's date when Alt+D is pressed

Private Sub Text5_KeyDown(KeyCode As Integer, Shift As Integer)
    If (Shift = acAltMask) And KeyCode = vbKeyD Then
        Call InsertAtCursor(vbCrLf & Date)
    End If
End Sub

The code

Here is the code to copy into a standard module in your database:

Public Function InsertAtCursor(strChars As String, Optional strErrMsg As String) As Boolean
On Error GoTo Err_Handler
    'Purpose:   Insert the characters at the cursor in the active control.
    'Return:    True if characters were inserted.
    'Arguments: strChars = the character(s) you want inserted at the cursor.
    '           strErrMsg = string to append any error messages to.
    'Note:      Control must have focus.
    Dim strPrior As String      'Text before the cursor.
    Dim strAfter As String      'Text after the cursor.
    Dim lngLen As Long          'Number of characters
    Dim iSelStart As Integer    'Where cursor is.
    
    If strChars <> vbNullString Then
        With Screen.ActiveControl
            If .Enabled And Not .Locked Then
                lngLen = Len(.Text)
                'SelStart can't cope with more than 32k characters.
                If lngLen <= 32767& - Len(strChars) Then
                    'Remember characters before cursor.
                    iSelStart = .SelStart
                    If iSelStart > 1 Then
                        strPrior = Left$(.Text, iSelStart)
                    End If
                    'Remember characters after selection.
                    If iSelStart + .SelLength < lngLen Then
                        strAfter = Mid$(.Text, iSelStart + .SelLength + 1)
                    End If
                    'Assign prior characters, new ones, and later ones.
                    .Value = strPrior & strChars & strAfter
                    'Put the cursor back where it as, after the new ones.
                    .SelStart = iSelStart + Len(strChars)
                    'Return True on success
                    InsertAtCursor = True
                End If
            End If
        End With
    End If
    
Exit_Handler:
    Exit Function
    
Err_Handler:
Debug.Print Err.Number, Err.Description
    Select Case Err.Number
    Case 438&, 2135&, 2144& 'Object doesn't support this property. Property is read-only. Wrong data type.
        strErrMsg = strErrMsg & "You cannot insert text here." & vbCrLf
    Case 2474&, 2185&       'No active control. Control doesn't have focus.
        strErrMsg = strErrMsg & "Cannot determine which control to insert the characters into." & vbCrLf
    Case Else
        strErrMsg = strErrMsg & "Error " & Err.Number & ": " & Err.Description & vbCrLf
    End Select
    Resume Exit_Handler
End Function