Force Search on One Field

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).
LVL 98
Lee W, MVPTechnology and Business Process AdvisorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jack LeachProprietorCommented:
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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Lee W, MVPTechnology and Business Process AdvisorAuthor Commented:
Thanks, simple... just needed to know what to do  :-)
Helen FeddemaCommented:
My Simple Filters sample database might be helpful here:

Here is a screen shot of the form:

Simple Filters form
Helen FeddemaCommented:
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

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.