• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 366
  • Last Modified:

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).
0
Lee W, MVP
Asked:
Lee W, MVP
  • 2
1 Solution
 
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"
    Else
      ' 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



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

http://www.helenfeddema.com/Files/accarch160.zip

Here is a screen shot of the form:

Simple Filters form
0
 
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

ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in " & Me.ActiveControl.Name & " procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now