i have a subform where the record s

[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.

 

[JerichoJ]

Your approach is close, however, you are telling access to basically find the LAST match since you never let the user see any of the intermediate matches before moving to the next one. Your .findnext should not be in a loop.

What I have done in the past is provided a text box on the form where they enter their search criteria. Then, I use the Afterupdate event of that text box to .findfirst. Then, I have a button right next to the text box called "Find Next". This button when clicked, does a single .findnext. Each time they click the button it does a .findnext.

If you want to use a popup to obtain the initial search data(as in your example above), then I would do something like the following. You would have to have 2 buttons, "Find" and "Find Next". You need 1 module level variable. Also, instead of opening another recordset to find the matches, use the .RecordSetClone of your subform. For example:

'Module Level
Private stFindCriteria as String

Private Sub cmdFind_Click()
Dim stResponse as String

stResponse = InputBox("Enter Search Criteria&quot
If stResponse = "" Then
Goto Proc_Exit
Else
stFindCriteria = "[AccountTitle] Like '*"
stFindCriteria = stFindCriteria & stReponse & "*'"
End If

Me.subControl.Form.RecordsetClone.FindFirst stFindCriteria
If Me.subControl.Form.RecordsetClone.NoMatch Then
MsgBox "Search text was not found."
stFindCriteria = ""
Else
Me.subControl.SetFocus
Me.subControl.Form.Bookmark = Me.subControl.Form.RecordsetClone.Bookmark
End If

Proc_Exit:
Exit Sub

End Sub

Private Sub cmdFindNext_Click()

If stFindCriteria = "" Then
Goto Proc_Exit
Else
Me.subControl.Form.RecordsetClone.FindNext stFindCriteria
If Me.subControl.Form.RecordsetClone.NoMatch Then
MsgBox "Search text was not found."
stFindCriteria = ""
Else
Me.subControl.SetFocus
Me.subControl.Form.Bookmark = Me.subControl.Form.RecordsetClone.Bookmark
End If
End If

Proc_Exit:
Exit Sub

End Sub

This way, if the initial search finds no matches, the "Find Next" button is essentially disabled because it sees that stFindCriteria = "". This prevents runtime errors and unnecessary messages if the "Find Next" button gets clicked.

 

[gtroiano]

do i need a specific reference loaded? i receive the message "method or data member not found" for subcontrol code.

jerry.

 

[JerichoJ]

SubControl is the name of the Sub Form Control on your main form.

Replace subControl with whatever you named your subform control.

 

[gtroiano]

perfect. it worked great. thanks a lot.

jerry.