Solved Filtering form Having 2 Combobox (1 Viewer)

theinviter

Registered User.
Local time
Today, 06:51
Joined
Aug 14, 2014
Messages
237
Dear gyus,
need you help

I have a form that have2 comobox, so combo1 and combo2
one i filter the date with combo1 then need combo2 to filter current fitered data instead of all data again.

On Error GoTo errhandler:
n = 1 / 0 ' cause an error
'Move to the record selected in the control
Me.Filter = "[Clinic] Like ""*" & Me.Combo1 & "*"""
Me.FilterOn = True

Exit Sub
errhandler:
' error handling code
Resume Next


for second
On Error GoTo errhandler:
n = 1 / 0 ' cause an error
'Move to the record selected in the control
Me.Filter = "[Company] Like ""*" & Me.Combo44 & "*"""
Me.FilterOn = True

Exit Sub
errhandler:
' error handling code
Resume Next
 

Ranman256

Well-known member
Local time
Today, 09:51
Joined
Apr 9, 2015
Messages
4,339
on a filter button click, check all controls for data to apply:

Code:
'----------------
sub btnFilter_click()
'----------------
dim sWhere as string 

sWhere = "1=1"
if not IsNUll(cboST) then sWhere = sWhere & " and [State]='" & cboST & "'"        'text
if not IsNUll(txtID) then sWhere = sWhere & " and [ID]=" & txtID                 'numeric
if not IsNUll(txtDate) then sWhere = sWhere & " and [EventDate]=#" & txtDate & "#"      'dates

If sWhere = "1=1" Then
  Me.FilterOn = False
Else
  Me.Filter = sWhere
  Me.FilterOn = True
End If
end sub
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:51
Joined
May 7, 2009
Messages
19,169
you can also try this code:
Code:
Private Sub Combo1_AfterUpdate()
    Call filterThisForm
End Sub

Private Sub Combo44_AfterUpdate()
    Call filterThisForm
End Sub


Private Sub filterThisForm()
    Dim strFilter As String
    If Len(Me!Combo1 & "") <> 0 Then
        strFilter = "[Clinic] Like ""*" & Me!Combo1 & "*"" And "
    End If
    If Len(Me!Combo44 & "") <> 0 Then
        srFilter = strFilter & "[Company] Like ""*" & Me!Combo44 & "*"" 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
End Sub
 

theinviter

Registered User.
Local time
Today, 06:51
Joined
Aug 14, 2014
Messages
237
you can also try this code:
Code:
Private Sub Combo1_AfterUpdate()
    Call filterThisForm
End Sub

Private Sub Combo44_AfterUpdate()
    Call filterThisForm
End Sub


Private Sub filterThisForm()
    Dim strFilter As String
    If Len(Me!Combo1 & "") <> 0 Then
        strFilter = "[Clinic] Like ""*" & Me!Combo1 & "*"" And "
    End If
    If Len(Me!Combo44 & "") <> 0 Then
        srFilter = strFilter & "[Company] Like ""*" & Me!Combo44 & "*"" 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
End Sub
I tried but only filter the first combo but for second not working
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:51
Joined
May 7, 2009
Messages
19,169
and your second combo name is combo44?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:51
Joined
May 7, 2009
Messages
19,169
if you can see, there is misspelling on previous code:

srFilter = ...

so i fix it:
Code:
Private Sub filterThisForm()
    Dim strFilter As String
    If Len(Me!Combo1 & "") <> 0 Then
        strFilter = "[Clinic] Like ""*" & Me!Combo1 & "*"" And "
    End If
    If Len(Me!Combo44 & "") <> 0 Then
        strFilter = strFilter & "[Company] Like ""*" & Me!Combo44 & "*"" 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
End Sub
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:51
Joined
Feb 19, 2002
Messages
42,970
NEVER use Like when using a combo for selection. And especially Like "*" since that prevents the query engine from using indexes to optimize the query. If you want the criteria to be optional, use:

Me.Filter = "([Clinic] =" & Me.Combo1 & " OR " & Me.Combo1 & " Is Null) "

Also, not naming your controls with meaningful names is poor practice.

I have no idea what you are trying to do with raising an error.
 

Users who are viewing this thread

Top Bottom