Filter A Form With Combo Box

[JeffG3209]

Trying to filter a form named [SEARCH] with a combo box.


The field in [SEARCH] would be [REP].
Combo Box values would be "Jeff" and "Paul"

From what I have found a requery would be my best bet. I am having a hard time creating a requery.

I am willing to use macros if it is easier that way. I have spent about two days searching the internet and this forum. I am completely lost.

I imagine it would go something like this...

FORMS![SEARCH].[REP] xxxxxx

[alansidman]

Is this what you are trying to accomplish?

http://www.datapigtechnologies.com/f...earchform.html

Alan

[JeffG3209]

Is this what you are trying to accomplish?

http://www.datapigtechnologies.com/f...earchform.html

Alan

Alan, I like your videos. Your website is bookmarked on my browser. I have used MANY of your videos to help me with various thing. Let me watch this one again. If I can't figure out a specific thing I will reply again. Thanks!

[JeffG3209]

Could you tell me where I am going wrong?

[Forms]![SEARCH].[REP] & "JEFF""PAUL"

[alansidman]

If I understand what you are saying, your combo box has two records in it. Jeff and Paul. If this is the case, you would select one of them and in your criteria, replace "Jeff""Paul" with &"*"

The beginning of the criteria (the like statement) tells Access to look at what you selected on your search page using the combo box as a the source. This also assumes that the combobox is named REP. If it is something else then you need to change it in the Like statement.
So it would look like this

Like [Forms]![SEARCH].[REP] & "*"

Alan

[June7]

EDIT: Alan posted above while I was composing so I removed my comment.

[JeffG3209]

I chose to us "on change" instead of "on click".

Private Sub qrep1_Change()
DoCmd.OpenQuery "Rep Filter", acViewNormal
End Sub

I changed the Query Criteria to:

Like [Forms]![SEARCH].[REP] & "*"

If I understand what you are saying, your combo box has two records in it. Jeff and Paul.

Yes.



I can't figure out why the combo box is populating with 3 values of "Paul" in Form View. Also, the 4th picture on the right, how do Ihow do I get the query to run on the SEARCH from and not open the query.


Alan,
I really appreciate the help more than you know!
Once I figure out how this works a bit more I will not be so needy

[June7]

Modify the RowSource statement for the combobox to:
SELECT DISTINCT ...

Instead of the DoCmd.OpenQuery, just
Me.Requery
This should requery the form's RecordSource and refresh the display.

[JeffG3209]

Changed that.

Private Sub qrep1_Change()
Me.Requery "Rep Filter", acViewNormal
End Sub

Now it's throwing an syntax error when I run the Query as shown in the screen shot below.

[June7]

You said you did not want a query to open. Is this filter criteria part of the RecordSource for a form or report?

Where is this code?

Is it behind the form with the records you want filtered? If so, just Me.Requery, no arguments.

If this code is behind a search form and you want to open another form, the Requery is not needed, just code to open the form.

If this code is behind a search form and the form to be filtered is already open then instead of Me refer to the form by name. Syntax is: Forms_formname.Requery

Attach your project to a post and we will take a look at.

Before running code you should Compile, this should catch syntax errors like the Requery with arguments you tried.

[JeffG3209]

I'm sorry. Let me try to explain.

Filter Criteria is what I am trying to accomplish. I would like to be able to do what Access has in place for filters without the right click.

The SEARCH form is a continuous form. It is a general summary of the record. I would like to be able to filter the results on the SEARCH form by REP, STATUS, TOTAL ACCOUNT, PRIORITY, TYPE, etc. I figured I would use REP since it is the easiest to explain and the lowest amount of values.

There shouldn't be any other forms associated with this query.

I'm not anywhere near good, but I have been trying through trial and error. Many times, If I try long enough I figure things out. When I can't, I turn here. You and Alan have helped me a few times already.

The easiest way for me to explain what I am trying to do is with this image. This is a different field, but you get the idea

I would like to be able to filter my SEARCH form by the combo box values that are in a column.

Thanks again for taking the time to walk me through this.

[June7]

Do I understand correctly:

1. One form in ContinuousView.

2. The criteria combobox is in Form Header section.

3. Form has a query as RecordSource and this query has the filter criteria that refers to the combobox.

4. You are using Change event of the combobox to execute the Me.Requery. I recommend the AfterUpdate event.

5. Right now you have only one criteria and therefore one combobox. If you want multiple criteria from several comboboxes will need a command button click event instead of combobox event for the Me.Requery.

[JeffG3209]

Do I understand correctly:

1. One form in DatasheetView. Correct

2. Where is the criteria combobox? DatasheetView doesn't allow display of controls. SEARCH form header. (See image)

3. Form has a query as RecordSource and this query has the filter criteria that refers to the combobox. Yes

4. You are using Change event of the combobox to execute the Me.Requery. I recommend the AfterUpdate event. I will change this now

5. Right now you have only one criteria and therefore one combobox. If you want multiple criteria from several comboboxes will need a command button click event instead of combobox event. True. I will use datapigs video to help me make this.

If I understand correctly the after update box will take effect after the command button has been clicked.

[June7]

Ooops. I was editing my post while you were composing. The quote doesn't capture my final edit but I get the info.

The command button will have no impact on the AfterUpdate event. The AfterUpdate event runs immediately after value in combobox is entered/selected when pressing Enter or Tab or clicking item in list. Use command button click event now if that is where need to eventually go for multiple criteria for the Me.Requery.

[JeffG3209]

How does this sound for a starting point.
1. Adding the combo boxes and creating the queries
2. Creating the command button and code behind it.
3 Trial and error the entire process.
4. If I can't figure it out post back on here.