Find Record

[hkimpact]

I have a combobox that has a query in it based on a Participant ID. I wrote this little search tool to find the Social, but if the record is not found and I backspace it generates an error. Actually let me be more clear on what I want to be done.

First I want the user to be able to type in this social security field and if the social exists in my table, then I want to tab over and it autocompletes the rest of the record for me. If the social does not exists, then I want to be able to delete what I typed and tab over to the next field.

Right now the error that I get when I backspace all the field information is a Run-Time error '3077'; Syntax error (missing operator) in expression. When I debug it goes to the RS.FindFirst "[ParticipantID] = " & Me![FindSS] part of the code.

Any help would be great. I'm sure this is an easy fix. I'll keep tinkering around with it until i figure it out.

Thanks,
Rick

' Search utility for Social Security
Private Sub FindSS_AfterUpdate()
    Dim RS As Object
    
    NameSearch.Value = Null
    
    Set RS = Me.Recordset.Clone
    RS.FindFirst "[ParticipantID] = " & Me![FindSS]
    If Not RS.EOF Then Me.Bookmark = RS.Bookmark
    
End Sub

 

[hkimpact]

Actually I fixed my own problem by creating an error handler. Here's what i did if anyone else ever has this problem.

' Search utility for Social Security
Private Sub FindSS_AfterUpdate()
On Error GoTo Err_Execute
Dim RS As Object
 
NameSearch.Value = Null
 
Set RS = Me.Recordset.Clone
RS.FindFirst "[ParticipantID] = " & Me![FindSS]
If Not RS.EOF Then Me.Bookmark = RS.Bookmark
 
On Error GoTo 0
 
Err_Execute:
UpdateTransactionDescription = False
End Sub

 

[mdlueck]

I have implemented a text field control which one may enter search text into, and it jumps to the occurrence as-you-type. If no occurrence is found, then the field turns red indicating error.

A button is supplied to jump to the next occurrence of the same search criteria. Again, if no record is found, the field turns red here as well.

Backspace eats the last character, fires the search, so would jump back to the previous search hit of the findstring - 1 char... nibbling back up the search/found tree until no characters are in the field... which lands the UI at the top record.

My code is a bit fancier as I support searching through multiple columns... based on the column which the UI is sorted by. One clicks the column header to sort by that column, or click a second time to switch that column between A/D sort order.

Private Sub fldFind_Change()
  On Error GoTo Err_fldFind_Change

  Dim strFindText As String

  'Find out if the Search Field has a value in it, else we outtahere!
  'Yes, appropriate to use this special shared code API as this time
  'we MUST read the .Text value rather than the .Value value
  strFindText = uiutils_ReadFormTextBoxText(Me.fldFind, vbNullString)
  If strFindText = vbNullString Then
    Me.fldFind.BackColor = vbWhite
    GoTo Exit_fldFind_Change
  End If

  Call FindRecord(strFindText, False)

Exit_fldFind_Change:
  Exit Sub

Err_fldFind_Change:
  Call errorhandler_MsgBox("Form: Form_parts, Subroutine: fldFind_Change()")
  Resume Exit_fldFind_Change

End Sub

Private Sub btnFindNext_Click()
  On Error GoTo Err_btnFindNext_Click

  Dim strFindText As String

  'Find out if the Search Field has a value in it, else we outtahere!
  strFindText = uiutils_ReadFormTextBox(Me.fldFind, vbNullString)
  If strFindText = vbNullString Then
    Me.fldFind.BackColor = vbWhite
    GoTo Exit_btnFindNext_Click
  End If

  Call FindRecord(strFindText, True)

Exit_btnFindNext_Click:
  Exit Sub

Err_btnFindNext_Click:
  Call errorhandler_MsgBox("Form: Form_parts, Subroutine: btnFindNext_Click()")
  Resume Exit_btnFindNext_Click

End Sub

Private Sub FindRecord(ByVal strFindText As String, ByVal flgFindNext As Boolean)
  On Error GoTo Err_FindRecord

  Dim strFindInCol
  Dim strWhereClause
  Dim daoRS As DAO.Recordset

  'Find out which column is currently being sorted by
  If InStr(strCurrentSort, " ") = 0 Then
    strFindInCol = strCurrentSort
  Else
    strFindInCol = Mid(strCurrentSort, 1, InStr(strCurrentSort, " ") - 1)
  End If

  'Determine if the col is one we support string matches for
  'If not, then exit
  If Not (strFindInCol = "partnumber") And _
     Not (strFindInCol = "title") Then
     'Blank the search field
     Me.fldFind.Value = vbNullString
     'Debug.Print "Wrong Col: " & strFindInCol
     GoTo Exit_FindRecord
  End If

  'Atttach to the Form's record set
  Set daoRS = Me.RecordsetClone
  strWhereClause = "[" & strFindInCol & "] LIKE " & Chr(34) & "*" & strFindText & "*" & Chr(34)
  If flgFindNext = True Then
    daoRS.FindNext (strWhereClause)
  Else
    daoRS.FindFirst (strWhereClause)
  End If
  'If we could find no match
  If daoRS.NoMatch Then
    Me.fldFind.BackColor = vbRed
    'Debug.Print "NoMatch"
  'Else jump to it
  Else
    Me.Bookmark = daoRS.Bookmark
    With Me.fldFind
      .BackColor = vbWhite
      .SetFocus
      .SelStart = Len(strFindText)
    End With
    'Debug.Print "Match"
  End If

Exit_FindRecord:
  Exit Sub

Err_FindRecord:
  Call errorhandler_MsgBox("Form: Form_parts, Subroutine: FindRecord()")
  Resume Exit_FindRecord

End Sub

'Generic API to safely read a Form Text Box / field control .Text attribute
Function uiutils_ReadFormTextBoxText(ByRef CtrlPointer As Object, ByVal varDefaultValue As Variant) As Variant
  On Error GoTo Err_uiutils_ReadFormTextBoxText

  If Len(Nz(CtrlPointer.Text, vbNullString)) = 0 Then
    uiutils_ReadFormTextBoxText = varDefaultValue
  Else
    uiutils_ReadFormTextBoxText = CtrlPointer.Text
  End If

Exit_uiutils_ReadFormTextBoxText:
  Exit Function

Err_uiutils_ReadFormTextBoxText:
  Call errorhandler_MsgBox("Module: modshared_uiutils, Function: uiutils_ReadFormTextBoxText()")
  uiutils_ReadFormTextBoxText = varDefaultValue
  Resume Exit_uiutils_ReadFormTextBoxText

End Function