Filter subform with call criteria (1 Viewer)

theinviter

Registered User.
Local time
Today, 15:39
Joined
Aug 14, 2014
Messages
237
Dear guys,

Need help,


Code Tags Added by UG
Please use Code Tags when posting VBA Code
Please read this for further information:-
Please feel free to Remove this Comment

;
;;
;;;
I draw your attention to my other posts in this thread where I point out that 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:-


;;;
;;;
;;
;;
;
;
;;;
;
;;;
;;;
;
;;
xxxXx
xxx
x
x
x
x
x
x
x
x
x
x
x
x
x
x
x
x
x
x
x
x
x
x
x
x
x
x
x
x
xx
xx
x
xx
x
x
x
x
x
xx
x
x
xx
x
x
xx
x
x
x
xx
x
x
x
xx
x
x
x
xx
x
x
x
xx
x
x
x
xx
x
x
x
x
xx
x
x
x
x
xx
x
x
x
x
x
x
x
x
x
;;;
;;
;;;
;;;
;;
;;
;
;
;;;
;
;;;
;;;
;
;;
;;;
;;
;;;
;;;
;;
;;
;
;
;;;
;
;;;
;;;
;
;;
;;;
;;
;;;
;;;
;;
;;
;
;
;;;
;
;;;
;;;
;
;;
;;;
;;
;;;
;;;
;;
;;
;
;
;;;
;
;;;
;;;
;
;;
;;;
;;
;;;
;;;
;;
;;
;
;
;;;
;
;;;
;;;
want to filter a subform with call criteria.
Subform name " MRN_Query1_subform "

i tried this code but no result , what change have to do to make it work in subform.

Code:
Private Sub filterThisForm7()
On Error GoTo errhandler:
n = 1 / 0 ' cause an error
      Dim strFilter As String
    If Len(Me!Combo59 & "") <> 0 Then
        strFilter = "[Item] Like ""*" & Me!Combo59 & "*"" And "
    End If

    If Len(Me!Combo1 & "") <> 0 Then
        strFilter = strFilter & "[Me.MRN_Query1_subform.Account_Name  ] Like ""*" & Me!Combo1 & "*"" 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


thanks
 
Last edited by a moderator:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:39
Joined
May 7, 2009
Messages
19,169
try this, if it will work:
Code:
Private Sub filterThisForm7()
    On Error GoTo errhandler
    'n = 1 / 0 ' cause an error
    Dim strFilter As String
    If Len(Me!Combo59 & "") <> 0 Then
        strFilter = "[Item] Like  '*" & Me!Combo59 & "*' And "
    End If
    
    If Len(Me!Combo1 & "") <> 0 Then
        strFilter = strFilter & "[Account_Name] Like  '*" & Me!Combo1 & "*' And "
    End If
    
    
    If Len(strFilter) <> 0 Then
        strFilter = Left$(strFilter, Len(strFilter) - 5)
        With Me![MRN_Query1_subform].Form
            .Filter = strFilter
            .FilterOn = True
        End With
    Else
        Me![MRN_Query1_subform].Form.FilterOn = False
    End If

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

theinviter

Registered User.
Local time
Today, 15:39
Joined
Aug 14, 2014
Messages
237
it did not work, i need to filter the subform bases on 2 criteria.
 

SHANEMAC51

Active member
Local time
Tomorrow, 01:39
Joined
Jan 28, 2022
Messages
310
it did not work, i need to filter the subform bases on 2 criteria
Code:
Private Sub filterThisForm7()
Dim s1, s2
    On Error GoTo errhandler
    'n = 1 / 0 ' cause an error
    s1 = ""
    If Len(Me!Combo59 & "") <> 0 Then
        s1 = s1 & " and [Item] Like  '*" & Me!Combo59 & "*'  "
    End If
    
    If Len(Me!Combo1 & "") <> 0 Then
        s1 = s1 & " and [Account_Name] Like  '*" & Me!Combo1 & "*'  "
    End If
    
    
    If Len(s1) > 5 Then
        s1 = Mid(s1, 5)
        With Me![MRN_Query1_subform].Form
            .Filter = s1
            .FilterOn = True
        End With
    Else
        Me![MRN_Query1_subform].Form.FilterOn = False
    End If

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

theinviter

Registered User.
Local time
Today, 15:39
Joined
Aug 14, 2014
Messages
237
Code:
Private Sub filterThisForm7()
Dim s1, s2
    On Error GoTo errhandler
    'n = 1 / 0 ' cause an error
    s1 = ""
    If Len(Me!Combo59 & "") <> 0 Then
        s1 = s1 & " and [Item] Like  '*" & Me!Combo59 & "*'  "
    End If
   
    If Len(Me!Combo1 & "") <> 0 Then
        s1 = s1 & " and [Account_Name] Like  '*" & Me!Combo1 & "*'  "
    End If
   
   
    If Len(s1) > 5 Then
        s1 = Mid(s1, 5)
        With Me![MRN_Query1_subform].Form
            .Filter = s1
            .FilterOn = True
        End With
    Else
        Me![MRN_Query1_subform].Form.FilterOn = False
    End If

    Exit Sub
errhandler:
    ' error handling code
    Resume Next
End Sub
not working , it filter the form but show blank record.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 22:39
Joined
Jul 9, 2003
Messages
16,245
Need help,

Yes you do!

I've mentioned this to you before in a previous post see here:-


It helps people read your code if you present it properly within code tax it's not difficult!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:39
Joined
May 7, 2009
Messages
19,169
what are the Rowsource of your Combos and their Bound Column?
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 22:39
Joined
Jul 9, 2003
Messages
16,245
And Here:-

 

SHANEMAC51

Active member
Local time
Tomorrow, 01:39
Joined
Jan 28, 2022
Messages
310
Code:
If Len(Me!Combo59 & "") <> 0 Then
        s1 = s1 & " and [Item] =" & Me!Combo59
    End If
  
    If Len(Me!Combo1 & "") <> 0 Then
        s1 = s1 & " and [Account_Name] =" & Me!Combo1
    End If
 

Users who are viewing this thread

Top Bottom