Filter form with 4 criteria. (1 Viewer)

theinviter

Registered User.
Local time
Today, 16:25
Joined
Aug 14, 2014
Messages
237
Code Tags Added by UG
Please use Code Tags when posting VBA Code
Please read this for further information:-

@theinviter ----
Please feel free to Remove this Comment

;
;;
;;;
This is not the first time that you have been asked to use code tags

See Here:- https://www.access-programmers.co.uk/forums/threads/copy-record-from-from-to-a-table.314089/

And Here:-
;
;
x
x
zz
z
z
zz
z
zz
z
z
z
zz
z
zz
z
z
z
z
z
z
z
z
z
zzzz

zz
z
z
z
zzz
z
z
z
zz
zz
z
z
z
z
z
zz
zz
zzz
z
z
z
zz
z
z
z
zz
z
zz
z
zz
z
z
z
z
z
zx
x
x
x
x
x
x
x
x
x
x
x
x
x
x
x
x
x
x
x
x
x
x
xx
x
x
x
x
x
x
x
xx
x
x
x
x
x
x
x
x
x
x
x
x
x
x
x
x
x
x
;;;
;
;;;
;;;
;
;;
;;;
;;
;;;
;;;
;;
;;
;
;
;;;
;
;;;
;;;
;
;;
;;;
;;
;;;
;;;
;;
;;
;
;
;;;
;
;;;
;;;
;
;;
;;;
;;
;;;
;;;
;;
;;
;
;
;;;
;
;;;
;;;
;
;;
;;;
;;
;;;
;;;
;;
;;
;
;
;;;
;
;;;
;;;
;
;;
..
...
..
..
..
..
..
.
..
.
..
.
.
.
....
.
..
..
..
.
..
..
.
..
.
..
..
.
.
.
.
.
..
.
..
.
.
.
.
.
..
.
.
.
.
.

;;;
;;
;;;
;;;
;;
;;
;
;
;;;
;
;;;
;;;
Code:
Dears;
i have a form and there is text field "Find" where i need to filter the record based on the data entered in text field to find in 4 field in the form.

if no result with "Old CODE" then find in next field "New Code "  and other.

i tried this but not working.

On Error GoTo errhandler:
n = 1 / 0 ' cause an error
      Dim strFilter As String
    If Len(Me.FindCode & "") <> 0 Then
        strFilter = "[New Code] Like ""*" & Me.FindCode & "*"" And "
    End If

    If Len(Me.FindCode & "") <> 0 Then
        strFilter = "[Old CODE] Like ""*" & Me.FindCode & "*"" And "
    End If

    If Len(Me.FindCode & "") <> 0 Then
        strFilter = "[Alt Old Code] Like ""*" & Me.FindCode & "*"" And "
    End If

    If Len(Me.FindCode & "") <> 0 Then
        strFilter = "[Alt new Code] 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
End Sub
 
Last edited by a moderator:

June7

AWF VIP
Local time
Today, 15:25
Joined
Mar 9, 2014
Messages
5,423
What does "not working" mean - error message, wrong result, nothing happens?

Have you step debugged?

Please post lengthy code between CODE tags to retain indentation and readability.
 

theinviter

Registered User.
Local time
Today, 16:25
Joined
Aug 14, 2014
Messages
237
What does "not working" mean - error message, wrong result, nothing happens?

Have you step debugged?

Please post lengthy code between CODE tags to retain indentation and readability.
no Error message , just show me blank record.
 

SHANEMAC51

Active member
Local time
Tomorrow, 02:25
Joined
Jan 28, 2022
Messages
310
i have a form and there is text field "Find" where i need to filter the record based on the data entered in text field to find in 4 field in the form.
filtering is more convenient to do in a continuous form
1 -green field - search terms by code
2-time - you can add a filter for other main fields
3- yellow -data is protected from unintentional damage
red symbols in the first field - output to correct the selected record, you just need to CLICK

Code:
Option Compare Database
Option Explicit

Private Sub FindCode_AfterUpdate()
On Error GoTo errhandler
''n = 1 / 0 ' cause an error

Dim strFilter As String
strFilter = ""
If Len(Me.FindCode & "") <> 0 Then
strFilter = " or '' & [New Code] Like '*" & Me.FindCode & "*'"
End If

If Len(Me.FindCode & "") <> 0 Then
strFilter = strFilter & " or '' & [Old CODE] Like '*" & Me.FindCode & "*'"
End If

If Len(Me.FindCode & "") <> 0 Then
strFilter = strFilter & " or '' & [Alt Old Code] Like '*" & Me.FindCode & "*'"
End If

If Len(Me.FindCode & "") <> 0 Then
strFilter = strFilter & " or '' & [Alt new Code] Like '*" & Me.FindCode & "*'"
End If

If Len(strFilter) > 0 Then
strFilter = Mid(strFilter, 4)
With Me
.Filter = strFilter
.FilterOn = True
End With
Else
Me.Filter = ""
Me.FilterOn = False
End If

Exit Sub
errhandler:
' error handling code
Resume Next
End Sub

Private Sub ID_Click()
DoCmd.OpenForm "DATA_LIST", acNormal, , "ID=" & Me.ID, acFormEdit, acDialog
End Sub
 

Attachments

  • Screenshot_6.png
    Screenshot_6.png
    22.4 KB · Views: 257

isladogs

MVP / VIP
Local time
Today, 23:25
Joined
Jan 14, 2017
Messages
18,186
Which form event are you using - FindCode_AfterUpdate?

It really isn't clear what you are trying to do here
1. You set an error then tell the code to ignore it using resume next
2. You set 4 individual filters in turn which each overwrite the previous filter.
Only the final filter for Alt New Code is used
3. You then remove the final 5 characters (to remove the final And together with spaces) - at least that part makes sense!

What do you ACTUALLY want to happen?

EDIT: I see SHANEMACS has responded whilst I was typing and given a much improved version of your code
I'd still get rid of the pointless n=1/0 line and use proper error handling instead of Resume Next
 

theinviter

Registered User.
Local time
Today, 16:25
Joined
Aug 14, 2014
Messages
237
I need to filter the form based on code entered into the text. as i have different 4 number for the items.
so no result with first field then will search 2nd and so on for the 4 field.

is there a way to do it please guide me
 

June7

AWF VIP
Local time
Today, 15:25
Joined
Mar 9, 2014
Messages
5,423
That essentially what SHANEMACS code accomplishes. You should try it.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:25
Joined
Feb 19, 2002
Messages
42,979
Put a break on the last If statement of your code. Then print strFilter to the debug window. Seeing the contents should give you a clue what the problem is. Your code is not concatenating anything. It is overlaying what was there previously as the others have said.
@SHANEMAC51 didn't bother to indent the code so using code tags doesn't help make the code more readable. Good thing UG didn't notice:)
 
Last edited:

SHANEMAC51

Active member
Local time
Tomorrow, 02:25
Joined
Jan 28, 2022
Messages
310
@SHANEMAC51 didn't bother to indent the code so using code tags doesn't help make the code more readable. Good think UG didn't notice
I prefer to separate blocks of text with empty lines - it's much clearer

of course, I also use margins, but no more than 1-2, so that the line of code does not go beyond the boundaries of the screen
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:25
Joined
Feb 19, 2002
Messages
42,979
You can do whatever you want. Standard practice is to indent subordinate code and you are not doing that. The example you posted doesn't have any nested If's so we can't see if you indent those or squish them all to the left margin hiding the structure.
Sorry I brought it up.
 

SHANEMAC51

Active member
Local time
Tomorrow, 02:25
Joined
Jan 28, 2022
Messages
310
Standard practice is to indent subordinate code and you are not doing that.
there are no nested IF ...END IF in this code, only 4 groups of 3 lines, and this is the modified code of the author of the topic

I just fixed the errors, made the code work
 

SHANEMAC51

Active member
Local time
Tomorrow, 02:25
Joined
Jan 28, 2022
Messages
310
The example you posted doesn't have any nested If's so we can't see if you indent those or squish them all to the left margin hiding the structure.
my main rule of code formatting is that the string should be fully visible, so
  • 1 - I rarely use the line continuation character (underscore)
  • 2- I use minimum margins (no more than 1-3 2 spaces each)
  • 3- I use the short names s1 and s2 as temporary working lines when forming a long line, so as not to waste the useful width of the screen
  • 4- I use empty lines to separate blocks of text[/B]


that's probably all the rules
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:25
Joined
Feb 19, 2002
Messages
42,979
That is really OK. Most of the rest of us think thst mushing all lines to the left margin regardless of their relationship makes code harder to read. You don't. I get it. I'm sorry I brought it up.
 

SHANEMAC51

Active member
Local time
Tomorrow, 02:25
Joined
Jan 28, 2022
Messages
310
That is really OK. Most of the rest of us think thst mushing all lines to the left margin regardless of their relationship makes code harder to read. You don't. I get it. I'm sorry I brought it up.
and of course I use the GO TO operator to simplify the web from IF .... END IF by going to the end of a loop or procedure
 

theinviter

Registered User.
Local time
Today, 16:25
Joined
Aug 14, 2014
Messages
237
filtering is more convenient to do in a continuous form
1 -green field - search terms by code
2-time - you can add a filter for other main fields
3- yellow -data is protected from unintentional damage
red symbols in the first field - output to correct the selected record, you just need to CLICK

Code:
Option Compare Database
Option Explicit

Private Sub FindCode_AfterUpdate()
On Error GoTo errhandler
''n = 1 / 0 ' cause an error

Dim strFilter As String
strFilter = ""
If Len(Me.FindCode & "") <> 0 Then
strFilter = " or '' & [New Code] Like '*" & Me.FindCode & "*'"
End If

If Len(Me.FindCode & "") <> 0 Then
strFilter = strFilter & " or '' & [Old CODE] Like '*" & Me.FindCode & "*'"
End If

If Len(Me.FindCode & "") <> 0 Then
strFilter = strFilter & " or '' & [Alt Old Code] Like '*" & Me.FindCode & "*'"
End If

If Len(Me.FindCode & "") <> 0 Then
strFilter = strFilter & " or '' & [Alt new Code] Like '*" & Me.FindCode & "*'"
End If

If Len(strFilter) > 0 Then
strFilter = Mid(strFilter, 4)
With Me
.Filter = strFilter
.FilterOn = True
End With
Else
Me.Filter = ""
Me.FilterOn = False
End If

Exit Sub
errhandler:
' error handling code
Resume Next
End Sub

Private Sub ID_Click()
DoCmd.OpenForm "DATA_LIST", acNormal, , "ID=" & Me.ID, acFormEdit, acDialog
End Sub
the code work well, but there is issue that i need exact match. for example if i search for "med12" then it will show all items with "med1" and i do not want this. so how to modify the code to get exact match.
 

SHANEMAC51

Active member
Local time
Tomorrow, 02:25
Joined
Jan 28, 2022
Messages
310
the code work well, but there is issue that i need exact match. for example if i search for "med12" then it will show all items with "med1" and i do not want this. so how to modify the code to get exact match.
if you specify med12 in the search field, med1 will not be selected

if you specify med1, then mad11 and med12 and med 13 will be selected

next, a visual selection of the necessary

sometimes you can vary with asterisks
med1* all combinations starting with med1
*med1 all combinations that end in med1, in this case med11 will not be selected
you have a combination of *med1*, that is, something that has at least med1 in the middle of the value will be selected
 

Users who are viewing this thread

Top Bottom