Search Continuous Form

jamescullis

Registered User.
Local time
Today, 18:21
Joined
Jun 2, 2014
Messages
38
Hi,

I have a continuous form with the recordsource from a query. I have a Unbound textbox and would like to search on more than one field which is what the below code is currently doing.

other field names
- jobNumber (number field)
- suburbName (text field)
- jobInformation (text field)
- installDate (date field)

Can someone please point me in the right direction?
thanks

Code:
Private Sub txtSearch_Change()
    Dim strFilter As String
    On Error GoTo ErrHandler
    If Me.txtSearch.Text <> "" Then
        strFilter = "[subNickName] Like '*" & Me.txtSearch.Text & "*'"
        Me.Filter = strFilter
        Me.FilterOn = True
    Else
        Me.Filter = ""
        Me.FilterOn = False
    End If
    With Me.txtSearch
        .SetFocus
        .SelStart = Len(Me.txtSearch.Text)
    End With
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbExclamation
End Sub
 
your search string would be

strFilter = "[subNickName] Like '*" & Me.txtSearch.Text & "*' OR [JobNo] Like '*" & Me.txtSearch.Text & "*' OR ....

like will work with numbers as above but dates you will need to format as (say)

format(installdate,"dd/mm/yyyy") like.....

Note that by having a * at the start of the search string means that indexing will not work (assuming the fields are indexed) so if you have a large amount of data, this could be quite slow. I would suggest you leave the first * off and let the user add it if required when creating the txtsearch string

"[subNickName] Like '" & Me.txtSearch.Text & "*'
 
nailed it CJ, thanks
 
I know this is an extremely delayed reply, but could you post the solution? I am trying to work through this exact issue right now and I cant quite get it to work.
Thanks
 
Solution to what? Solution to the op’s question has been provided. You have done what is called hijacking a thread. Suggest you start your own thread and provide the detail so responders can suggest solutions
 

Users who are viewing this thread

Back
Top Bottom