Help With Find Next Button

[gtroiano]

i have a subform where the record source is a table. i have a button on the form that prompts the user to enter search criteria and then it finds that record. my problem is that i can't get it to continue searching for other records that match the entered criteria. so basically what i want to do is have a "find next" button similar to the one on the pop up box when you hit Ctrl-F in other applications.

This is the code behind the "Find" button that i already have:

Private Sub cmdFind_Click()

Dim db As Database
Dim rst As DAO.Recordset
Dim strCriteria As String

On Error GoTo err_frame

Set db = CurrentDb
Set rst = db.OpenRecordset("totaldata", dbOpenDynaset)

strCriteria = "[AccountTitle] Like '*" & InputBox("Enter the " _
& "first few letters of the Account to find&quot & "*'"
If strCriteria = "" Then
GoTo endsub
End If

rst.FindFirst strCriteria
frm.Bookmark = rst.Bookmark
If rst.NoMatch Then
MsgBox "No record found"
Else
Do Until rst.NoMatch
Debug.Print rst!AccountTitle
rst.FindNext strCriteria
Loop
End If
rst.Close
Set db = Nothing
Set rst = Nothing
Me.Refresh

Exit_frame:
Exit Sub

err_frame:
' MsgBox Err.Number
MsgBox Err.Description
Resume Exit_frame

endsub:
End Sub


any help someone can give me would be greatly appreciated.

 

[TudorSmith]

It's just possible that your InputBox is returning *ABC* so it is looking for records with ABC in it...rather than records starting with ABC*

Try removing the first Asterisk in the InputBox call!

strCriteria = "[AccountTitle] Like '" & InputBox("Enter the first few letters of the Account to find") & "*'"

instead of your current call:

strCriteria = "[AccountTitle] Like '

*

" & InputBox("Enter the first few letters of the Account to find") & "*'"

birklea ~©¿©~ <><
&quot;If at first you succeed, try and remember how you did it!&quot;

 

[lionelbrown]

I think you need to use bookmarks and .findnext

Here's an example from Access 2000 help files that should provide you with the info you need:

-------------------------
FindFirst, FindLast, FindNext, FindPrevious Methods Example

This example uses the FindFirst, FindLast, FindNext, and FindPrevious methods to move the record pointer of a Recordset based on the supplied search string and command. The FindAny function is required for this procedure to run.

Sub FindFirstX()

Dim dbsNorthwind As Database
Dim rstCustomers As Recordset
Dim strCountry As String
Dim varBookmark As Variant
Dim strMessage As String
Dim intCommand As Integer

Set dbsNorthwind = OpenDatabase(&quot;Northwind.mdb&quot
Set rstCustomers = dbsNorthwind.OpenRecordset( _
&quot;SELECT CompanyName, City, Country &quot; & _
&quot;FROM Customers ORDER BY CompanyName&quot;, _
dbOpenSnapshot)

Do While True
' Get user input and build search string.
strCountry = _
Trim(InputBox(&quot;Enter country for search.&quot)
If strCountry = &quot;&quot; Then Exit Do
strCountry = &quot;Country = '&quot; & strCountry & &quot;'&quot;

With rstCustomers
' Populate recordset.
.MoveLast
' Find first record satisfying search string. Exit
' loop if no such record exists.
.FindFirst strCountry
If .NoMatch Then
MsgBox &quot;No records found with &quot; & _
strCountry & &quot;.&quot;
Exit Do
End If

Do While True
' Store bookmark of current record.
varBookmark = .Bookmark
' Get user choice of which method to use.
strMessage = &quot;Company: &quot; & !CompanyName & _
vbCr & &quot;Location: &quot; & !City & &quot;, &quot; & _
!Country & vbCr & vbCr & _
strCountry & vbCr & vbCr & _
&quot;[1 - FindFirst, 2 - FindLast, &quot; & _
vbCr & &quot;3 - FindNext, &quot; & _
&quot;4 - FindPrevious]&quot;
intCommand = Val(Left(InputBox(strMessage), 1))
If intCommand < 1 Or intCommand > 4 Then Exit Do

' Use selected Find method. If the Find fails,
' return to the last current record.
If FindAny(intCommand, rstCustomers, _
strCountry) = False Then
.Bookmark = varBookmark
MsgBox &quot;No match--returning to &quot; & _
&quot;current record.&quot;
End If

Loop

End With

Exit Do
Loop

rstCustomers.Close
dbsNorthwind.Close

End Sub

Function FindAny(intChoice As Integer, _
rstTemp As Recordset, _
strFind As String) As Boolean

' Use Find method based on user input.
Select Case intChoice
Case 1
rstTemp.FindFirst strFind
Case 2
rstTemp.FindLast strFind
Case 3
rstTemp.FindNext strFind
Case 4
rstTemp.FindPrevious strFind
End Select

' Set return value based on NoMatch property.
FindAny = IIf(rstTemp.NoMatch, False, True)

End Function

 

[lionelbrown]

Oops, sorry about that. I missed the .findnext in your code. I apologize if my previous post was not of much help.

 

[gtroiano]

lionel, thanks anyway.

birklea, you are right. your suggestion does help the search process a bit but i still need the ability to &quot;step&quot; through each matching record by clicking a button.

jerry

 

[TudorSmith]

OK...try putting the strCriteria bit before your record call...

strCriteria = "[AccountTitle] Like '" & InputBox("Enter the first few letters of the Account to find") & "*'"
If strCriteria = "" Then
  GoTo endsub
End If

Now use the strCriteria as part of your OpenRecordset SQL

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM totaldata WHERE [AccountTitle] LIKE '" & strCriteria & "'", dbOpenDynaset)

Now youre recordset will return all records with ABC*, and when you step through, only thos erelated records with the matching values are the correct records you'll be stepping through!

Might help! birklea ~©¿©~ <><
I know what I know...don't presume that I know what I don't! Smithism!!!