Search Box Help

[BDawg04]

I have been working on this for about 2-3 days and am frustrated with this. I have a combo box on my form that I use to search for a particular record using the 'Official Citation'. My problem is that there are several records of the same Official Citation. I have an autonumber setup as the primary key. The database works fine other than the search. Here is the code I have in the AfterUpdate event:

Private Sub Combo60_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindNext "[OffCit] = '" & Me![Combo60] & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

This does find a record that matches the criteria. However, I would like for this search to also find the next record that matches the criteria. Currently, if you press enter again it goes to next record in the table rather than the next record that matches the search criteria. I know that FindNext starts with the current record and searches until it finds a matching record or gets to the end of the table. Is there a way I can modify this code so that it will find a record matching the criteria, and then when I press enter again find the next matching record? Or, am I just out of look with this part of the search box. Thanks for any help you can provide.

 

[TheAceMan1]

How are ya BDawg04 . . . .

Have a look at the [blue]Filter[/blue] & [blue]FilterOn[/blue] properties . . .

See Ya! . . . . . .

 

[BDawg04]

Thanks for the tip.

 

[hkaing79]

Perhaps using a rank query?

http://support.microsoft.com/?kbid=208946

Then just select the record where rank=rank+1.

I modified the rank query so that it finds the next record using the following:

"SELECT a.OffCit, a.autonumber, (Select Min(autonumber) from table Where [OffCit]=[a].[OffCit] AND autonumber>[a].[autonumber]) AS Rank
FROM tblTest AS a"

Basically, I used a subquery to find all the records that have autonumber higher than the current autonumber and then finding the Min of those autonumber. I'm assuming however that you're sorting the recordset by the autonumber.