MS Access: Automatically Jumping to the Only Record that Matches

Many years back, just before web pages got popular, I remember that some programs sent you as close as possible to your desired data whenever you searched. If you typed a search term, and only one record matched, you’d be taken to that record.

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