Finding successive values (1 Viewer)

sbrown106

Member
Local time
Today, 17:46
Joined
Feb 6, 2021
Messages
77
Hi all,

I have a search text box that sits above a nav form, so when I enter a value in the text box and click the button the value is found in the form.
But how do I continue to search for the same value starting, again, from where the first occurrence has been found in the form.
Ive tried to put a loop in but the code keeps coming back to the first hit.

many thanks for any help please

Code:
Private Sub cmdSearchMenu_Click()

  Dim strCriteria As String
  Dim rst As DAO.Recordset
  Set rst = Me.RecordsetClone

  If IsNull(Me.txtheader_Address) Then
  MsgBox "Please enter a value"
      ElseIf Me.txtheader_Address <> 0 Then
      rst.FindFirst "fldAddress='" & Me!txtheader_Address & "'"
      If Not rst.NoMatch Then Me.Bookmark = rst.Bookmark
      Me.NavigationSubform.SetFocus
      GoToAddress
      End If
  Set rst = Nothing
End Sub

Private Sub NavigationButton9_Click()
GoToAddress
End Sub

Private Sub GoToAddress()

  'move to first record if field is blank

If IsNull(Me.txtheader_Address) Then
'do nothing
Else
    With Forms!frmNavigation.NavigationSubform.Form.RecordsetClone
        .FindFirst "fldAddress='" & Me!txtheader_Address & "'"
        Forms!frmNavigation.NavigationSubform.Form.Bookmark = .Bookmark
End With
End If

End Sub
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:46
Joined
May 7, 2009
Messages
19,169
Code:
Private Sub cmdSearchMenu_Click()
    
    'arnelgp
    Static bolNext As Boolean

    
    Dim strCriteria As String
    Dim rst As DAO.Recordset
    Set rst = Me.RecordsetClone
    
    If IsNull(Me.txtheader_Address) Then
        bolNext = False
        MsgBox "Please enter a value"
    ElseIf Me.txtheader_Address <> 0 Then
        If bolNext Then
            rst.FindNext "fldAddress='" & Me!txtheader_Address & "'"
        Else
            rst.FindFirst "fldAddress='" & Me!txtheader_Address & "'"
        End If
        If Not rst.NoMatch Then
            Me.Bookmark = rst.Bookmark
            bolNext = True
        Else
            bolNext = False
        End If
        Me.NavigationSubform.SetFocus
        GoToAddress
    End If
    Set rst = Nothing
End Sub
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:46
Joined
Feb 19, 2002
Messages
42,971
Depending on what you are doing with the records you find, it might be better to use the search box to create a filter so the form only shows the rows that match the criteria. You will also need to toggle the filter to remove it. The filter can work like a drill down if you have several fields. For example, you can filter by state and then filter by last name.

If you are willing to use DS view for the form or subform, you don't need code at all. Access has filters that work very much like Excel. My clients really like this feature. When they find what they are looking for, they double click on the id and open an edit form that allows them to modify data since I always make the search form not updateable to avoid accidents.
 

sbrown106

Member
Local time
Today, 17:46
Joined
Feb 6, 2021
Messages
77
Depending on what you are doing with the records you find, it might be better to use the search box to create a filter so the form only shows the rows that match the criteria. You will also need to toggle the filter to remove it. The filter can work like a drill down if you have several fields. For example, you can filter by state and then filter by last name.

If you are willing to use DS view for the form or subform, you don't need code at all. Access has filters that work very much like Excel. My clients really like this feature. When they find what they are looking for, they double click on the id and open an edit form that allows them to modify data since I always make the search form not updateable to avoid accidents.
Thanks for that Pat - what is DS view ?- have you got a simple example of what you mean please
 

sbrown106

Member
Local time
Today, 17:46
Joined
Feb 6, 2021
Messages
77
Code:
Private Sub cmdSearchMenu_Click()
   
    'arnelgp
    Static bolNext As Boolean

   
    Dim strCriteria As String
    Dim rst As DAO.Recordset
    Set rst = Me.RecordsetClone
   
    If IsNull(Me.txtheader_Address) Then
        bolNext = False
        MsgBox "Please enter a value"
    ElseIf Me.txtheader_Address <> 0 Then
        If bolNext Then
            rst.FindNext "fldAddress='" & Me!txtheader_Address & "'"
        Else
            rst.FindFirst "fldAddress='" & Me!txtheader_Address & "'"
        End If
        If Not rst.NoMatch Then
            Me.Bookmark = rst.Bookmark
            bolNext = True
        Else
            bolNext = False
        End If
        Me.NavigationSubform.SetFocus
        GoToAddress
    End If
    Set rst = Nothing
End Sub
Thanks Arnelgp for this its a big help
 

Users who are viewing this thread

Top Bottom