searching recordsets using FindFirst and FindNext

[Corster]

I went through a great deal of hassle to figure this out for myself, but now it is complete, I would like to share it with the world!
I know afew other people have had trouble with FindFirst and FindNext, so I hope this will be helpful!

'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

[NeoPa]

Corster,
Thank you for posting this. This code may well help some of our members if they come across similar situations :)