Filter button on access form

[keshavmodi]

How do we put the filter button on an access form?

I am talking about the simple filter button which when clicked pops up a menu for that field and asks the user to select the relevant data

 

[Trevor G]

Welcome to the forum,

When you are in design view on the form there are some tools that appear in your icons at the top of the screen, you need to find the combo box and also make sure the wizard is switche don, then click the icon and click again in the form somwhere, as it starts the wizard you will be prompted and you want to select find a record based on the form, continue through the wizard and select the field to use and then complete the other steps.

Then save the form and change the view and check out the drop down and it will give you your filter.

 

[keshavmodi]

Thanks for the reply.
But I am afraid it does not answer my question.

In Access 2007 there is an icon for filter in the ribbon.I just want to place that button for filter in my form.Can you help me with that?

 

[Trevor G]

Thanks for the reply.
But I am afraid it does not answer my question.

In Access 2007 there is an icon for filter in the ribbon.I just want to place that button for filter in my form.Can you help me with that?

If you add a command button and then when the wizard starts you would need to select the form section on the left then you will see you can select apply filter, filter by form and filter by selection options

 

[keshavmodi]

I have tried that method. But it only gives the apply filter, filter by form and filter by selection options. In Access 2007 in the ribbon there is filter icon beside the advance filter menu. When you click on it, a filtering window opens in which you can put a tick across the records for that particular field. I want the functionality of that button on my form. How can it be done?

 

[vbaInet]

Docmd.RunCommand acCmdFilterMenu

 

[keshavmodi]

I tried to put that in the vb code.
However, whenever I click on the button, I get the following message:

Run-time error '2046':
The command or action 'FilterMenu' isn't available now.

 

[vbaInet]

Put the cursor in that field and look at the Filter icon on the ribbon. You will find that the Filter button is not enabled. For example, if the control is not bound you can't apply a filter on it.

 

[keshavmodi]

I had thought of the same thing. But that is not the case. The Filter button is ENABLED. Still I am getting this message.

 

[vbaInet]

Maybe your field is too complex. Is it directly from a table or is it a calculated field?

 

[Trevor G]

To run the filter option that you want you need a command button and then look at the on click event, you then need a docmd.gotocontrol command and then you can use the acCmdFilterMenu

Private Sub Command10_Click()
DoCmd.GoToControl "Employeename"
DoCmd.RunCommand acCmdFilterMenu
End Sub

 

[keshavmodi]

Its a direct field from a table. And the filter button on the ribbon is working perfectly.
However, the button on the form is not functioning.

 

[vbaInet]

I think Trevor has solved your problem, go to or setfocus to the control before calling the command.

 

[keshavmodi]

It works by setting focus to a particular field. Thank you for the help.

Have a nice day!

 

[vbaInet]

Great stuff!!

And you.

 

[keshavmodi]

Just one thing...

If I select the field where i have to set focus from a list box named "list_fields" , how do I use it in my code to set focus?

 

[vbaInet]

Hmm... I don't quite understand. Maybe explain with an example?

 

[keshavmodi]

There are more than one fields in my form.

So while writing the code I do not know the field where I have to set the focus to.

So I plan to ask the user to choose the field using a list box.Using that value i plan to set focus.

Name of the list box is "list_fields".

So how do i write the statement for setting the focus?

do i write:

list_fields.value.setfocus

i need the correct syntax

 

[vbaInet]

You can use the Option Group control or listbox. The user will pick which control he/she wants to filter by then based on that you setfocus.

Or setfocus to the Previous Control if and only if it is a valid control.

For setting focus:
Screen.PreviousControl.SetFocus

For checking the control type:
Screen.PreviousControl.ControlType

 

[keshavmodi]

Setting focus on the previous control works perfectly.
Can you suggest any help for Access or VBA coding? I am fairly new in it.