Search Function on Form

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

I have a form called Clients. I want to put a textbox on this form and
called it txtSearch. I will also put a command called cmdSearch next to it.
I want to enter a search criteria like my clients Last Name. When I hit the
cmdSearch the records will navigate to that record. It works OK but if there
are multiple records with the same last name, I have to keep hitting the
search button to display the next record. Is there a way to display all the
records based on the what is in the text box?
 
Hi Jeff

Instead of searching (are you using FindFirst/FindNext?) you could apply a
filter to the form. For example:

If Len(txtSearch) > 0 then
Me.Filter = "[Last Name] like '" & txtSearch & "*'"
Me.FilterOn = True
Else
Me.FilterOn = False
End If

You could put this in the AfterUpdate event of txtSearch and do away with
the command button, or even put it in the Change event and have it "filter
as you type". If you do this, then change the two instances of txtSearch
above to txtSearch.Text.
 
Jeff wrote
-----Original Message-----
I have a form called Clients. I want to put a textbox on this form and
called it txtSearch. I will also put a command called cmdSearch next to it.
I want to enter a search criteria like my clients Last Name. When I hit the
cmdSearch the records will navigate to that record. It works OK but if there
are multiple records with the same last name, I have to keep hitting the
search button to display the next record. Is there a way to display all the
records based on the what is in the text box?


.
Click to expand...

Jeff,

Here is a way to do it but it restricts the records to
the "subset" of what you selected. So I put a reset
button on it as well.

1. Make Unbound text box name FClient
2. Make a CMB with the name of FFind
3. Make a CMB with the name of FReset
4. I also put the RESET in the OnLoad event 'cause I am
paranoid.

CODE ************************************

Private Sub FReset_Click()
sqlexec = ""
sqlexec = "Select * from tblClient ORDER BY ClientName;"
Me.RecordSource = sqlexec
End Sub

Private Sub FFIND_Click()
' I am showing you how you can have mulitple boxes to
' search from in this section
'This section will do a Like "??*"
If Not IsNull(Me.FClient) Then
sqlexec = ""
sqlexec = "Select * from tblClient "
sqlexec = sqlexec & "WHERE ClientName like """ &
Me.FFName & "*"""
sqlexec = sqlexec & " ORDER BY ClientName;"
Me.RecordSource = sqlexec
FClient = Null
FSecond = Null
'This section will do a Like "*??*"
ElseIf Not IsNull(Me.FSecond) Then
sqlexec = ""
sqlexec = "Select * from tblClient "
sqlexec = sqlexec & "WHERE Second like ""*" & Me.FAName
& "*"""
sqlexec = sqlexec & " ORDER BY ClientName;"
Me.RecordSource = sqlexec
FClient = Null
FSecond = Null
FThird = Null
End If

End Sub

hth

Bobby
 
Back
Top