"Global" search on form (1 Viewer)

T

tgstephens

New member
Local time
Yesterday, 20:15
Joined
Sep 15, 2010
Messages
3
I'm designing a text box and button on a form that I would like to have run a "global" filter to only show rows where the text I entered in the text box appears anywhere in the table that "feeds" the form (hope that makes sense). The below code will return results for the first two fields (RIC and P_RIC) but nothing after that. Can someone PLEASE point me in the right direction?

Thanks,
Tommy

Code: 
    Public Sub Global_Search_Click()
    DoCmd.OpenForm "Wait", acNormal, , , , , acWindowNormal
    Dim strWhere As String
    Dim lngLen As Long
    Const conJetDate = "\#mm\/dd\/yyyy\#"
    DoCmd.Hourglass (-1)
    Screen.MousePointer = 11
    DoCmd.SetWarnings False
    
    If Not IsNull(Me.txtFilterGlobal) Then
    strWhere = strWhere & "([RIC] Like ""*" & Me.txtFilterGlobal & "*"") AND "
    Screen.MousePointer = 0
    lngLen = Len(strWhere) - 5
    If lngLen > 1 Then strWhere = Left$(strWhere, lngLen)
    If lngLen > 1 Then Me.Filter = strWhere
    If lngLen > 1 Then Me.FilterOn = True
    If lngLen <= 0 Then Me.FilterOn = False
    DoCmd.Close acForm, "Wait"
    If lngLen > 1 Then Exit Sub
    Else
    If Not IsNull(Me.txtFilterGlobal) Then
    strWhere = strWhere & "([P_RIC] Like ""*" & Me.txtFilterGlobal & "*"") AND "
    Screen.MousePointer = 0
    lngLen = Len(strWhere) - 5
    If lngLen > 1 Then strWhere = Left$(strWhere, lngLen)
    If lngLen > 1 Then Me.Filter = strWhere
    If lngLen > 1 Then Me.FilterOn = True
    If lngLen <= 0 Then Me.FilterOn = False
    DoCmd.Close acForm, "Wait"
    If lngLen > 1 Then Exit Sub
    Else
    
    If Not IsNull(Me.txtFilterGlobal) Then
    strWhere = strWhere & "([ALT_RIC] Like ""*" & Me.txtFilterGlobal & "*"") AND "
    Screen.MousePointer = 0
    lngLen = Len(strWhere) - 5
    If lngLen > 1 Then strWhere = Left$(strWhere, lngLen)
    If lngLen > 1 Then Me.Filter = strWhere
    If lngLen > 1 Then Me.FilterOn = True
    If lngLen <= 0 Then Me.FilterOn = False
    DoCmd.Close acForm, "Wait"
    If lngLen > 1 Then Exit Sub
    Else
        
    If Not IsNull(Me.txtFilterGlobal) Then
    strWhere = strWhere & "([RIN] Like ""*" & Me.txtFilterGlobal & "*"") AND "
    Screen.MousePointer = 0
    lngLen = Len(strWhere) - 5
    If lngLen > 1 Then strWhere = Left$(strWhere, lngLen)
    If lngLen > 1 Then Me.Filter = strWhere
    If lngLen > 1 Then Me.FilterOn = True
    If lngLen <= 0 Then Me.FilterOn = False
    DoCmd.Close acForm, "Wait"
    If lngLen > 1 Then Exit Sub
    Else
        
    If Not IsNull(Me.txtFilterGlobal) Then
    strWhere = strWhere & "([ALT_RIN] Like ""*" & Me.txtFilterGlobal & "*"") AND "
    Screen.MousePointer = 0
    lngLen = Len(strWhere) - 5
    If lngLen > 1 Then strWhere = Left$(strWhere, lngLen)
    If lngLen > 1 Then Me.Filter = strWhere
    If lngLen > 1 Then Me.FilterOn = True
    If lngLen <= 0 Then Me.FilterOn = False
    DoCmd.Close acForm, "Wait"
    If lngLen > 1 Then Exit Sub
    Else
        
    If Not IsNull(Me.txtFilterGlobal) Then
    strWhere = strWhere & "([RIC_NOMENC] Like ""*" & Me.txtFilterGlobal & "*"") AND "
    Screen.MousePointer = 0
    lngLen = Len(strWhere) - 5
    If lngLen > 1 Then strWhere = Left$(strWhere, lngLen)
    If lngLen > 1 Then Me.Filter = strWhere
    If lngLen > 1 Then Me.FilterOn = True
    If lngLen <= 0 Then Me.FilterOn = False
    DoCmd.Close acForm, "Wait"
    If lngLen > 1 Then Exit Sub
    Else
        
    If Not IsNull(Me.txtFilterGlobal) Then
    strWhere = strWhere & "([PRID] Like ""*" & Me.txtFilterGlobal & "*"") AND "
    Screen.MousePointer = 0
    lngLen = Len(strWhere) - 5
    If lngLen > 1 Then strWhere = Left$(strWhere, lngLen)
    If lngLen > 1 Then Me.Filter = strWhere
    If lngLen > 1 Then Me.FilterOn = True
    If lngLen <= 0 Then Me.FilterOn = False
    DoCmd.Close acForm, "Wait"
    If lngLen > 1 Then Exit Sub
    Else
        
    If Not IsNull(Me.txtFilterGlobal) Then
    strWhere = strWhere & "([HSC] Like ""*" & Me.txtFilterGlobal & "*"") AND "
    Screen.MousePointer = 0
    lngLen = Len(strWhere) - 5
    If lngLen > 1 Then strWhere = Left$(strWhere, lngLen)
    If lngLen > 1 Then Me.Filter = strWhere
    If lngLen > 1 Then Me.FilterOn = True
    If lngLen <= 0 Then Me.FilterOn = False
    DoCmd.Close acForm, "Wait"
    If lngLen > 1 Then Exit Sub
    Else
        
    If Not IsNull(Me.txtFilterGlobal) Then
    strWhere = strWhere & "([EFD] Like ""*" & Me.txtFilterGlobal & "*"") AND "
    Screen.MousePointer = 0
    lngLen = Len(strWhere) - 5
    If lngLen > 1 Then strWhere = Left$(strWhere, lngLen)
    If lngLen > 1 Then Me.Filter = strWhere
    If lngLen > 1 Then Me.FilterOn = True
    If lngLen <= 0 Then Me.FilterOn = False
    DoCmd.Close acForm, "Wait"
    If lngLen > 1 Then Exit Sub
    Else
        
    If Not IsNull(Me.txtFilterGlobal) Then
    strWhere = strWhere & "([EIC] Like ""*" & Me.txtFilterGlobal & "*"") AND "
    Screen.MousePointer = 0
    lngLen = Len(strWhere) - 5
    If lngLen > 1 Then strWhere = Left$(strWhere, lngLen)
    If lngLen > 1 Then Me.Filter = strWhere
    If lngLen > 1 Then Me.FilterOn = True
    If lngLen <= 0 Then Me.FilterOn = False
    DoCmd.Close acForm, "Wait"
    If lngLen > 1 Then Exit Sub
    Else
        
    If Not IsNull(Me.txtFilterGlobal) Then
    strWhere = strWhere & "([ESD] Like ""*" & Me.txtFilterGlobal & "*"") AND "
    Screen.MousePointer = 0
    lngLen = Len(strWhere) - 5
    If lngLen > 1 Then strWhere = Left$(strWhere, lngLen)
    If lngLen > 1 Then Me.Filter = strWhere
    If lngLen > 1 Then Me.FilterOn = True
    If lngLen <= 0 Then Me.FilterOn = False
    DoCmd.Close acForm, "Wait"
    If lngLen > 1 Then Exit Sub
    Else
        
    If Not IsNull(Me.txtFilterGlobal) Then
    strWhere = strWhere & "([LOCATION] Like ""*" & Me.txtFilterGlobal & "*"") AND "
    Screen.MousePointer = 0
    lngLen = Len(strWhere) - 5
    If lngLen > 1 Then strWhere = Left$(strWhere, lngLen)
    If lngLen > 1 Then Me.Filter = strWhere
    If lngLen > 1 Then Me.FilterOn = True
    If lngLen <= 0 Then Me.FilterOn = False
    DoCmd.Close acForm, "Wait"
    If lngLen > 1 Then Exit Sub
    Else
        
    If Not IsNull(Me.txtFilterGlobal) Then
    strWhere = strWhere & "([SERIAL_NUM] Like ""*" & Me.txtFilterGlobal & "*"") AND "
    Screen.MousePointer = 0
    lngLen = Len(strWhere) - 5
    If lngLen > 1 Then strWhere = Left$(strWhere, lngLen)
    If lngLen > 1 Then Me.Filter = strWhere
    If lngLen > 1 Then Me.FilterOn = True
    If lngLen <= 0 Then Me.FilterOn = False
    DoCmd.Close acForm, "Wait"
    If lngLen > 1 Then Exit Sub
    Else
        
    If Not IsNull(Me.txtFilterGlobal) Then
    strWhere = strWhere & "([WCR_EQUIP] Like ""*" & Me.txtFilterGlobal & "*"") AND "
    Screen.MousePointer = 0
    If lngLen <= 0 Then Me.Filter = Null
    If lngLen > 1 Then strWhere = Left$(strWhere, lngLen)
    If lngLen > 1 Then Me.Filter = strWhere
    If lngLen > 1 Then Me.FilterOn = True
    If lngLen <= 0 Then Me.FilterOn = False
    DoCmd.Close acForm, "Wait"
    If lngLen > 1 Then Exit Sub
    End If
        
    DoCmd.SetWarnings True
    If Me.Recordset.RecordCount = 0 Then MsgBox "No results for this search"
    DoCmd.Close acForm, "Wait"
    DoCmd.Hourglass (-0)
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End Sub
 
Last edited by a moderator:
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 20:15
Joined
Aug 30, 2003
Messages
36,124
Welcome to the site. I moved your thread out of the introductions forum to a more appropriate one.

To your question, I suspect you want OR rather than AND between each condition. This may also help, so you can see what the finished string is:

http://www.baldyweb.com/ImmediateWindow.htm
 
ashleedawg

ashleedawg

"Here for a good time"
Local time
Yesterday, 20:15
Joined
Jun 22, 2017
Messages
154
You want to return records where the criteria shows up in any of the fields, correct? Just add all the fields together into one long string, and use your LIKE criteria on that:

Code: 
strWhere = "[RIC]&[P_RIC]&[ALT_RIC]&[RIN]&[ALT_RIN]&[RIC_NOMENC]&[PRID]&[HSC]&[EFD]&[EIC]&[ESD]&[LOCATION]&[SERIAL_NUM]&[WCR_EQUIP] Like '*" & Me.txtFilterGlobal & "*'")

Then you only need 1 set of criteria, not 14.

Let me know if that works for you.
 
You must log in or register to reply here.

Similar threads

smtazulislam
Solved Unbound Combo box need multiple value
2
Replies
20
Views
1,764
smtazulislam
smtazulislam
F
Search form
Replies
4
Views
595
bastanu
bastanu
G
Help with unbound form to filter report
Replies
9
Views
534
gojets1721
G
M
Solved Get Username in 64 bit
Replies
3
Views
665
MarkK
MarkK
oxicottin
Cant get form filter to work correctly
Replies
8
Views
198
plog
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom