I have been using an Access db at work that doesn’t have this feature. It’s kind of a pain, because when you search, you sometimes get results that are one record, or no records at all. Below is code that will take you straight to the record if you type in a search term that’s specific enough.
There’s no magic shortcut here. You have to “peek” into the results to count the number of records your search will bring up, and behave accordingly.
There’s also some logic to distinguish between searches for full names and last names. It’s another way to refine the search quickly.
(BTW, you can’t just drop this code into your project. You have to study it and replicate the logic for your own system. Sorry, lazy programmers.)
Here’s some code to do that:
Private Sub ActFilter_AfterUpdate() On Error GoTo Err_ActFilter_Click Dim stDocName As String Dim stLinkCriteria As String Dim f As String Dim first, last As String Dim offset As Long Dim dbs As Database Dim rst As Recordset Dim fedid As Variant Set dbs = CurrentDb ' if they type both first and last name, try to match on both f = LTrim(RTrim([ActFilter])) offset = InStr(1, f, " ") If (offset > 0) Then first = Left(f, offset - 1) last = Mid(f, offset + 1) stLinkCriteria = "[FName] Like " & SQuote(first & "*") & _ " AND [LName] Like " & SQuote(last & "*") Else stLinkCriteria = "[LName] Like " & SQuote(f & "*") & _ " OR Email Like " & SQuote(f & "*") End If ' peek into db to see if records exist Set rst = dbs.OpenRecordset("SELECT FEDID FROM tblActivists WHERE " & stLinkCriteria) ' if no records exist, don't show results If rst.EOF Then MsgBox "Nobody matches." rst.Close Exit Sub End If ' count how many results there are. if only 1, then jump to the record rst.MoveLast If (rst.RecordCount = 1) Then fedid = rst.Fields("FEDID") rst.Close ActFilter = "" DoCmd.OpenForm "frmActivists", , , "[FEDID] = " & fedid Exit Sub End If rst.Close ' if we have more than one record, show a list of records stDocName = "frmActivList" ActFilter = "" DoCmd.OpenForm stDocName, , , stLinkCriteria Exit_ActFilter_Click: Exit Sub Err_ActFilter_Click: MsgBox Err.Description Resume Exit_ActFilter_Click End Sub