Flexible filter

  • Thread starter Thread starter Frank Situmorang
  • Start date Start date
F

Frank Situmorang

Hello experts,

I am very thankful to all of you in this news group that already helped me.
All I can say is I can make our church membership database is just by your
help. One thing that I still need your help on how to make the flexible
filter, I do not know how to say it in English, maybe something that is
versatile filter.

I want the filter to be able to have the following:

1. To choose filter from any of the fields ( I will label this it " Choose
Filter"), then I will make criteria such as if the field is date, it will
have " Start date"anad " End date" , if it is a text field it will have
option for any...or contain..what

2. The above will show what is the current filter choice is set to (default
settings)
and below this there is a button saying " Remove Selected Filter"

3. The explanation on how to do the filter choice.

Thanks for any idea on this.

Frank
 
This will take a bit of VBA, but it is not too terrible hard.

The concept is that you set the form's filter based on values selected from
combo boxes or text boxes.

Here is the routine that sets the filters. You put this in the After Update
event box for each control's property sheet that will be included in the
filger:

=SetFilters()

'---------------------------------------------------------------------------------------
' Procedure : SetFilters
' DateTime : 2/10/2008 14:15
' Author : Dave Hargis
' Purpose : Sets the form filter for each combo search box
'---------------------------------------------------------------------------------------
'
Private Function SetFilters()
Dim strfilter As String 'Used by Form Filtering procedures

On Error GoTo SetFilters_Error

With Me

'Build The Filter String
If .cboPriority <> "(All)" Then
strfilter = "[InitPriority] = " & .cboPriority
End If

If .cboOrigdate <> "(All)" Then
strfilter = AddAnd(strfilter)
strfilter = strfilter & "Format([OrigReleaseTarget],
""yyyy-mm"") = """ & _
.cboOrigdate & """"
End If

If .cboCurrDate <> "(All)" Then
strfilter = AddAnd(strfilter)
strfilter = strfilter & "[CurrentReleaseTarget] = " & .cboCurrDate
End If

If .cboInitStatus <> 0 Then
strfilter = AddAnd(strfilter)
strfilter = strfilter & "[InitStatus] = " & .cboInitStatus
End If

If Not IsNull(.txtDescrSearch) Then
strfilter = AddAnd(strfilter)
strfilter = strfilter & "[InitShortDescr] Like ""*" &
Me.txtDescrSearch & "*"""
End If

.subInitiative.Form.Filter = strfilter
.subInitiative.Form.FilterOn = True

End With 'Me


SetFilters_Exit:

Exit Function
On Error GoTo 0

SetFilters_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure SetFilters of VBA Document Form_frmStartForm"
GoTo SetFilters_Exit

End Function


Here is the code that clears the filters.

Private Sub cmdClearFilters_Click()
With Me
.cboPriority = "(All)"
.cboOrigdate = "(All)"
.cboCurrDate = "(All)"
.cboInitStatus = 0
.txtDescrSearch = Null
.subInitiative.Form.FilterOn = False
.subInitiative.Form.Requery
End With

End Sub

Of course, the code is specific to one of my forms, but this will give you
the concept.
 
Frank said:
I am very thankful to all of you in this news group that already helped me.
All I can say is I can make our church membership database is just by your
help. One thing that I still need your help on how to make the flexible
filter, I do not know how to say it in English, maybe something that is
versatile filter.

I want the filter to be able to have the following:

1. To choose filter from any of the fields ( I will label this it " Choose
Filter"), then I will make criteria such as if the field is date, it will
have " Start date"anad " End date" , if it is a text field it will have
option for any...or contain..what

2. The above will show what is the current filter choice is set to (default
settings)
and below this there is a button saying " Remove Selected Filter"

3. The explanation on how to do the filter choice.
Click to expand...


That's an awful lot to cover in a post. The concepts are
all included in Allen Browne's page at
http://allenbrowne.com/ser-62.html

Study that and try to get some of your features working.
Then post back with a specific question if you have a
specific problem.
 
Thanks Klatuu for your response. I appreciate if you could inform me your
website where can I see your form, so that I can see if it is similar with
what I need.

Thanks in advance

Frank

Klatuu said:
This will take a bit of VBA, but it is not too terrible hard.

The concept is that you set the form's filter based on values selected from
combo boxes or text boxes.

Here is the routine that sets the filters. You put this in the After Update
event box for each control's property sheet that will be included in the
filger:

=SetFilters()

'---------------------------------------------------------------------------------------
' Procedure : SetFilters
' DateTime : 2/10/2008 14:15
' Author : Dave Hargis
' Purpose : Sets the form filter for each combo search box
'---------------------------------------------------------------------------------------
'
Private Function SetFilters()
Dim strfilter As String 'Used by Form Filtering procedures

On Error GoTo SetFilters_Error

With Me

'Build The Filter String
If .cboPriority <> "(All)" Then
strfilter = "[InitPriority] = " & .cboPriority
End If

If .cboOrigdate <> "(All)" Then
strfilter = AddAnd(strfilter)
strfilter = strfilter & "Format([OrigReleaseTarget],
""yyyy-mm"") = """ & _
.cboOrigdate & """"
End If

If .cboCurrDate <> "(All)" Then
strfilter = AddAnd(strfilter)
strfilter = strfilter & "[CurrentReleaseTarget] = " & .cboCurrDate
End If

If .cboInitStatus <> 0 Then
strfilter = AddAnd(strfilter)
strfilter = strfilter & "[InitStatus] = " & .cboInitStatus
End If

If Not IsNull(.txtDescrSearch) Then
strfilter = AddAnd(strfilter)
strfilter = strfilter & "[InitShortDescr] Like ""*" &
Me.txtDescrSearch & "*"""
End If

.subInitiative.Form.Filter = strfilter
.subInitiative.Form.FilterOn = True

End With 'Me


SetFilters_Exit:

Exit Function
On Error GoTo 0

SetFilters_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure SetFilters of VBA Document Form_frmStartForm"
GoTo SetFilters_Exit

End Function


Here is the code that clears the filters.

Private Sub cmdClearFilters_Click()
With Me
.cboPriority = "(All)"
.cboOrigdate = "(All)"
.cboCurrDate = "(All)"
.cboInitStatus = 0
.txtDescrSearch = Null
.subInitiative.Form.FilterOn = False
.subInitiative.Form.Requery
End With

End Sub

Of course, the code is specific to one of my forms, but this will give you
the concept.

--
Dave Hargis, Microsoft Access MVP


Frank Situmorang said:
Hello experts,

I am very thankful to all of you in this news group that already helped me.
All I can say is I can make our church membership database is just by your
help. One thing that I still need your help on how to make the flexible
filter, I do not know how to say it in English, maybe something that is
versatile filter.

I want the filter to be able to have the following:

1. To choose filter from any of the fields ( I will label this it " Choose
Filter"), then I will make criteria such as if the field is date, it will
have " Start date"anad " End date" , if it is a text field it will have
option for any...or contain..what

2. The above will show what is the current filter choice is set to (default
settings)
and below this there is a button saying " Remove Selected Filter"

3. The explanation on how to do the filter choice.

Thanks for any idea on this.

Frank
Click to expand...
Click to expand...
 
Marshall,

Thanks for informing me Allen's website. Since I will have many combo box as
a tools to search, could you teach me , how to make the 2 buttons for
activate filter and clear filter can alsways be staying in one place, when we
move horizontal scroll to go to the right side for example.


Thanks in advance

Frank
 
Frank said:
Thanks for informing me Allen's website. Since I will have many combo box as
a tools to search, could you teach me , how to make the 2 buttons for
activate filter and clear filter can alsways be staying in one place, when we
move horizontal scroll to go to the right side for example.
Click to expand...
Sorry, but I do not understand what you are trying to do.
Scrolling moves the things on the form and you want some of
them to not move?? If that can be done, then I don't know
how to do it.

It would be better to arrange the form so it does not need
to be scrolled.
 
Back
Top