Solved How to combine two option group to filter listbox (1 Viewer)

Ihk

Member
Local time
Today, 12:55
Joined
Apr 7, 2020
Messages
280
I have a form with listbox, based on query.
I have two option groups on my form, and want to combine filter result, to show filtered result same time, if I toggle between options.
  1. Option group with Frame155 filters status of Articles
  2. Option group with Frame173 filters date criteria
Because form loads with no filter, thats I am having one of the option as "All" so user will know that this is All data, without any filter.
  1. Frame155 (All, Approved, Pending)
  2. Frame173 (All, 180days, 30days)
Problem:
if one of the option is selected in Frame155 (other than default option) then
If i move between options in Frame173, then option in this filter work, but the option which is already selected in Frame155 stops working and but filters with default option "All"
Briefly: Filter options work perefectly fine independent of each other, but I want to combine those.
Here is my code for each Frame and has no difference on After update or click event.

Code:
Private Sub Frame155_AfterUpdate()
If Frame155 = 1 Then
Me.SearchResults.RowSource = "SELECT * FROM Query1 ORDER BY Query1.ID DESC"
Else
If Frame155 = 2 Then
Me.SearchResults.RowSource = "SELECT * FROM Query1 " & _
      "WHERE  ((Query1.[Status])= 'Approved') ORDER BY Query1.ID DESC"
Else
If Frame155 = 3 Then
Me.SearchResults.RowSource = "SELECT * FROM Query1 " & _
      "WHERE  ((Query1.[Status])= 'Pending') ORDER BY Query1.ID DESC"
Code:
Private Sub Frame173_AfterUpdate()
If Frame173 = 1 Then
Me.SearchResults.RowSource = "SELECT * FROM Query1 ORDER BY Query1.ID DESC"
Else
If Frame173 = 2 Then
Me.SearchResults.RowSource = "SELECT * FROM Query1 " & _
      "WHERE  (((Query1.[Dateorder])>=Date()-180))ORDER BY Query1.ID DESC"
Else
If Frame173 = 3 Then
Me.SearchResults.RowSource = "SELECT * FROM Query1 " & _
      "WHERE  (((Query1.[DateOrder])>=Date()-30))ORDER BY Query1.ID DESC"
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:55
Joined
Sep 21, 2011
Messages
13,965
Crossposting the same question in the same site, is not going to get you a quicker response, in fact probably the opposite.
 

Ihk

Member
Local time
Today, 12:55
Joined
Apr 7, 2020
Messages
280
Crossposting the same question in the same site, is not going to get you a quicker response, in fact probably the opposite.
I agree, I want to delete the previous one but could not find the way. I never want to duplicate, after search and work on it I realized - how should it be titled and written.
I think I am more precise here, with correct title of post as well as decription
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:55
Joined
Jan 23, 2006
Messages
15,352
For clarity, you want to delete
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:55
Joined
Sep 21, 2011
Messages
13,965
You need to refer to the 'other' control value in each of the AfterUpdate events
 

Ihk

Member
Local time
Today, 12:55
Joined
Apr 7, 2020
Messages
280
You need to refer to the 'other' control value in each of the AfterUpdate events
I have tried already for example something like
Code:
If Frame155 = 2  And Frame173 = 2 Then
Me.SearchResults.RowSource = "SELECT * FROM Query1 " & _
      "WHERE  ((Query1.[Status])= 'Approved')  And ((Query1.[Dateorder])>=Date()-180)) ORDER BY Query1.ID DESC"

by combining the Select statement as well as frame value with all options.
Then it does not even filter, It seems like options which are not under focus just apparently selected
 

Ihk

Member
Local time
Today, 12:55
Joined
Apr 7, 2020
Messages
280
For clarity, you want to delete
Exactly. Thank you
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:55
Joined
Sep 21, 2011
Messages
13,965
I would not do it that way.
Code:
If Me.Frame155 = 2 then 'give them some decent names :(
    If Me.Frame173 = 2 then
         requisite sql here
    ElseIf Me.Frame173 = 3 then 
                  requisite sql here
    End If
ElseIf Me.Frame155 = 3 then
etc, or use Case statements.
Also walk through the code with F8.
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:55
Joined
Sep 21, 2011
Messages
13,965
Consider setting a tempvar/global with 0,30,180 and include that sql code all the time?
 

LarryE

Active member
Local time
Today, 04:55
Joined
Aug 18, 2021
Messages
551
I have a form with listbox, based on query.
I have two option groups on my form, and want to combine filter result, to show filtered result same time, if I toggle between options.
  1. Option group with Frame155 filters status of Articles
  2. Option group with Frame173 filters date criteria
Because form loads with no filter, thats I am having one of the option as "All" so user will know that this is All data, without any filter.
  1. Frame155 (All, Approved, Pending)
  2. Frame173 (All, 180days, 30days)
Problem:
if one of the option is selected in Frame155 (other than default option) then
If i move between options in Frame173, then option in this filter work, but the option which is already selected in Frame155 stops working and but filters with default option "All"
Briefly: Filter options work perefectly fine independent of each other, but I want to combine those.
Here is my code for each Frame and has no difference on After update or click event.

Code:
Private Sub Frame155_AfterUpdate()
If Frame155 = 1 Then
Me.SearchResults.RowSource = "SELECT * FROM Query1 ORDER BY Query1.ID DESC"
Else
If Frame155 = 2 Then
Me.SearchResults.RowSource = "SELECT * FROM Query1 " & _
      "WHERE  ((Query1.[Status])= 'Approved') ORDER BY Query1.ID DESC"
Else
If Frame155 = 3 Then
Me.SearchResults.RowSource = "SELECT * FROM Query1 " & _
      "WHERE  ((Query1.[Status])= 'Pending') ORDER BY Query1.ID DESC"
Code:
Private Sub Frame173_AfterUpdate()
If Frame173 = 1 Then
Me.SearchResults.RowSource = "SELECT * FROM Query1 ORDER BY Query1.ID DESC"
Else
If Frame173 = 2 Then
Me.SearchResults.RowSource = "SELECT * FROM Query1 " & _
      "WHERE  (((Query1.[Dateorder])>=Date()-180))ORDER BY Query1.ID DESC"
Else
If Frame173 = 3 Then
Me.SearchResults.RowSource = "SELECT * FROM Query1 " & _
      "WHERE  (((Query1.[DateOrder])>=Date()-30))ORDER BY Query1.ID DESC"
 

LarryE

Active member
Local time
Today, 04:55
Joined
Aug 18, 2021
Messages
551
Try using the following code in BOTH Frame155 and Frame173 AfterUpdate events:
Code:
If Me.Frame155 = 1 Then
    Me.FilterOn=False
End If
If Me.Frame155 = 2 Then
    Me.Filter="[Status]= 'Approved'"
    Me.FilterOn=True
End If
If Me.Frame155 = 3 Then
    Me.Filter="[Status]= 'Pending'"
    Me.FilterOn=True
End If
If Me.Frame173 = 1 Then
    Me.FilterOn=False
End If
If Me.Frame173 = 2 Then
    Me.Filter="[Dateorder]>=Date()-180"
    Me.FilterOn=True     
End If
If Me.Frame173 = 3 Then
    Me.Filter="[Dateorder]>=Date()-30"
    Me.FilterOn=True     
End If
Me.Requery
 

Ihk

Member
Local time
Today, 12:55
Joined
Apr 7, 2020
Messages
280
Try using the following code in BOTH Frame155 and Frame173 AfterUpdate events:
Code:
If Me.Frame155 = 1 Then
    Me.FilterOn=False
End If
If Me.Frame155 = 2 Then
    Me.Filter="[Status]= 'Approved'"
    Me.FilterOn=True
End If
If Me.Frame155 = 3 Then
    Me.Filter="[Status]= 'Pending'"
    Me.FilterOn=True
End If
If Me.Frame173 = 1 Then
    Me.FilterOn=False
End If
If Me.Frame173 = 2 Then
    Me.Filter="[Dateorder]>=Date()-180"
    Me.FilterOn=True   
End If
If Me.Frame173 = 3 Then
    Me.Filter="[Dateorder]>=Date()-30"
    Me.FilterOn=True   
End If
Me.Requery
Thank you for this, but I want to filter listbox not the form. Also important to mention, my option groups - options are unbound, thats why i was using sql select statement from that query (source of listbox)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:55
Joined
Feb 19, 2002
Messages
42,872
Advice - ALWAYS give your controls meaningful names. Who the hell knows when reading the code what Me.Frame173 and Me.Frame155 are? And then there is the ubiquitous Query1.
 

Ihk

Member
Local time
Today, 12:55
Joined
Apr 7, 2020
Messages
280
Advice - ALWAYS give your controls meaningful names. Who the hell knows when reading the code what Me.Frame173 and Me.Frame155 are? And then there is the ubiquitous Query1.
  1. Option group with Frame155 filters status of Articles
  2. Option group with Frame173 filters date criteria
Suggestion noted, Thank you. but I mentioned this all in my 1st post (what represents what). Query1 is in the post is demo query. changed code accordingly.
 

MarkK

bit cruncher
Local time
Today, 04:55
Joined
Mar 17, 2004
Messages
8,177
I would do it like this...
Code:
Private Const SQL_BASE As String = _
    "SELECT * FROM Query1 " & _
    "{0} " & _
    "ORDER BY Query1.ID"
    
Private Const STATUS_CLAUSE As String = _
    "And Query1.Status = '{0}' "
    
Private Const DATE_CLAUSE As String = _
    "And Query1.DateOrder >= Date() - {0} "

Property Get StatusFilter() As String
'   constructs the status filter clause
    Select Case Me.fraStatus
        Case 2
            StatusFilter = VBA.Replace(STATUS_CLAUSE, "{0}", "Approved")
        Case 3
            StatusFilter = VBA.Replace(STATUS_CLAUSE, "{0}", "Pending")
    End Select
End Property

Property Get DateFilter() As String
'   constructs the date filter clause
    Select Case Me.fraDate
        Case 2
            StatusFilter = VBA.Replace(DATE_CLAUSE, "{0}", "180")
        Case 3
            StatusFilter = VBA.Replace(DATE_CLAUSE, "{0}", "30")
    End Select
End Property

Property Get FinalFilter() As String
'   constructs the filter
    Dim tmp As String
    tmp = StatusFilter & DateFilter
    If Len(tmp) Then FinalFilter = "WHERE " & Mid(tmp, 5)
End Property

Property Get FinalSQL() As String
    FinalSQL = VBA.Replace(SQL_BASE, "{0}", FinalFilter)
End Property


Private Sub Frame155_AfterUpdate()
    Me.SearchResults.RowSource = FinalSQL
End Sub

Private Sub Frame173_AfterUpdate()
    Me.SearchResults.RowSource = FinalSQL
End Sub
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:55
Joined
Feb 19, 2002
Messages
42,872
Suggestion noted, Thank you. but I mentioned this all in my 1st post (what represents what). Query1 is in the post is demo query. changed code accordingly.
Yes you did. But WE are not the people who have to maintain this database.
 

SHANEMAC51

Active member
Local time
Today, 14:55
Joined
Jan 28, 2022
Messages
310
  1. Frame155 (All, Approved, Pending)
  2. Frame173 (All, 180days, 30days)
Code:
Private Sub Frame155_AfterUpdate()
dim s1,s2
s1=""
If Frame155 = 2 Then
     s1=s1 & " and Query1.[Status]= 'Approved'"
ElseIf Frame155 = 3 Then
     s1=s1 & " and query1.[Status]= 'Pending'"
end if

If Frame173 = 2 Then
    s1=s1 & " and Query1.[Dateorder]>=Date()-180"
ElseIf Frame173 = 3 Then
     s1=s1 & " and Query1.[DateOrder]>=Date()-30
end if

if len(s1)>0 then
    s2=" where " & mid(s1,5) 
else 
    s2=""
end if

Me.SearchResults.RowSource ="SELECT * FROM Query1 " & s2 & " ORDER BY Query1.ID DESC"
Me.Requery
end sub
 
  • Love
Reactions: Ihk

Ihk

Member
Local time
Today, 12:55
Joined
Apr 7, 2020
Messages
280
Code:
Private Sub Frame155_AfterUpdate()
dim s1,s2
s1=""
If Frame155 = 2 Then
     s1=s1 & " and Query1.[Status]= 'Approved'"
ElseIf Frame155 = 3 Then
     s1=s1 & " and query1.[Status]= 'Pending'"
end if

If Frame173 = 2 Then
    s1=s1 & " and Query1.[Dateorder]>=Date()-180"
ElseIf Frame173 = 3 Then
     s1=s1 & " and Query1.[DateOrder]>=Date()-30
end if

if len(s1)>0 then
    s2=" where " & mid(s1,5)
else
    s2=""
end if

Me.SearchResults.RowSource ="SELECT * FROM Query1 " & s2 & " ORDER BY Query1.ID DESC"
Me.Requery
end sub
Fantastic !! I treid this worked perefect, This is what is I was looking for. I am very thankful. :)
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:55
Joined
Sep 21, 2011
Messages
13,965
Is there a need for a requery when you change rowsource?
 

Users who are viewing this thread

Top Bottom