472,144 Members | 1,949 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,144 software developers and data experts.

Access 2016 Live search Error 2185 on secondary passes only

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
Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. 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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub FN_buffer_KeyUp(KeyCode As Integer, Shift As Integer)
  2. 'This subroutine institutes a live-action query on every character entered
  3. 'this brings up on-the-fly match results as the user types each character.
  4. 'Only numerical, alphanumerical, Space, or backspace characters count for this action.
  5. 'This code is for the (unbound) [textbox]_buffer control, the 'hidden' [textbox]_input
  6. ' control is the actual (unbound) control coded as search criteria the query.
  7. 'The [textbox]_input control is only filled in (and cleared) using this code.
  8. 'Note that the delete key actions do not fire here, and therefore
  9. ' they will have unintended consequences, so should be avoided.
  10. On Error GoTo Err_Handler
  12. If (Not IsMissing(KeyCode)) Then
  13.     If (KeyCode > 47 And KeyCode < 91) Or KeyCode = vbSpace Or KeyCode = vbKeyBack Then
  14.     ' If key is numerical, alphanumerical, Space, or backspace then transfer it to live query
  15.         'the following code prevents #2185 errors with null .Text value of this control
  16. MsgBox ("FN_charCount = " & FN_charCount)
  17.         If KeyCode = vbKeyBack And FN_charCount > 0 Then
  18.             FN_charCount = FN_charCount - 1 'decrement count on backspace key (if not zero length previously)
  19. MsgBox ("Decrement fired")
  20.         Else
  21.             FN_charCount = FN_charCount + 1 'count characters in text
  22. MsgBox ("Increment fired")
  23. MsgBox ("FN_charCount = " & FN_charCount)
  24.         End If
  25.         If FN_charCount > 0 Then        'Null .Text property throws error
  26.             Me.FN_input.Value = Me.FN_buffer.Text
  27.             'move the current typed text to (hidden) query control textbox
  28. MsgBox ("Text move fired")
  29.         Else
  30. MsgBox ("About to clear search string")
  31.             Me.FN_input.Value = vbNullString
  32.             'if text calculates as Null, make input control value Null also
  33. MsgBox ("Search string should be cleared")
  34.         End If
  35. MsgBox ("Requery imminent")
  36.         Me.Requery                      'query in real-time on-the-fly
  37. MsgBox ("Requery occurred")
  38.         Me.FN_buffer.SetFocus  'return cursor to control (textbox)
  39. MsgBox ("Focus occurred")
  40.         If FN_charCount > 0 Then        'Null selection properties throws #2185 errors
  41.             Me.FN_buffer.SelLength = 0 'unselect text and...
  42.             Me.FN_buffer.SelStart = Len(FN_input)
  43.             'set cursor to end ready for the next char
  44. MsgBox ("Deselected text in textbox")
  45.         End If
  46. MsgBox ("FN_charCount = " & FN_charCount)
  47.     End If
  48. End If
  50. ExitSub:
  51.     Exit Sub
  53. Err_Handler:
  54.         MsgBox ("Error #" & Err.Number & ": " & Err.Description)
  55.     Resume ExitSub
  57. 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.
Jan 18 '19 #1
1 1314
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.
Jan 18 '19 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.