I know afew other people have had trouble with FindFirst and FindNext, so I hope this will be helpful!
Expand|Select|Wrap|Line Numbers
- 'This is the counter to help with FindNext
- Dim vblCnt As Integer
- Private Sub btnSearch_Click()
- On Error GoTo Err_btnSearch_Click
- Dim vblSearch As String
- Dim vblRSC As Recordset
- cbxField.SetFocus
- vblSearch = cbxField.Text
- 'Set variable to attain appropriate column referenced to text selected from combobox.
- 'This allows you to set the field the user wishes to search without using your field names.
- If cbxField.Text = "My Search Column Option 1" Then
- vblSearch = "[MyColumn1]"
- Else
- If cbxField.Text = "My Search Column Option 2" Then
- vblSearch = "[MyColumn2]"
- Else
- If cbxField.Text = "My Search Column Option 3" Then
- vblSearch = "[MyColumn3]"
- Else
- If cbxField.Text = "My Search Column Option 4" Then
- vblSearch = "[MyColumn4]"
- Else
- MsgBox ("Please select the field you wish to search!")
- Resume Exit_btnSearch_Click
- End If
- End If
- End If
- End If
- 'Set focus to referenced control
- txtSearch.SetFocus
- 'Use variable to store clone of current recordset to allow dynaset searching
- Set vblRSC = Me.RecordsetClone()
- 'If not first search of specified text, find the succeeding record (Stored in FindNext global variable)
- If vblCnt > 1 Then
- vblRSC.FindNext (vblSearch & " = '" & txtSearch.Text & "'")
- If vblRSC.NoMatch Then
- MsgBox (" There are no more instances of " & txtSearch.Text & "in" & cbxField.Text & ".")
- vblCnt = 1
- vblRSC.Close
- Exit Sub
- Else
- 'Freeze current record for Me.Refresh
- Me.Bookmark = vblRSC.Bookmark
- End If
- Else
- 'Find the first instance
- vblRSC.FindFirst (vblSearch & " = '" & txtSearch.Text & "'")
- If vblRSC.NoMatch Then
- MsgBox ("Cannot find " & txtSearch.Text & "in" & cbxField.Text & "; please redefine your search.")
- vblRSC.Close
- Exit Sub
- Else
- Me.Bookmark = vblRSC.Bookmark
- End If
- End If
- Me.Refresh
- 'Close the recordset clone
- vblRSC.Close
- If MsgBox("Is this the record you were looking for?" & Chr(13) & "Select 'Yes' to close this message or 'No' if you wish to continue searching.", vbYesNo, "Searching...") = vbNo Then
- 'Search for the next instance
- vblCnt = vblCnt + 1
- 'Search again by performing another click on the search button
- Call btnSearch_Click
- Else
- vblRSC.Close
- Exit Sub
- End If
- Exit_btnSearch_Click:
- vblRSC.Close
- Exit Sub
- Err_btnSearch_Click:
- MsgBox Err.Description
- Resume Exit_btnSearch_Click
- End Sub