Automatically Search backwards from end of database

[dicepackage]

I have a search button that will search the database for a specific ID. The problem is it starts from the beginning and the results I am most likely to need are at the tail end. I can go to the last record and change the search direction to up and find things quicker but is there anyway to emulate this behavior automatically?

I currently am using the following code to search
Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

[dicepackage]

It wouldn't let me edit my post but alternatively what I could do is change the forms order by property to descending. The problem with this is that there is a good likelihood that the record will get locked if someone leaves the 1st record open. Is there perhaps a way to prevent someone from accidentally locking this 1st record just by leaving it open?

[orange]

If there is an index on the field in question, can you do a Seek?
This would be reading an index not records in a table.

http://support.microsoft.com/kb/98806

[SydneyGeek]

If there is an index on the field in question, can you do a Seek?
This would be reading an index not records in a table.

http://support.microsoft.com/kb/98806

Try searching a recordset with a combo box. I tried a dummy table with close to 100K records. Standard right-click and Find took a second or two. The recordset method (using a combo box) was pretty well instantaneous.
See this link: http://www.datawright.com.au/access_resources/access_combo_boxes.htm#combo_find

Denis

[dicepackage]

Thanks for the help. It took me awhile to understand what to do but this is a far better approach than I even thought was possible.

In the interest of helping anybody else my code is below. I created a button CommandSearchMember and a text box called TextSearch so that a user can type in the member and click search and go to the desired record.


Private Sub CommandSearchMember_Click()
Dim rsForm As DAO.Recordset
Set rsForm = Forms!Main.RecordsetClone
With rsForm
.FindLast "[MemberNumber] = " & Chr(34) & Me.TextSearch & Chr(34) ' Finds the most recent record of the member number. Chr(34) creates a " character
End With
If rsForm.NoMatch = True Then
MsgBox "Not found"
Else
Forms!Main.Bookmark = rsForm.Bookmark ' goes to the record
End If
End Sub


I have one little addition I can't figure out. Sometimes the most recent member record is not the one I want in which case I would like to go back to the next instance where that member appears. How would I go about implementing this?

[CreganTur]

The heart of this question goes back to good database design. Every record should have a primary key- something that makes it unique. Sometimes this is a single field- like a SSN- sometimes it's something more complex like an employee ID and a date- it really depends on what you're doing.

If your member number is not the PK for that table (which it can't be if you have multiple entries for it) then the easiest thing to do would be return all of the records for that MemberNumber, but sorted by a secondary criteria. That way the user can cycle through them to find the one they want.

Databases do not keep data stored in the order in which a record is entered (unless you are using an autonumber PK) so you cannot rely on the organization of data within a database to "search backwards from end" and expect any reliable results... especially from Access.

HTH

[dicepackage]

I have a primary key which is an autonumbered ID. Your are correct in that the MemberNumber has multiple values and is NOT the primary key. I would love it if it could return all records for that MemberNumber but I am not sure how you get to that point.

[orange]

I found this using Google. It's using ADO but it has a searchdirection parameter.


Sub Find_WithFind()
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset

Set conn = New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CurrentProject.Path & "\mydb.mdb"
Set rst = New ADODB.Recordset
rst.Open "Employees", conn, adOpenKeyset, adLockOptimistic

rst.Find "TitleOfCourtesy ='Ms.'"
Do Until rst.EOF
Debug.Print rst.Fields("LastName").Value
rst.Find "TitleOfCourtesy ='Ms.'", SkipRecords:=1, _
SearchDirection:=adSearchForward
Loop

rst.Close
Set rst = Nothing
conn.Close
Set conn = Nothing
End Sub

You might be able to work with it or do more research on it.

Good luck.

[SydneyGeek]

If you follow the link I gave you earlier, that page has an article on filtering records with a combo.

That may give you what you need.

Denis

[dicepackage]

I tried that but there are far too many records. I have a database of around 300,000 and as soon as I try to load the form with the combo box it will just crash.
I think orange is on the right track with his post but I am trying to keep this using DAO if possible.