Force Search on One Field

Posted on 2014-07-15
Last Modified: 2014-07-15
I have a client with a Database that they frequently search by last name (possibly only).  Yet it has MANY fields.  Problem is they are often forgetting to change to the last name field to perform the search so when they search, they often get back no records because there is no state named "smith".  

How can I force Access (2013) to ONLY search the last name field when search is requested?

One solution I tried was to make a dedicated search box on the form itself - in theory this works - except when I type in the criteria and click my search button, it doesn't actually change the record - it's like Access is saying "Ok, I'll search for smith.  Ah, found him."  and I say, "Great, where's the info?" and access says "Oh, you want me to show you that record?  I didn't realize, sorry"

Any help appreciated (I'm not married to any particular method).
Question by:Lee W, MVP
    LVL 4

    Accepted Solution

    What search mechanism are you using?  I'd try a custom textbox with some code to set the bookmark if found:

    (untested - concept only, assuming a bound form)
    Private Sub SearchButton_Click
      With Me.Recordset
        .FindFirst "LastName = """ & Me.SearchTextbox & """"
        If .NoMatch Then
          "Not found"
          ' bookmark is auto-set when found using the Recordset.FindFirst
          ' if using RecordsetClone, set the bookmark explicitly: Me.Bookmark = .Bookmark
        End If
      End With
    End Sub

    Open in new window

    LVL 95

    Author Closing Comment

    by:Lee W, MVP
    Thanks, simple... just needed to know what to do  :-)
    LVL 31

    Expert Comment

    My Simple Filters sample database might be helpful here:

    Here is a screen shot of the form:

    Simple Filters form
    LVL 31

    Expert Comment

    Another approach is to place an unbound combo box in the form header, with a row source of an ID field and a name field (maybe just the last name, or a full name displayed last name first).  The first column width is usually set to zero.  Then, upon selecting a name, this code on the AfterUpdate event goes to the selected record:

    Private Sub cboSelect_AfterUpdate()
    'Created by Helen Feddema 29-Apr-2011
    'Last modified by Helen Feddema 29-Apr-2011
    On Error GoTo ErrorHandler
       Dim strSearch As String
       'For text IDs
       strSearch = "[______ID] = " & Chr$(39) & Me.ActiveControl.Value _
          & Chr$(39)
       'For numeric IDs
       strSearch = "[______ID] = " & Me.ActiveControl.Value
       'Find the record that matches the control
       Me.Recordset.FindFirst strSearch
       Exit Sub
       MsgBox "Error No: " & Err.Number _
          & " in " & Me.ActiveControl.Name & " procedure; " _
          & "Description: " & Err.Description
       Resume ErrorHandlerExit
    End Sub

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now