Access 2016 Live search Error 2185 on secondary passes only

[HalloweenWeed]

Good day I am new to the forum and still learning MS Access. I have developed a code to allow 'live' 'searh-on-the-fly' queries 'as you type.' However, the problem lies in that when doing a query you lose the focus of the control in use, and when the SetFocus is set back to the control it selects all the text typed so far, so the next typed letter overwrites all the previous. I have developed a workaround that deselects the text and moves the cursor to the end, but after the first character the code

FN_buffer.SelStart = Len(FN_input)

no longer works, throwing a 2185 error "Can't reference a property unless the control has the focus." Now it seems to me that this subroutine would not be running if the control didn't have the focus! From there it just snowballs: the previous code

Me.FN_input.Value = Me.FN_buffer.Text

quits working throwing 2185, and what's more the other field's identical code (LN, last name) is affected by this one's actions, causing it to fail throwing 2185 on the first character entered.


MS Access 2016 continuous form bound to a query. Unbound textbox controls in the header, the _input control is a criteria for the query. The _input control is 'locked,' the _buffer control is not.

It transfers the first two characters to the _input control before it begins failing.

Private Sub FN_buffer_KeyUp(KeyCode As Integer, Shift As Integer)
'This subroutine institutes a live-action query on every character entered
'this brings up on-the-fly match results as the user types each character.
'Only numerical, alphanumerical, Space, or backspace characters count for this action.
'This code is for the (unbound) [textbox]_buffer control, the 'hidden' [textbox]_input
' control is the actual (unbound) control coded as search criteria the query.
'The [textbox]_input control is only filled in (and cleared) using this code.
'Note that the delete key actions do not fire here, and therefore
' they will have unintended consequences, so should be avoided.
On Error GoTo Err_Handler
 
If (Not IsMissing(KeyCode)) Then
    If (KeyCode > 47 And KeyCode < 91) Or KeyCode = vbSpace Or KeyCode = vbKeyBack Then
    ' If key is numerical, alphanumerical, Space, or backspace then transfer it to live query
        'the following code prevents #2185 errors with null .Text value of this control
MsgBox ("FN_charCount = " & FN_charCount)
        If KeyCode = vbKeyBack And FN_charCount > 0 Then
            FN_charCount = FN_charCount - 1 'decrement count on backspace key (if not zero length previously)
MsgBox ("Decrement fired")
        Else
            FN_charCount = FN_charCount + 1 'count characters in text
MsgBox ("Increment fired")
MsgBox ("FN_charCount = " & FN_charCount)
        End If
        If FN_charCount > 0 Then        'Null .Text property throws error
            Me.FN_input.Value = Me.FN_buffer.Text
            'move the current typed text to (hidden) query control textbox
MsgBox ("Text move fired")
        Else
MsgBox ("About to clear search string")
            Me.FN_input.Value = vbNullString
            'if text calculates as Null, make input control value Null also
MsgBox ("Search string should be cleared")
        End If
MsgBox ("Requery imminent")
        Me.Requery                      'query in real-time on-the-fly
MsgBox ("Requery occurred")
        Me.FN_buffer.SetFocus  'return cursor to control (textbox)
MsgBox ("Focus occurred")
        If FN_charCount > 0 Then        'Null selection properties throws #2185 errors
            Me.FN_buffer.SelLength = 0 'unselect text and...
            Me.FN_buffer.SelStart = Len(FN_input)
            'set cursor to end ready for the next char
MsgBox ("Deselected text in textbox")
        End If
MsgBox ("FN_charCount = " & FN_charCount)
    End If
End If
 
ExitSub:
    Exit Sub
 
Err_Handler:
        MsgBox ("Error #" & Err.Number & ": " & Err.Description)
    Resume ExitSub
 
End Sub
 

I added the 'Msgbox's to debug, removing them makes no functional difference. Adding a 2185 trap does not help, as it still fails to move the cursor or transfer the text to the _input textbox, and then fails to requery as well. Please help, I have googled several different ways and can't find anything shedding any light on this issue.

[HalloweenWeed]

Both textbox controls are set "visible." The _input textbox control is "hidden" by way of setting both the background and font colors to same as form header background (it is set "visible"). The "locked" trait of _input prevents the user from manually editing it, and I have tried unlocking it and that doesn't help.

The purpose of this subroutine is to query to prevent the user from adding a duplicate record. If the query has a result, it will not allow the user to continue to the autofilled standard edit form, in new record mode (I've tested this and it works fine). And a button in the detail allows the user to automatically select the proper record if it exists (also tested and works great). All the rest of the mechanics of these forms/queries/tables have been tested and work fine, this one bug is the only thing preventing proper function.