I am coding a search function to search for the record based on the selected field and value entered. I am using findfirst and findnext method, however, I am only able to find the first record and unable to get the find next functioning. Below is my code:
Set rs = Forms("tabel1").Recordset.Clone
sField = Switch(field = "IDs", "ID", field = "First Name", "FirstName", field = "Last Name", "LastName", field = "Email Address", "Email", True, "OTHER")
If count > 1 Then
rs.FindNext (sField & " = '" & value & "'")
If rs.NoMatch Then
MsgBox ("There are no more instances of '" & value & "' in" & sField & ".")
count = 1
rs.Close
Exit Sub
Else
' Find the next instance
Forms("tabel1").Bookmark = rs.Bookmark
End If
Else
rs.FindFirst (sField & " = '" & value & "'")
If rs.NoMatch Then
MsgBox ("Cannot find '" & value & "' in" & sField & "; Please enter a valid value.")
rs.Close
Exit Sub
Else
Forms("tabel1").Bookmark = rs.Bookmark
End If
End If
Forms("tabel1").Refresh
rs.Close
Else
MsgBox ("Please enter a valid search value")
Me.txtValue.SetFocus
End If
Else
Me.txtValue.SetFocus
Me.txtValue.Text = ""
answer = MsgBox("Please select a search field and enter a search value.", vbExclamation, "Missing Fields")
Me.ComboSearchField.SetFocus
End If
Please tell me what am I missing or doing wrongly. Sorry but I am quite rusty in VBA. Thank you.
Your not actually looping in the code provided so you will always hit FindFirst
on the first pass and never the come back around to it the FindNext
statement. Try something like this:
Do Until rs.EOF
If rs.AbsolutePosition = 1 Then
'do something to first record set
Else
'do something else...
End If
Loop
The Do Until rs.EOF
will only loop when you have a record set returned.