Apply a filter on a form

[Jac Tremblay]

Hi,
I have a beneficiary form with a Search button on it. When the user clicks
on it, a Search form appears where one can specify a code or a
LastName/FirstName pair (either one or both can be empty).
From those entries, I build a filter which I want to apply to the
beneficiary form.
This part works fine thanks to the many instructive posts I found on this
newsgroup.
The part that does not work is the second ApplyFilter (I first clear the
filter and then try to apply the new one).
Here is some parts of the code I use:
' **********************************
' Remove the old filter (works fine).
strFiltreNul = "(NO_DOSS_IR = ""*"")"
DoCmd.ApplyFilter FilterName:=strFiltreNul

' Apply the new filter (doesn't work).
Form_frmBénéfDeGauche.SetFocus
Form_frmBénéfDeGauche.NO_DOSS_IR.SetFocus
DoCmd.ApplyFilter FilterName:=strFiltre
Form_frmBénéfDeGauche.FilterOn = True
' **********************************
Note: Object and variable names are in French.
Because this part doesn't work, I wrote this code to circumvent the problem
in the mean time. It's a macramé and inefficient way to do things but at
least, I can go on with the development.
' **********************************
' Close the form and reopen it with the filter.
DoCmd.Close acForm, strNomFormulaire, acSaveYes
DoCmd.OpenForm FormName:=strNomFormulaire, View:=acNormal, _
WhereCondition:=strFiltre
' **********************************
Note: strNomFormulaire = Form_frmBénéfDeGauche
Anyone has some idea of what is wrong in my application?

Thanks in advance.

 

[Allen Browne]

Here's a simple solution. Add a pair of unbound text boxes to the (Form
Header?) of the form that actually contains this data. Name the text boxes:
txtSearchLastName
txtSearchFirstName
Whatever the user enters in the first will be applied to the LastName field,
and the second one will be applied to the FirstName field. Substitute your
own field names.

The code is designed so you can easily add more search boxes if you wish.
Each one assigns the filter string, and tacks an " AND " on the end ready
for the next one. The final " AND " is chopped off at the end.

Dim strWhere As String
Dim lngLen As Long

'Save first
If Me.Dirty Then Me.Dirty = False

If Not IsNull(Me.txtSearchLastName) Then
strWhere = strWhere & "([LastName] = """ & _
Me.txtSearchLastname & """) AND "
End If

If Not IsNull(Me.txtSearchFirstName) Then
strWhere = strWhere & "([Firstname] = """ & _
Me.txtSearchFirstName & """) AND "
End If

'etc for other search boxes if you want.

'Now chop off the trailing " AND ".
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'No criteria: show all.
Me.FilterOn = False
Else
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
End If

 

[Jac Tremblay]

Hi Allen,
Thank you for your answer. I appreciate it.
But I do not want to add unbound fields in my ben form's header (or
somewhere else) because I already have a Search button (in the header, along
with other ones) that brings up a Search screen where my unbound fields are
laid. In that Search screen, I catch the user's data and work from there.
I modified the whole procedure to show you what I want and what does not
work. The commented lines are tests that do not work. Here is the code:
' **********************************************************
Private Sub cmdSearch_Click()
Dim strFilter As String
Dim strNoFilter As String
' Close the Search Form (the data entered has
' already been transfered in variables along with
' the form name that called the procedure).
DoCmd.Close
If lngBenId <> 0 Then
strFilter = "([BenId] = " & lngBenId & ")"
Else
' It's either the name and/or the last name.
If Nz(strLastName, "") = "" Then
strFilter = "([BenLastName] LIKE ""*"" And "
Else
strFilter = "([BenLastName] = '" & strLastName & "' And "
End If
If Nz(strFisrtName, "") = "" Then
strFilter = strFilter & _
"[BenFirstName] LIKE ""*"")"
Else
strFilter = strFilter & _
"[BenFirstName] = '" & strFisrtName & "')"
End If
End If
strNoFilter = "([BenId] = ""*"")"
DoCmd.ApplyFilter FilterName:=strNoFilter
' This last line of code works fine but the next one does not. Why???
' DoCmd.ApplyFilter FilterName:=strFilter

' I tried this.
' Form_frmBen.SetFocus
' Form_frmBen.BenId.SetFocus
' DoCmd.ApplyFilter FilterName:=strFilter
' Form_frmBen.FilterOn = True

' This is my temporary solution and I will leave it like that until I find
the problem.
' Close and reopen the form with the filter...
' ...macramé way of doing things but at least, it works.
DoCmd.Close acForm, strFormName, acSaveYes
DoCmd.OpenForm FormName:=strFormName, View:=acNormal, _
WhereCondition:=strFilter

' I tried that too...
' Forms!strFormName.SetFocus
' Forms!strFormName.BenId.SetFocus
'
' strNoFilter = "([BenId] = ""*"")"
' DoCmd.ApplyFilter FilterName:=strNoFilter
'
' DoCmd.ApplyFilter FilterName:=strFilter
End Sub
' **********************************************************
As you can see, I already have in my code a good part of what you mention in
your answer.
There are many questions that remain unanswered:
I cannot use the "Me." because I get the message: The object is either
closed or not available anymore...
If I do not close the Search form, it remains opened and I do not want that.
What can I do?

Thank you again for your help. It keeps me awake.
--
Jac Tremblay

Here's a simple solution. Add a pair of unbound text boxes to the (Form
Header?) of the form that actually contains this data. Name the text boxes:
txtSearchLastName
txtSearchFirstName
Whatever the user enters in the first will be applied to the LastName field,
and the second one will be applied to the FirstName field. Substitute your
own field names.

The code is designed so you can easily add more search boxes if you wish.
Each one assigns the filter string, and tacks an " AND " on the end ready
for the next one. The final " AND " is chopped off at the end.

Dim strWhere As String
Dim lngLen As Long

'Save first
If Me.Dirty Then Me.Dirty = False

If Not IsNull(Me.txtSearchLastName) Then
strWhere = strWhere & "([LastName] = """ & _
Me.txtSearchLastname & """) AND "
End If

If Not IsNull(Me.txtSearchFirstName) Then
strWhere = strWhere & "([Firstname] = """ & _
Me.txtSearchFirstName & """) AND "
End If

'etc for other search boxes if you want.

'Now chop off the trailing " AND ".
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'No criteria: show all.
Me.FilterOn = False
Else
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,
I have a beneficiary form with a Search button on it. When the user clicks
on it, a Search form appears where one can specify a code or a
LastName/FirstName pair (either one or both can be empty).
From those entries, I build a filter which I want to apply to the
beneficiary form.
This part works fine thanks to the many instructive posts I found on this
newsgroup.
The part that does not work is the second ApplyFilter (I first clear the
filter and then try to apply the new one).
Here is some parts of the code I use:
' **********************************
' Remove the old filter (works fine).
strFiltreNul = "(NO_DOSS_IR = ""*"")"
DoCmd.ApplyFilter FilterName:=strFiltreNul

' Apply the new filter (doesn't work).
Form_frmBénéfDeGauche.SetFocus
Form_frmBénéfDeGauche.NO_DOSS_IR.SetFocus
DoCmd.ApplyFilter FilterName:=strFiltre
Form_frmBénéfDeGauche.FilterOn = True
' **********************************
Note: Object and variable names are in French.
Because this part doesn't work, I wrote this code to circumvent the
problem
in the mean time. It's a macramé and inefficient way to do things but at
least, I can go on with the development.
' **********************************
' Close the form and reopen it with the filter.
DoCmd.Close acForm, strNomFormulaire, acSaveYes
DoCmd.OpenForm FormName:=strNomFormulaire, View:=acNormal, _
WhereCondition:=strFiltre
' **********************************
Note: strNomFormulaire = Form_frmBénéfDeGauche
Anyone has some idea of what is wrong in my application?

Thanks in advance.

 

[Allen Browne]

Okay, at the top of your procedure, add these 2 lines:
Dim frm As Form
Set frm = Forms("NameOfTheFormToApplyTheFilterToHere")

Now, everywhere I suggested:
Me
replace it with:
frm
and it will refer to the other form.

I did not go through your code in detail, but the DoCmd.ApplyFilter will
operate on the active form, which is your search form, I think - not the
result you want. Setting the Filter property of the specific form will work,
e.g.:
frm.Filter = strWhere
frm.FilterOn = True

The other issue with your ApplyFilter was that you were trying to use the
(useless) FilterName argument. You built a string that should be used for
the WhereCondition argument.

The code I suggested only bothers to filter the fields where you actually
entered something. That should be more efficient, but more importantly it
also retrieves the records the field is Null. If you enter Criteria of:
Like "*"
it returns only the records that actually contain a value, i.e. you exclude
the nulls. For more info on this, see:
Common errors with Null
at:
http://allenbrowne.com/casu-12.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,
Thank you for your answer. I appreciate it.
But I do not want to add unbound fields in my ben form's header (or
somewhere else) because I already have a Search button (in the header,
along
with other ones) that brings up a Search screen where my unbound fields
are
laid. In that Search screen, I catch the user's data and work from there.
I modified the whole procedure to show you what I want and what does not
work. The commented lines are tests that do not work. Here is the code:
' **********************************************************
Private Sub cmdSearch_Click()
Dim strFilter As String
Dim strNoFilter As String
' Close the Search Form (the data entered has
' already been transfered in variables along with
' the form name that called the procedure).
DoCmd.Close
If lngBenId <> 0 Then
strFilter = "([BenId] = " & lngBenId & ")"
Else
' It's either the name and/or the last name.
If Nz(strLastName, "") = "" Then
strFilter = "([BenLastName] LIKE ""*"" And "
Else
strFilter = "([BenLastName] = '" & strLastName & "' And "
End If
If Nz(strFisrtName, "") = "" Then
strFilter = strFilter & _
"[BenFirstName] LIKE ""*"")"
Else
strFilter = strFilter & _
"[BenFirstName] = '" & strFisrtName & "')"
End If
End If
strNoFilter = "([BenId] = ""*"")"
DoCmd.ApplyFilter FilterName:=strNoFilter
' This last line of code works fine but the next one does not. Why???
' DoCmd.ApplyFilter FilterName:=strFilter

' I tried this.
' Form_frmBen.SetFocus
' Form_frmBen.BenId.SetFocus
' DoCmd.ApplyFilter FilterName:=strFilter
' Form_frmBen.FilterOn = True

' This is my temporary solution and I will leave it like that until I find
the problem.
' Close and reopen the form with the filter...
' ...macramé way of doing things but at least, it works.
DoCmd.Close acForm, strFormName, acSaveYes
DoCmd.OpenForm FormName:=strFormName, View:=acNormal, _
WhereCondition:=strFilter

' I tried that too...
' Forms!strFormName.SetFocus
' Forms!strFormName.BenId.SetFocus
'
' strNoFilter = "([BenId] = ""*"")"
' DoCmd.ApplyFilter FilterName:=strNoFilter
'
' DoCmd.ApplyFilter FilterName:=strFilter
End Sub
' **********************************************************
As you can see, I already have in my code a good part of what you mention
in
your answer.
There are many questions that remain unanswered:
I cannot use the "Me." because I get the message: The object is either
closed or not available anymore...
If I do not close the Search form, it remains opened and I do not want
that.
What can I do?

Thank you again for your help. It keeps me awake.
--
Jac Tremblay

Here's a simple solution. Add a pair of unbound text boxes to the (Form
Header?) of the form that actually contains this data. Name the text
boxes:
txtSearchLastName
txtSearchFirstName
Whatever the user enters in the first will be applied to the LastName
field,
and the second one will be applied to the FirstName field. Substitute
your
own field names.

The code is designed so you can easily add more search boxes if you wish.
Each one assigns the filter string, and tacks an " AND " on the end ready
for the next one. The final " AND " is chopped off at the end.

Dim strWhere As String
Dim lngLen As Long

'Save first
If Me.Dirty Then Me.Dirty = False

If Not IsNull(Me.txtSearchLastName) Then
strWhere = strWhere & "([LastName] = """ & _
Me.txtSearchLastname & """) AND "
End If

If Not IsNull(Me.txtSearchFirstName) Then
strWhere = strWhere & "([Firstname] = """ & _
Me.txtSearchFirstName & """) AND "
End If

'etc for other search boxes if you want.

'Now chop off the trailing " AND ".
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'No criteria: show all.
Me.FilterOn = False
Else
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,
I have a beneficiary form with a Search button on it. When the user
clicks
on it, a Search form appears where one can specify a code or a
LastName/FirstName pair (either one or both can be empty).
From those entries, I build a filter which I want to apply to the
beneficiary form.
This part works fine thanks to the many instructive posts I found on
this
newsgroup.
The part that does not work is the second ApplyFilter (I first clear
the
filter and then try to apply the new one).
Here is some parts of the code I use:
' **********************************
' Remove the old filter (works fine).
strFiltreNul = "(NO_DOSS_IR = ""*"")"
DoCmd.ApplyFilter FilterName:=strFiltreNul

' Apply the new filter (doesn't work).
Form_frmBénéfDeGauche.SetFocus
Form_frmBénéfDeGauche.NO_DOSS_IR.SetFocus
DoCmd.ApplyFilter FilterName:=strFiltre
Form_frmBénéfDeGauche.FilterOn = True
' **********************************
Note: Object and variable names are in French.
Because this part doesn't work, I wrote this code to circumvent the
problem
in the mean time. It's a macramé and inefficient way to do things but
at
least, I can go on with the development.
' **********************************
' Close the form and reopen it with the filter.
DoCmd.Close acForm, strNomFormulaire, acSaveYes
DoCmd.OpenForm FormName:=strNomFormulaire, View:=acNormal, _
WhereCondition:=strFiltre
' **********************************
Note: strNomFormulaire = Form_frmBénéfDeGauche
Anyone has some idea of what is wrong in my application?

Thanks in advance.

 

[Jac Tremblay]

Hi again Allen,
That made it. It works fine now. Your comment is very pertinent and I really
appreciate the issue. It simplifies the whole process.
I checked your web site and found it very interesting. Many good points are
clarified. You've done a very good job.
Many thanks again. I may now go to bed and sleep in peace.
--
Jac Tremblay

Okay, at the top of your procedure, add these 2 lines:
Dim frm As Form
Set frm = Forms("NameOfTheFormToApplyTheFilterToHere")

Now, everywhere I suggested:
Me
replace it with:
frm
and it will refer to the other form.

I did not go through your code in detail, but the DoCmd.ApplyFilter will
operate on the active form, which is your search form, I think - not the
result you want. Setting the Filter property of the specific form will work,
e.g.:
frm.Filter = strWhere
frm.FilterOn = True

The other issue with your ApplyFilter was that you were trying to use the
(useless) FilterName argument. You built a string that should be used for
the WhereCondition argument.

The code I suggested only bothers to filter the fields where you actually
entered something. That should be more efficient, but more importantly it
also retrieves the records the field is Null. If you enter Criteria of:
Like "*"
it returns only the records that actually contain a value, i.e. you exclude
the nulls. For more info on this, see:
Common errors with Null
at:
http://allenbrowne.com/casu-12.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,
Thank you for your answer. I appreciate it.
But I do not want to add unbound fields in my ben form's header (or
somewhere else) because I already have a Search button (in the header,
along
with other ones) that brings up a Search screen where my unbound fields
are
laid. In that Search screen, I catch the user's data and work from there.
I modified the whole procedure to show you what I want and what does not
work. The commented lines are tests that do not work. Here is the code:
' **********************************************************
Private Sub cmdSearch_Click()
Dim strFilter As String
Dim strNoFilter As String
' Close the Search Form (the data entered has
' already been transfered in variables along with
' the form name that called the procedure).
DoCmd.Close
If lngBenId <> 0 Then
strFilter = "([BenId] = " & lngBenId & ")"
Else
' It's either the name and/or the last name.
If Nz(strLastName, "") = "" Then
strFilter = "([BenLastName] LIKE ""*"" And "
Else
strFilter = "([BenLastName] = '" & strLastName & "' And "
End If
If Nz(strFisrtName, "") = "" Then
strFilter = strFilter & _
"[BenFirstName] LIKE ""*"")"
Else
strFilter = strFilter & _
"[BenFirstName] = '" & strFisrtName & "')"
End If
End If
strNoFilter = "([BenId] = ""*"")"
DoCmd.ApplyFilter FilterName:=strNoFilter
' This last line of code works fine but the next one does not. Why???
' DoCmd.ApplyFilter FilterName:=strFilter

' I tried this.
' Form_frmBen.SetFocus
' Form_frmBen.BenId.SetFocus
' DoCmd.ApplyFilter FilterName:=strFilter
' Form_frmBen.FilterOn = True

' This is my temporary solution and I will leave it like that until I find
the problem.
' Close and reopen the form with the filter...
' ...macramé way of doing things but at least, it works.
DoCmd.Close acForm, strFormName, acSaveYes
DoCmd.OpenForm FormName:=strFormName, View:=acNormal, _
WhereCondition:=strFilter

' I tried that too...
' Forms!strFormName.SetFocus
' Forms!strFormName.BenId.SetFocus
'
' strNoFilter = "([BenId] = ""*"")"
' DoCmd.ApplyFilter FilterName:=strNoFilter
'
' DoCmd.ApplyFilter FilterName:=strFilter
End Sub
' **********************************************************
As you can see, I already have in my code a good part of what you mention
in
your answer.
There are many questions that remain unanswered:
I cannot use the "Me." because I get the message: The object is either
closed or not available anymore...
If I do not close the Search form, it remains opened and I do not want
that.
What can I do?

Thank you again for your help. It keeps me awake.
--
Jac Tremblay

Here's a simple solution. Add a pair of unbound text boxes to the (Form
Header?) of the form that actually contains this data. Name the text
boxes:
txtSearchLastName
txtSearchFirstName
Whatever the user enters in the first will be applied to the LastName
field,
and the second one will be applied to the FirstName field. Substitute
your
own field names.

The code is designed so you can easily add more search boxes if you wish.
Each one assigns the filter string, and tacks an " AND " on the end ready
for the next one. The final " AND " is chopped off at the end.

Dim strWhere As String
Dim lngLen As Long

'Save first
If Me.Dirty Then Me.Dirty = False

If Not IsNull(Me.txtSearchLastName) Then
strWhere = strWhere & "([LastName] = """ & _
Me.txtSearchLastname & """) AND "
End If

If Not IsNull(Me.txtSearchFirstName) Then
strWhere = strWhere & "([Firstname] = """ & _
Me.txtSearchFirstName & """) AND "
End If

'etc for other search boxes if you want.

'Now chop off the trailing " AND ".
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'No criteria: show all.
Me.FilterOn = False
Else
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,
I have a beneficiary form with a Search button on it. When the user
clicks
on it, a Search form appears where one can specify a code or a
LastName/FirstName pair (either one or both can be empty).
From those entries, I build a filter which I want to apply to the
beneficiary form.
This part works fine thanks to the many instructive posts I found on
this
newsgroup.
The part that does not work is the second ApplyFilter (I first clear
the
filter and then try to apply the new one).
Here is some parts of the code I use:
' **********************************
' Remove the old filter (works fine).
strFiltreNul = "(NO_DOSS_IR = ""*"")"
DoCmd.ApplyFilter FilterName:=strFiltreNul

' Apply the new filter (doesn't work).
Form_frmBénéfDeGauche.SetFocus
Form_frmBénéfDeGauche.NO_DOSS_IR.SetFocus
DoCmd.ApplyFilter FilterName:=strFiltre
Form_frmBénéfDeGauche.FilterOn = True
' **********************************
Note: Object and variable names are in French.
Because this part doesn't work, I wrote this code to circumvent the
problem
in the mean time. It's a macramé and inefficient way to do things but
at
least, I can go on with the development.
' **********************************
' Close the form and reopen it with the filter.
DoCmd.Close acForm, strNomFormulaire, acSaveYes
DoCmd.OpenForm FormName:=strNomFormulaire, View:=acNormal, _
WhereCondition:=strFiltre
' **********************************
Note: strNomFormulaire = Form_frmBénéfDeGauche
Anyone has some idea of what is wrong in my application?

Thanks in advance.