Custom Find Button

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to create a custom Find button that will search through four fields Person1, Person2, Person3, Person4 for a particular name. Access' standard find button searches only on one field (current). Can I do this? Alternately, is there a way to direct the find button to automatically search all fields in the subform

Thanks in advance.
 
Bob,

Private Sub cmdFind_Click()
Dim db As Database
Dim rs As DAO.Recordset
Dim sSQL As String

Set db = CurrentDb
Set rs = Me.RecordsetClone

sSQL = "Person1 LIKE '*musashi*' OR Person2 = 'miyamoto' OR " & _
"Person3 LIKE 'mifune*' OR Person4 LIKE '*toshiro'"
rs.FindFirst sSQL
If Not rs.NoMatch Then
Me.Bookmark = rs.Bookmark

'Or, you can do a filter
'DoCmd.ApplyFilter sSQL
End If

rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


Bob Mullen said:
I would like to create a custom Find button that will search through four
Click to expand...
fields Person1, Person2, Person3, Person4 for a particular name. Access'
standard find button searches only on one field (current). Can I do this?
Alternately, is there a way to direct the find button to automatically
search all fields in the subform?
 
Bob,

You didn't say what you wanted to do when you found the name.

Private Sub cmdFind_Click()
Dim db As Database
Dim rs As DAO.Recordset
Dim sSQL As String
Dim sName As String

sName = InputBox("Enter the name of the person to find.","Search for
name","")

Set db = CurrentDb
Set rs = Me.RecordsetClone

sSQL = "Person1 LIKE '*' & sName & '*' OR Person2 LIKE '*' & sName & '*'
OR " & _
"Person3 LIKE '*' & sName & '*' OR Person4 LIKE '*'
& sName & '*'

rs.FindFirst sSQL
If rs.NoMatch Then
'The name was NOT found
Else
'The name was found
End If

rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


Bob Mullen said:
Graham:

Thanks for the start. I realize now that I wasn't clear enough in
Click to expand...
explaining what I want to do. Let me try again.
I have a form frmMain on which the Find button is located. This form has a
Click to expand...
subform frmSubForm that is a datasheet view that includes (among others)
fields P1, P2, P3, P4 (all containing peoples names). I want to click the
Find button and have it prompt for the person being searched for
(preferrably first few letters of last name) and then search all four P...
fields in the subform.
 
Back
Top