VBA Userform Find Next button

[dorsetcream]

Hi all,
My database has multiple columns as follows
Column A: Tracking number (txttracking)
Column B: New / Resupply (txtrequest)
Column C: Resupply No. (txtresupplyno)
etc

e.g. I have 3 rows with the same tracking number
ABC123 new n/a
ABC123 resupply 1
ABC123 resupply 2


Now I have a list and I would like to create a userform with search function (search by tracking number). I was able to do the search function but it only show up the first entry with the matching criteria (i.e. only show ABC123 new, but not resupply 1 and 2).

I'm a new guy here so it would be great if someone can show me a code for the 'find next' button so that when i press 'Search' button the form will show ABC123 new, then click 'find next' to show ABC123 resupply 1, then click 'find next' again to show ABC123 resupply 2?

My current code for search button is as below:

Private Sub search_Click()


'Transferring values from the search box to the controls
Dim FindRow
Dim i As Integer
Dim cRow As String


Set FindRow = nwb.Sheets("Master Database").Range("A:A").Find(What:=cRow, LookIn:=xlValues)
Me.txtCurrentAddress = FindRow.Address
Me.txtCurrentAddress.Visible = False


'error block
On Error GoTo errHandler:




'find the row with the data
cRow = txttracking.Text




If txttracking.Text <> "" Then


Set FindRow = nwb.Sheets("Master Database").Range("A:A").Find(What:=cRow, LookIn:=xlValues)


'add the values to the userform
txttracking.Text = FindRow
txtrequest.Text = FindRow.Offset(0, 1)
txtresupplyno.Text = FindRow.Offset(0, 2)


End If


'error block
On Error GoTo 0
Exit Sub
errHandler:
MsgBox "Record Not Found. "
End Sub

Thanks a lot!

 

[dmt32]

Hi,
welcome to Forum.

Try this update to your code

Dim FirstAddress As String, strSearch As String
Dim FoundRecord As Range
Dim RecordNo As Long
Const SearchCol As Long = 1


Private Sub Search_Click()
    Dim MatchCount As Integer
    
'value to search
   strSearch = txttracking.Text
'nothing entered
   If Len(strSearch) = 0 Then Exit Sub
    
    With nwb.Sheets("Master Database")
'set start range
        If FoundRecord Is Nothing Then Set FoundRecord = .Cells(1, SearchCol)
'search range for strSearch match
        Set FoundRecord = .Columns(SearchCol).Find(strSearch, After:=FoundRecord, Lookat:=xlWhole, LookIn:=xlValues)
        If Not FoundRecord Is Nothing Then
'count number matches in range
        MatchCount = Application.CountIf(.Columns(SearchCol), strSearch)
        
            If FirstAddress <> FoundRecord.Address Then
                RecordNo = RecordNo + 1
'update caption
                Me.Caption = "Search Match " & RecordNo & " of " & MatchCount
                
'add the values to the userform
                txttracking.Text = FoundRecord.Value
                txtrequest.Text = FoundRecord.Offset(0, 1)
                txtresupplyno.Text = FoundRecord.Offset(0, 2)
                
'if more than one match in range change Search Button caption
                If MatchCount > 1 Then Me.Search.Caption = "Find Next"


'mark first matched record address
                If Len(FirstAddress) = 0 Then FirstAddress = FoundRecord.Address
                
            Else
'no more matches
                MsgBox strSearch & Chr(10) & Space(20) & Chr(10) & "End Of File", 48, "End Of File"
're-set variables
                RecordNo = 0: Set FoundRecord = Nothing: Me.Search.Caption = "Find": FirstAddress = ""
            End If
        Else
'no match found
            MsgBox strSearch & Chr(10) & " Record Not Found", 48, "Not Found"
        End If
    End With
End Sub

If more than one match exists in the search range, the Find button Caption changes to "Find Next" to allow you to continue searching.
Msgbox will be displayed at end of search

I assume your object variable nwb has been initialized elsewhere in your project

Dave