CODEWITHSUNDEEP
ms-accessvbams-access-2010
Scott Holtzman
Scott Holtzman

Reputation: 27239

Access Search All Records in a Single Form

I have a bound form in Single Form view.

I want to mimic the behavior of the Search box in the Record Navigation Buttons, but also have more control. The problem I am facing is that when my users search for a record, sometimes they accidentally start typing over a record when it's found (say after 3 or 4 letters are entered and they are still typing the search term) and the original data gets lost. Since these fields require data entry at times, locking them isn't really an option at this point.

My thought was to turn the Navigation Buttons off and mimic this feature through VBA or through a macro, and set the focus on another locked field when the value searched for is found (also with options to find the next one or to alert that something was, or was not found).

I have tried working with macros and VBA already, but cannot get it work.

I have this code (and have played with the arguments a bit), but it will only search the current record (and not all records) and either moves to the next record, or no record at all if I comment out the FindNext line.

Private Sub cmdFind_Click()

Dim sText As String
sText = Me.txtSearch 'a text box where user enters search term

DoCmd.FindRecord sText, acAnywhere, False, acDown, True, acCurrent
DoCmd.FindNext

End Sub

Obviously, I am missing something. Can someone lend some insight or an alternative approach.

Upvotes: 1

Views: 3938

Answers (2)

HansUp
HansUp

Reputation: 97101

Seems you want to find the first record where any field matches the search text.

In that case, use acAll instead of acCurrent for the sixth argument (OnlyCurrentField) to DoCmd.FindRecord.

FindRecord will be examining the form's controls, and that includes txtSearch. So clear out that text box's value immediately before calling FindRecord. Otherwise every row in the form's Record Source will be interpreted as a match. That happens because there is no way to tell FindRecord to "look for a match in every control except txtSearch."

That explanation should be clearer if you test your original code with the form in Continuous Form view (temporarily). With that code, FindRecord always put you at the first record. And then DoCmd.FindNext put you at the second record.

Anyway here is code tested in Access 2010 which does what I think you want. I used the text box's After Update event instead of a command button.

Private Sub txtSearch_AfterUpdate()
    Dim strSearch As String

    If Not IsNull(Me!txtSearch.Value) Then
        strSearch = Me!txtSearch.Value 'a text box where user enters search term
        ' txtSearch contains the search text ...
        ' so remove it to avoid matching the first (and every) record
        Me!txtSearch.Value = Null
        DoCmd.FindRecord FindWhat:=strSearch, _
            Match:=acAnywhere, _
            MatchCase:=False, _
            Search:=acDown, _
            SearchAsFormatted:=True, _
            OnlyCurrentField:=acAll ' instead of acCurrent
        ' restore search text to text box
        Me!txtSearch.Value = strSearch
    End If
End Sub

Upvotes: 2

Andre
Andre

Reputation: 27634

I think HansUp has posted the solution... but since I was coming up with a different way of searching (via RecordsetClone and FindFirst), I'll post it anyway, maybe it is of use. :)

Private Sub cmdFind_Click()

    Static RS As Recordset
    Static sText As String
    Dim curText As String
    Dim newSearch As Boolean
    Dim sCrit As String

    curText = Nz(Me.txtSearch)
    If curText = "" Then Exit Sub

    ' If text hasn't changed, use FindNext
    If curText = sText Then
        newSearch = False
    Else
        sText = curText
        newSearch = True
    End If

    ' First call?
    If RS Is Nothing Then
        Set RS = Me.RecordsetClone
        newSearch = True
    End If

    ' The field you are searching in
    sCrit = "[Text1] LIKE '*" & sText & "*'"

    If newSearch Then
        RS.FindFirst sCrit
    Else
        RS.FindNext sCrit
    End If

    If Not RS.NoMatch Then
        ' If found, navigate to the record
        Me.Bookmark = RS.Bookmark
    Else
        MsgBox "No (more) matches)", vbInformation
    End If

End Sub

Upvotes: 3

Related Questions