Solved How to combine two option group to filter listbox

I

Ihk

Member
Local time
Today, 06:10
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"
 
Crossposting the same question in the same site, is not going to get you a quicker response, in fact probably the opposite.
 
Gasman said:
Crossposting the same question in the same site, is not going to get you a quicker response, in fact probably the opposite.
Click to expand...
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
 
For clarity, you want to delete

Form with two option groups / Frames

Form has two Frames (option groups). It is must that one the option has be selected from each frame. Individually each filter is working fine with no problem, (filter is based on sql select statement). I should mention, each option has its value. Problem: If I move between options within one...
www.access-programmers.co.uk
 
You need to refer to the 'other' control value in each of the AfterUpdate events
 
Gasman said:
You need to refer to the 'other' control value in each of the AfterUpdate events
Click to expand...
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
 
jdraw said:
For clarity, you want to delete

Form with two option groups / Frames

Form has two Frames (option groups). It is must that one the option has be selected from each frame. Individually each filter is working fine with no problem, (filter is based on sql select statement). I should mention, each option has its value. Problem: If I move between options within one...
www.access-programmers.co.uk
Click to expand...
Exactly. Thank you
 
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.
 
Consider setting a tempvar/global with 0,30,180 and include that sql code all the time?
 
Ihk said:
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"
Click to expand...
 
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
 
LarryE said:
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
Click to expand...
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 said:
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.
Click to expand...
  1. Option group with Frame155 filters status of Articles
  2. Option group with Frame173 filters date criteria
Click to expand...
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.
 
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
 
Ihk said:
  1. Frame155 (All, Approved, Pending)
  2. Frame173 (All, 180days, 30days)
Click to expand...
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
SHANEMAC51 said:
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
Click to expand...
Fantastic !! I treid this worked perefect, This is what is I was looking for. I am very thankful. :)
 
Is there a need for a requery when you change rowsource?
 
Ihk said:
No
Click to expand...
Code: 
Private Sub Frame155_AfterUpdate()
f_search
end sub
'''''''''''''''''''''''''''''''''''
Private Sub Frame173_AfterUpdate()
f_search
end sub
'''''''''''''''''''''''''''''''''''''
sub f_search()
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
    me.filter= mid(s1,5)
    me.filteron=true 
else 
    me.filteron=false
end if
end sub
 
Last edited:
SHANEMAC51 said:
Code: 
Private Sub Frame155_AfterUpdate()
f_search
end sub
'''''''''''''''''''''''''''''''''''
Private Sub Frame155_AfterUpdate()
f_search
end sub
'''''''''''''''''''''''''''''''''''''
sub f_search()
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
    me.filter= mid(s1,5)
    me.filteron=true
else
    me.filteron=false
end if
end sub
Click to expand...
Your greatness, Previous code was working perfectly fine, without any problem, data is filtered immediately.
I said No to this comment
Is there a need for a requery when you change rowsource?
Click to expand...
as I understood is , do I need an extra requery for this... so I said no
 
You must log in or register to reply here.

Similar threads

T
Solved It takes too long to download data from server
Replies
8
Views
838
GPGeorge
GPGeorge
F
Solved Mask with sub-mask filtered by 2 combined drop-down boxes and a checkbox, opening two different reports depending on the checkbox
Replies
6
Views
736
Faoineag
F
A
Solved Filtering (using VBA) a subform based on Option Buttons on main form
Replies
19
Views
1,472
CJ_London
C
Uncle Gizmo
Custom chat interface DEMO using two LLM's - Ollama Mistral and open AI GPT4
Replies
1
Views
1,555
Uncle Gizmo
Uncle Gizmo
J
Solved Query v Function
Replies
7
Views
798
John Sh
J

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom