I need some help: Command button code that filters on combobox value

[AA Arens]

I have a form with combo boxes. Contents: records of helpdesk tickets.

One of the combobox in cmbCompany.

I am looking for a commandbutton code that has the function to filter
on value of the combobox in the active record on the form.
Then the user can browse to other records that same the same combobox
value.

Releasing the button cancels the filtering.

Thank you.

Bart, Access 2003

 

[Arvin Meyer [MVP]]

I have a similar demo using listboxes at:

http://www.accessmvp.com/Arvin/Combo.zip

Combos work exactly the same way. You cannot release a button and get the
results you want, but you can create another button with code like:

Private Sub cmdShowAll_Click()
DoCmd.RunCommand acCmdRemoveFilterSort
End Sub

to do it.

 

[AA Arens]

I have a similar demo using listboxes at:

http://www.accessmvp.com/Arvin/Combo.zip

Combos work exactly the same way. You cannot release a button and get the
results you want, but you can create another button with code like:

Private Sub cmdShowAll_Click()
    DoCmd.RunCommand acCmdRemoveFilterSort
End Sub

to do it.

Is it in any way possible to active the filter, referring to the value
from a combo?

I see it like this...

Private Sub cmdShowAll_Click()
Me!comboboxname etc...
DoCmd.RunCommand acCmdFilterSort
End Sub

Bart

 

[Arvin Meyer [MVP]]

I have a similar demo using listboxes at:

http://www.accessmvp.com/Arvin/Combo.zip

Combos work exactly the same way. You cannot release a button and get the
results you want, but you can create another button with code like:

Private Sub cmdShowAll_Click()
DoCmd.RunCommand acCmdRemoveFilterSort
End Sub

to do it.

Is it in any way possible to active the filter, referring to the value
from a combo?

I see it like this...

Private Sub cmdShowAll_Click()
Me!comboboxname etc...
DoCmd.RunCommand acCmdFilterSort
End Sub

Bart
-------------------
Try filtering like this:

http://www.accessmvp.com/Arvin/Combo.zip

it uses a list box instead of a combo, but works exactly the same.

 

[John Spencer]

To apply the filter
Dim strFilter as String
strFilter = "[NameofField]=""" & me.ComboBoxName & """"
Me.Filter = strFilter
Me.FilterOn = True

To remove the filter

Me.FilterOn = False

Sometimes you have to force things and set the filter to a zero-length
string.
Me.Filter=""

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[AA Arens]

To apply the filter
Dim strFilter as String
strFilter = "[NameofField]=""" & me.ComboBoxName & """"
Me.Filter = strFilter
Me.FilterOn = True

To remove the filter

Me.FilterOn = False

Sometimes you have to force things and set the filter to a zero-length
string.
   Me.Filter=""

'====================================================
  John Spencer
  Access MVP 2002-2005, 2007-2009
  The Hilltop Institute
  University of Maryland Baltimore County
'====================================================

Is it in any way possible to active the filter, referring to the value
from a combo?

I see it like this...

Private Sub cmdShowAll_Click()
  Me!comboboxname etc...
  DoCmd.RunCommand acCmdFilterSort
End Sub

Bart

Thanks Arvin, John

About John's code,

How to have the filter automatically take the present value from the
combobox as search value instead of defining it manually?

This is the present code btw.

Private Sub CmdFilterOn_Click()
Dim strFilter As String
strFilter = "CompanyName= "Adhimix TA"" & Me.CmbCompanyName & """"""
Me.Filter = strFilter
Me.FilterOn = True
End Sub

 

[John Spencer]

I don't understand what you are asking.

That code does grab whatever the value is. If you mean you want to
apply the filter automatically whenever you change the value in the
combobox, then put the code to filter in the after update event of the
combobox.

You will still need a button to remove the filter or a value in the
combobox that you can test for to remove the filter.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

To apply the filter
Dim strFilter as String
strFilter = "[NameofField]=""" & me.ComboBoxName & """"
Me.Filter = strFilter
Me.FilterOn = True

To remove the filter

Me.FilterOn = False

Sometimes you have to force things and set the filter to a zero-length
string.
Me.Filter=""

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I have a similar demo using listboxes at:
http://www.accessmvp.com/Arvin/Combo.zip
Combos work exactly the same way. You cannot release a button and get the
results you want, but you can create another button with code like:
Private Sub cmdShowAll_Click()
DoCmd.RunCommand acCmdRemoveFilterSort
End Sub
to do it.
--
Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmv...
I have a form with combo boxes. Contents: records of helpdesk tickets.
One of the combobox in cmbCompany.
I am looking for a commandbutton code that has the function to filter
on value of the combobox in the active record on the form.
Then the user can browse to other records that same the same combobox
value.
Releasing the button cancels the filtering.
Thank you.
Bart, Access 2003
Is it in any way possible to active the filter, referring to the value
from a combo?
I see it like this...
Private Sub cmdShowAll_Click()
Me!comboboxname etc...
DoCmd.RunCommand acCmdFilterSort
End Sub
Bart

Thanks Arvin, John

About John's code,

How to have the filter automatically take the present value from the
combobox as search value instead of defining it manually?

This is the present code btw.

Private Sub CmdFilterOn_Click()
Dim strFilter As String
strFilter = "CompanyName= "Adhimix TA"" & Me.CmbCompanyName & """"""
Me.Filter = strFilter
Me.FilterOn = True
End Sub

 

[AA Arens]

I don't understand what you are asking.

That code does grab whatever the value is.  If you mean you want to
apply the filter automatically whenever you change the value in the
combobox, then put the code to filter in the after update event of the
combobox.

You will still need a button to remove the filter or a value in the
combobox that you can test for to remove the filter.

'====================================================
  John Spencer
  Access MVP 2002-2005, 2007-2009
  The Hilltop Institute
  University of Maryland Baltimore County
'====================================================

To apply the filter
Dim strFilter as String
strFilter = "[NameofField]=""" & me.ComboBoxName & """"
Me.Filter = strFilter
Me.FilterOn = True
To remove the filter
Me.FilterOn = False
Sometimes you have to force things and set the filter to a zero-length
string.
   Me.Filter=""
'====================================================
  John Spencer
  Access MVP 2002-2005, 2007-2009
  The Hilltop Institute
  University of Maryland Baltimore County
'====================================================
AA Arens wrote:
I have a similar demo using listboxes at:
http://www.accessmvp.com/Arvin/Combo.zip
Combos work exactly the same way. You cannot release a button and get the
results you want, but you can create another button with code like:
Private Sub cmdShowAll_Click()
    DoCmd.RunCommand acCmdRemoveFilterSort
End Sub
to do it.
--
Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmv...
I have a form with combo boxes. Contents: records of helpdesk tickets.
One of the combobox in cmbCompany.
I am looking for a commandbutton code that has the function to filter
on value of the combobox in the active record on the form.
Then the user can browse to other records that same the same combobox
value.
Releasing the button cancels the filtering.
Thank you.
Bart, Access 2003
Is it in any way possible to active the filter, referring to the value
from a combo?
I see it like this...
Private Sub cmdShowAll_Click()
  Me!comboboxname etc...
  DoCmd.RunCommand acCmdFilterSort
End Sub
Bart

Thanks Arvin, John

About John's code,

How to have the filter automatically take the present value from the
combobox as search value instead of defining it manually?

This is the present code btw.

Private Sub CmdFilterOn_Click()
Dim strFilter As String
strFilter = "CompanyName= "Adhimix TA"" & Me.CmbCompanyName & """"""
Me.Filter = strFilter
Me.FilterOn = True
End Sub

I only want to filter on the existing value. This is the code in the
click event of the command button:

Private Sub CmdFilterOn_Click()
Dim strFilter As String
strFilter = "[CompanyName]= """ & Me.CmbCompanyName & """"""
Me.Filter = strFilter
Me.FilterOn = True
End Sub

But I get a run-time error 2448 - "You can't assign a value to this
object" and Me.Filter = strFilter is highlighted yellow.

Bart

 

[Steve Sanford]

You need to do a little basic (no pun intended) troubleshooting...

Change your code (temporarily) to:

Private Sub CmdFilterOn_Click()
Dim strFilter As String
strFilter = "[CompanyName]= """ & Me.CmbCompanyName & """"""
MsgBox strFilter
' Me.Filter = strFilter
' Me.FilterOn = True
End Sub

I would be willing to bet that there are too many quotes (should be 4, not
6) at the end if the strFilter which would result in an error.

Once you have a valid string in strFilter, delete or comment the message box
line and uncomment the two filter command lines.

HTH

 

[AA Arens]

You need to do a little basic (no pun intended) troubleshooting...

Change your code (temporarily) to:

Private Sub CmdFilterOn_Click()
   Dim strFilter As String
   strFilter = "[CompanyName]= """ & Me.CmbCompanyName & """"""
   MsgBox strFilter
   '   Me.Filter = strFilter
   '   Me.FilterOn = True
End Sub

I would be willing to bet that there are too many quotes (should be 4, not
6) at the end if the strFilter which would result in an error.

Once you have a valid string in strFilter, delete or comment the message box
line and uncomment the two filter command lines.

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

I only want to filter on the existing value. This is the code in the
click event of the command button:

Private Sub CmdFilterOn_Click()
Dim strFilter As String
strFilter = "[CompanyName]= """ & Me.CmbCompanyName & """"""
Me.Filter = strFilter
Me.FilterOn = True
End Sub

But I get a run-time error 2448 - "You can't assign a value to this
object" and Me.Filter = strFilter is highlighted yellow.

Bart

With this code it works, but the filter is canceled after I browse to
the next record. I can see it from the filter icon "filter by
selection" that re-appears after browsing to the next record. Users
will not have access to this toolbar, which is the reason for the
button.

Private Sub CmdFilterOn_Click()
Dim strFilter As String
strFilter = "[CompanyName]= """ & Me.CmbCompanyName & """"
' MsgBox strFilter
Me.Filter = strFilter
Me.Filter = True
End Sub

 

[Steve Sanford]

Do you have any code in the "Form_Current()" event that removes the filter?

AFAIK, moving to a different record shouldn't remove the filter.


HTH

 

[AA Arens]

Do you have any code in the "Form_Current()" event that removes the filter?

AFAIK, moving to a different record shouldn't remove the filter.

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

With this code it works, but the filter is canceled after I browse to
the next record. I can see it from the filter icon "filter by
selection" that re-appears after browsing to the next record. Users
will not have access to this toolbar, which is the reason for the
button.

Private Sub CmdFilterOn_Click()
Dim strFilter As String
   strFilter = "[CompanyName]= """ & Me.CmbCompanyName & """"
   ' MsgBox strFilter
   Me.Filter = strFilter
   Me.Filter = True
End Sub

No, not related to the filter. Dismissing the SetFocus comment that I
have also does not help.

 

[Steve Sanford]

Somehow two letters were lost in the code.

Try the following:


Private Sub CmdFilterOn_Click()
Dim strFilter As String
strFilter = "[CompanyName]= """ & Me.CmbCompanyName & """"
Me.Filter = strFilter
Me.FilterOn = True

'----
' Me.FilterOn , not Me.Filter
'----

End Sub




HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Do you have any code in the "Form_Current()" event that removes the filter?

AFAIK, moving to a different record shouldn't remove the filter.

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

With this code it works, but the filter is canceled after I browse to
the next record. I can see it from the filter icon "filter by
selection" that re-appears after browsing to the next record. Users
will not have access to this toolbar, which is the reason for the
button.

Private Sub CmdFilterOn_Click()
Dim strFilter As String
strFilter = "[CompanyName]= """ & Me.CmbCompanyName & """"
' MsgBox strFilter
Me.Filter = strFilter
Me.Filter = True
End Sub

No, not related to the filter. Dismissing the SetFocus comment that I
have also does not help.

 

[AA Arens]

Somehow two letters were lost in the code.

Try the following:

Private Sub CmdFilterOn_Click()
Dim strFilter As String
   strFilter = "[CompanyName]= """ & Me.CmbCompanyName & """"
   Me.Filter = strFilter
   Me.FilterOn = True

'----
  '  Me.FilterOn , not   Me.Filter
'----

End Sub

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Do you have any code in the "Form_Current()" event that removes the filter?
AFAIK, moving to a different record shouldn't remove the filter.
HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
With this code it works, but the filter is canceled after I browse to
the next record. I can see it from the filter icon "filter by
selection" that re-appears after browsing to the next record. Users
will not have access to this toolbar, which is the reason for the
button.
Private Sub CmdFilterOn_Click()
Dim strFilter As String
   strFilter = "[CompanyName]= """ & Me.CmbCompanyName & """"
   ' MsgBox strFilter
   Me.Filter = strFilter
   Me.Filter = True
End Sub

No, not related to the filter. Dismissing the SetFocus comment that I
have also does not help.

Hi Steve, it works now. Just a question, how to let Access jump to the
last record that belongs to the filter, so browsing to the previous
will show the records that pass the filter. The reson: The latest
records are the most actual.

I use this command
DoCmd.GoToRecord , , acLast

But it also disables the filter and goes to the very last record.



Bart

 

[Steve Sanford]

When you execute the command

DoCmd.GoToRecord , , acLast

it goes to the last record of the *form recordset*, not the filtered
(displayed) records.

Here is an example:

Suppose a form has a record source of:

SELECT * FROM tblEmployees WHERE [Active] = TRUE ORDER BY Last_Name;

and it returns 100 records. Now you want to see only the employees from
Alaska. So you set a filter on

Me.Filter = "[ST] = 'Alaska'"
Me.FilterOn = TRUE

Now, only 10 records are displayed. What happened to the other 90 records?
Was the recordset reduced to just 10 records? No, they were just hidden. So
when you execute a command that says "Go to the last record", it goes to the
last record of the *recordset*, not the last record or the filtered recordset.


The only way I can think of is (in code) to get the form record source,
modify it to include the filter, open a recordset, move to the last record,
then set the form bookmark to the recordset bookmark.


HTH

 

[AA Arens]

When you execute the command

    DoCmd.GoToRecord , , acLast

it goes to the last record of the *form recordset*, not the filtered
(displayed) records.

Here is an example:

Suppose a form has a record source of:

   SELECT * FROM tblEmployees WHERE [Active] = TRUE ORDER BY Last_Name;

and it returns 100 records. Now you want to see only the employees from
Alaska. So you set a filter on

    Me.Filter = "[ST] = 'Alaska'"
    Me.FilterOn = TRUE

Now, only 10 records are displayed. What happened to the other 90 records?
Was the recordset reduced to just 10 records? No, they were just hidden. So
when you execute a command that says "Go to the last record", it goes to the
last record of the *recordset*, not the last record or the filtered recordset.

The only way I can think of is (in code) to get the form record source,
modify it to include the filter, open a recordset, move to the last record,
then set the form bookmark to the recordset bookmark.

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Hi Steve, it works now. Just a question, how to let Access jump to the
last record that belongs to the filter, so browsing to the previous
will show the records that pass the filter. The reson: The latest
records are the most actual.

I use this command
DoCmd.GoToRecord , , acLast

But it also disables the filter and goes to the very last record.

Bart

If I use a second filterbutton to filter from another combo with row
sourse SELECT tblContacts.ContactName, tblContacts.ContactName FROM
tblContacts WHERE (((tblContacts.CompanyName)=Forms!
Calls.CompanyName)) ORDER BY tblContacts.ContactName;
then it deletes the value from the previous combo company name.
Why?

Bart

 

[AA Arens]

When you execute the command

    DoCmd.GoToRecord , , acLast

it goes to the last record of the *form recordset*, not the filtered
(displayed) records.

Here is an example:

Suppose a form has a record source of:

   SELECT * FROM tblEmployees WHERE [Active] = TRUE ORDER BY Last_Name;

and it returns 100 records. Now you want to see only the employees from
Alaska. So you set a filter on

    Me.Filter = "[ST] = 'Alaska'"
    Me.FilterOn = TRUE

Now, only 10 records are displayed. What happened to the other 90 records?
Was the recordset reduced to just 10 records? No, they were just hidden.. So
when you execute a command that says "Go to the last record", it goes to the
last record of the *recordset*, not the last record or the filtered recordset.

The only way I can think of is (in code) to get the form record source,
modify it to include the filter, open a recordset, move to the last record,
then set the form bookmark to the recordset bookmark.

HTH

If I use a second filterbutton to filter from another combo with row
sourse SELECT tblContacts.ContactName, tblContacts.ContactName FROM
tblContacts WHERE (((tblContacts.CompanyName)=Forms!
Calls.CompanyName)) ORDER BY tblContacts.ContactName;
then it deletes the value from the previous combo company name.
Why?

Bart

Anybody has a solution for this matter?

 

[Steve Sanford]

Bart,

Don't know....can't see your MDB.

- What is the code for the second filter button?

- Is there any code in the after update event of the second combo box?

- Why do you have

SELECT tblContacts.ContactName, tblContacts.ContactName FROM

for the row source of the second combo box? ("ContactName" twice?)

What is "= Forms!Calls.CompanyName"? Shouldn't it be looking at the combo
box named "CmbCompanyName". Are you trying to set up cascading combo boxes
(Select the company, then select the contact name)?


What are you trying to do? Do you want to be able to filter by company name,
then by contact name? You can use two combo boxes and one button instead of
two buttons.


HTH

 

[AA Arens]

Bart,

Don't know....can't see your MDB.

 - What is the code for the second filter button?

 - Is there any code in the after update event of the second combo box?

 - Why do you have

       SELECT tblContacts.ContactName, tblContacts.ContactName FROM

for the row source of the second combo box? ("ContactName" twice?)

What is   "= Forms!Calls.CompanyName"?  Shouldn't it be looking at the combo
box named "CmbCompanyName".   Are you trying to set up cascading combo boxes  
(Select the company, then select the contact name)?

What are you trying to do? Do you want to be able to filter by company name,
then by contact name? You can use two combo boxes and one button instead of
two buttons.

HTH

I use the same code (but then for contactname, the code below is for
company filter)

Private Sub CmdFilterOn_Click()
Dim strFilter As String
strFilter = "[CompanyName]= """ & Me.CmbCompanyName & """"
Me.Filter = strFilter
Me.Filter = True
End Sub

I use a few combo's in the form and after selecting the company name,
I select contaxct person from that slected company name.

With regards to "twice company name", you have a better solution?

I just want to add a possibility to filter on contact person, beside
company name. I already have added a filter on another combo box and i
can indeed use it together, e.g. two conditions.

But I use two filter buttons for the two combo's.

 

[Steve Sanford]

I use the same code (but then for contactname, the code below is for
company filter)

It doesn't help telling me that it is the same code as something else. I
can't see the code. If it is the same code, then it is wrong because the code
below is wrong and won't work.

Private Sub CmdFilterOn_Click()
Dim strFilter As String
strFilter = "[CompanyName]= """ & Me.CmbCompanyName & """"
Me.Filter = strFilter
Me.Filter = True
End Sub

You CANNOT use "Me.Filter = TRUE". The syntax is "Me.FilterON = TRUE"

With regards to "twice company name", you have a better solution?

The question wasn't about "twice company name"; you have


The question was "Why select ContactName twice??"

I just want to add a possibility to filter on contact person, beside
company name. I already have added a filter on another combo box and i
can indeed use it together, e.g. two conditions.

But I use two filter buttons for the two combo's.

With out being able to see your MDB, I have to ask questions about it and
rely on you to provide the info. When the answer is "It is the same as the
other code", then there is nothing I can do to help.

Having the form names, control names and the exact code makes it a lot
easier to try and determine a solution.


Good luck...hope you find the solution.