Solved Filter form after update with code (1 Viewer)

[theinviter]

Dears;
i would like to ask a support.
I had a form where the user scan the bar code in text field, so after scanning need to find the code in 3 different filed and filter the form.
IF code not present in Field 1 ( Code1) then search in field 2
If code not present in Field 2 (Code2) then search in field 2
If code not present in Field 3 (Code 3) Msgbox " code not found"

I tired below but do not know how to add to 3 items.

Private Sub filterThisForm2()
On Error GoTo errhandler:
N = 1 / 0 ' cause an error
Dim strFilter As String

If Len(Me!Combo80 & "") <> 0 Then
strFilter = strFilter & "[Item] Like """ & Me!Combo80 & """ And "
End If


If Len(Me!FindCode & "") <> 0 Then
strFilter = strFilter & "

 Like """ & Me!FindCode & """ And "
    
    End If
    
  
   
    
    
    If Len(strFilter) <> 0 Then
        strFilter = Left$(strFilter, Len(strFilter) - 5)
        With Me
            .Filter = strFilter
            .FilterOn = True
           
        End With
    Else
        Me.FilterOn = False
        
    End If
    
Exit Sub
errhandler:
' error handling code
Resume Next

 

[The_Doc_Man]

Please clarify regarding what happens when the result is in more than one field. Or is it merely that you want to see if it is in ANY of the fields?

If all you want to know is whether there is a match then - let's say for simplicity it is Table1 we are searching for something from Combo1 and you have Field1, Field2, and Field3 to search, and you want to know if ANY field matches at all.

IF DCount( "*", "Table1", "('" & Me.[Combo1] & "' = [Field1] ) OR ('" & Me.[Combo1] & "' = [Field2] ) OR ('" & Me.Combo1] & "' = [Field3]" ) > 0 Then
   With Me
    .Filter = strFilter
    .FilterOn = True
  End With
Else
    Me.FilterOn = False
End If

 

[arnelgp]

you may also try:

Private Sub FilterThisForm2()
    ' on this code replace txtScanCode with the textbox Name where you scan the code
    Dim strCodeToFind As String
    Dim blnFound As Boolean
    If IsNull(Me!txtScanCode) Then
        Me.FilterOn = False
    Else
        'Code1, Code2, Code3 are the field1, field2, field3 that you want to search if txtScanCode exists.
        strCodeToFind = "Code1 = '" & Me.txtScanCode & "' Or Code2 = '" & Me.txtScanCode & "' Or Code3 = '" & Me.txtScanCode & "'"
        With Me.RecordsetClone
            .FindFirst "Code1 = '" & Me.txtScanCode & "' Or Code2 = '" & Me.txtScanCode & "' Or Code3 = '" & Me.txtScanCode & "'"
            blnFound = Not .NoMatch
        End With
        If Not blnFound Then
            Me.FilterOn = False
        Else
            Me.Filter = strCodeToFind
            Me.FilterOn = True
        End If
    End If
End Sub

 

[ruthdavida]

you may also try:

Private Sub FilterThisForm2()
    ' on this code replace txtScanCode with the textbox Name where you scan the code
    Dim strCodeToFind As String
    Dim blnFound As Boolean
    If IsNull(Me!txtScanCode) Then
        Me.FilterOn = False
    Else
        'Code1, Code2, Code3 are the field1, field2, field3 that you want to search if txtScanCode exists.
        strCodeToFind = "Code1 = '" & Me.txtScanCode & "' Or Code2 = '" & Me.txtScanCode & "' Or Code3 = '" & Me.txtScanCode & "'"
        With Me.RecordsetClone
            .FindFirst "Code1 = '" & Me.txtScanCode & "' Or Code2 = '" & Me.txtScanCode & "' Or Code3 = '" & Me.txtScanCode & "'"
            blnFound = Not .NoMatch
        End With
        If Not blnFound Then
            Me.FilterOn = False
        Else
            Me.Filter = strCodeToFind
            Me.FilterOn = True
        End If
    End If
End Sub

This can be try!

 

[theinviter]

This can be try!

thanks that work
what if want to add additional dropbox named "Item_name" so want to filter either by code or item name.

 

[theinviter]

you may also try:

Private Sub FilterThisForm2()
    ' on this code replace txtScanCode with the textbox Name where you scan the code
    Dim strCodeToFind As String
    Dim blnFound As Boolean
    If IsNull(Me!txtScanCode) Then
        Me.FilterOn = False
    Else
        'Code1, Code2, Code3 are the field1, field2, field3 that you want to search if txtScanCode exists.
        strCodeToFind = "Code1 = '" & Me.txtScanCode & "' Or Code2 = '" & Me.txtScanCode & "' Or Code3 = '" & Me.txtScanCode & "'"
        With Me.RecordsetClone
            .FindFirst "Code1 = '" & Me.txtScanCode & "' Or Code2 = '" & Me.txtScanCode & "' Or Code3 = '" & Me.txtScanCode & "'"
            blnFound = Not .NoMatch
        End With
        If Not blnFound Then
            Me.FilterOn = False
        Else
            Me.Filter = strCodeToFind
            Me.FilterOn = True
        End If
    End If
End Sub

thanks that work
what if want to add additional dropbox named "Item_name" so want to filter either by code or item name.

 

[Pat Hartman]

Of course the best solution which wasn't mentioned is to normalize the schema so you don't have three places to look

 

[arnelgp]

modify the code to this:

Private Sub FilterThisForm2()
    ' on this code replace txtScanCode with the textbox Name where you scan the code
    Dim strCodeToFind As String
    Dim blnFound As Boolean
    If IsNull(Me!txtScanCode) Then
        Me.FilterOn = False
    Else
        'Code1, Code2, Code3 are the field1, field2, field3 that you want to search if txtScanCode exists.
        strCodeToFind = "Code1 = '" & Me.txtScanCode & "' Or Code2 = '" & Me.txtScanCode & "' Or Code3 = '" & Me.txtScanCode & "' Or " & _
                                    "Item_Name = '" & Me.comboBoxNameHere & "'"
        With Me.RecordsetClone
            .FindFirst strCodeToFind
            blnFound = Not .NoMatch
        End With
        If Not blnFound Then
            Me.FilterOn = False
        Else
            Me.Filter = strCodeToFind
            Me.FilterOn = True
        End If
    End If
End Sub

 

[theinviter]

modify the code to this:

Private Sub FilterThisForm2()
    ' on this code replace txtScanCode with the textbox Name where you scan the code
    Dim strCodeToFind As String
    Dim blnFound As Boolean
    If IsNull(Me!txtScanCode) Then
        Me.FilterOn = False
    Else
        'Code1, Code2, Code3 are the field1, field2, field3 that you want to search if txtScanCode exists.
        strCodeToFind = "Code1 = '" & Me.txtScanCode & "' Or Code2 = '" & Me.txtScanCode & "' Or Code3 = '" & Me.txtScanCode & "' Or " & _
                                    "Item_Name = '" & Me.comboBoxNameHere & "'"
        With Me.RecordsetClone
            .FindFirst strCodeToFind
            blnFound = Not .NoMatch
        End With
        If Not blnFound Then
            Me.FilterOn = False
        Else
            Me.Filter = strCodeToFind
            Me.FilterOn = True
        End If
    End If
End Sub

HI, thank
it work only when i update Findcode but what about if there is 2 criteria. so mean either filter by item name or code.
File attached, where i want user to filer by medication or code.

 

[arnelgp]

here, test it. i change the code.