Looping Search

[srmezick]

I would like to create a search that will return to the first record found when it comes to the last one. Any ideas?

[Toyman]

I would like to create a search that will return to the first record found when it comes to the last one. Any ideas?

Not sure what you mean. Can you explain on what you are trying to achieve?

[srmezick]

Currently when users search for a record in the database (a database containing various employee information) when the search reaches the last record, it will not continue to search and return to the first record. For example if I am searching for an employee with the last name smith, and there are 7 smiths in the database, it will display the first record, then if you "re-search" it will display the second and so on. However, if the user for some reason overlooks the particular smith he/she is looking for, when he/she comes to the 7th smith, the database search bar, as well as any buttons I have created, if he/she tries to continue to search for "Smith" it will tell him/her access has completed the search and has not found any records matching the criteria. What I would LIKE to happen is when he/she gets to the 7th smith, and wishes to continue searching, the search will return to the 1st smith, then second and so on and so forth until the user does not miss the record for which they are searching.

[orange]

Still not sure I'm following the issue.
What if you used the search term to create an sql Select statement with a Where clause, and used that SQL as the recordSource of a form that you open in Datasheet.
Then you would see all of the "found" records.

As a matter of fact you could do a DCOUNT before actually doing the Search to make sure records existed and then do the search. If no matching records exist, then issue a message saying that.

Just my $.02

[srmezick]

Not sure how I could explain it any clearer than I have explained it. Your suggestion sounds pretty complicated for my expertise in the program, not sure I could acheive it with out additional help. I might stick with my original suggestion to the users, which is that they be aware of the current record before they continue searching, or they start the search over from the beggining if they do in fact miss the record for which they are searching. Im sure a few times of trying to enter a record that already exists and not being able to duplicate an existing record will enstill a bit of awareness in them. LoL

[Toyman]

Not sure how I could explain it any clearer than I have explained it. Your suggestion sounds pretty complicated for my expertise in the program, not sure I could acheive it with out additional help. I might stick with my original suggestion to the users, which is that they be aware of the current record before they continue searching, or they start the search over from the beggining if they do in fact miss the record for which they are searching. Im sure a few times of trying to enter a record that already exists and not being able to duplicate an existing record will enstill a bit of awareness in them. LoL

Are you using the filter by form function in the ribbon? If so, there is not other ways around what you are getting. I usually create my own search function. One suggestion is to create a listbox for the search result (last name). Above the list box, create a search text box. In the "On Change" event for the search test box put in the following"

Private Sub SearchTxt_Change()
Dim sSearch as String
Dim sSql as String

sSearch = Me.SearchTxt.Text 'I named the search text field as SearchTxt
sSql = "Select LastName & ', ' & FirstName AS EmpName From YourTblName "
sSql = sSql & "Where LastName Like '" & sSearch & "*'"

Me.YourListBox.Rowsource = sSql 'assigns the sql to the rowsource
Me.YourListBox.Requery
End Sub

What this does is that when the user start typing the last name, the result in the list box narrows.