Please Help: Combo Box Criteria Statement

[mikeone610]

I have a search form setup with a Combo Box which is populated with the unique values found in "TableA!ColumnA". My goal is to allow a user to select one of the values from the combo box and then run the query and only data entries which share the same value in "TableA!ColumnA" will be shown. If the combo box is not used it should show everything in that field. I should add that the values in question are all text and not numbers.

I am pretty new to Access and am not very familiar with VBA codes so just a warning that most of that stuff goes over my head. Because of this I was hoping to just reference the combo box as a control in the query's design view for the field I am trying to filter ("TableA!ColumnA").

Thus far I have tried to place "[Forms]![SearchForm]![ComboCity]" in the criteria for the field but when I run the query it comes up blank whether the combo box is being used or not.

Does anyone know what I need to put in the criteria to make this happen?

EDIT: If this was suppose to be in the Forms section of the forum please let me know, I figured this was the right place to post the question.

[June7]

Use LIKE with wildcard in the query criteria parameter then need to requery the form. Review http://datapigtechnologies.com/flash...tomfilter.html

[mikeone610]

I just figured out most of it, I was missing a Control Source. So it works kinda. My new problem is, ugh this is tough to explain:

-I have two sheets, one that has names and no duplicates (Sheet1) and another with a name field and activities field and duplicates(Sheet2).
-The query is set up like this: Names (from Sheet1) and Activity (from Sheet2).
-When I filter by the ComboBox I am doing so depending on what is in the Activity value on Sheet2.

-My goal is to have people displayed whether they ever fit the criteria of the ComboBox once, regardless if they do not fit the criteria in other ways.
-Currently when I run the query while using the combo box the results will show me every time someone did this activity. So it would show:

John Doe ; Running
John Doe ; Running
Mary Doe ; Running
Mary Doe ; Running
Phil Doe ; Running

- I want it to look like this:

John Doe ; Running
Mary Doe ; Running
Phil Doe ; Running

Is this a different problem all together? Or does it belong on another forum?

[June7]

Why would you need ControlSource? A control used to input filter criteria should be unbound, otherwise you will change data in record.

What do you mean 'Sheet1', 'Sheet2'? Are these links to an Excel workbook?

I don't understand what you are doing. What is the SQL of the form and what is the SQL of the combobox RowSource? Are names in both 'sheets'?

[mikeone610]

I miss typed, I need to set the bound column to 2 instead of the default 1.

They are two separate tables in Access and the names are in both sheets but I have the query set up as:

Names (from Sheet1) and Activities (from Sheet2)

I am pretty sure the combo boxes are good now the problem is how the data is arranged in the query results. I am telling it through the combo box that I want all entries that have "running" in the field and it brings me back every time any name has the word "running" in the field. So I get many duplicate names because it is common for a person to have done it twice or three times. I want it to bring back each persons name once even if they match the combo box criteria one time or 20 times.

I thought I could do this by changing the total in the field to "count" but when I do that the query returns nothing.

EDIT: I guess I am asking does this cell meet this criteria from the combo box Yes or No? And then show me all of the Names from Sheet1 which are a yes opposed to every name from sheet one, every time it is a yes in the Activities field from Sheet2.

[June7]

Then you need to build a query that retrieves only DISTINCT combination of Name/Activity and filter on that dataset. This would not be an editable dataset.

SELECT DISTINCT Names, Activities FROM Sheet2;

or

SELECT Names, Activity GROUP BY Names, Activity;

[mikeone610]

Does that SELECT DISTINCT Names, Activities FROM Sheet2; go in the criteria spot or somewhere else?

EDIT: I went to the SQL View of the query and tried to add the word "DISTINCT" in there after "SELECT" but that did not make a difference. In case that is what you were saying.

[June7]

That query would be the RecordSource of form.

I didn't think to show the WHERE clause.

SELECT DISTINCT Names, Activities FROM Sheet2 WHERE Activities Like [Forms]![SearchForm]![ComboCity] & "*"

[mikeone610]

I am sorry what do you mean RecordSource? I am kinda new.

[June7]

I assumed you built a form and used the parameterized query as the form's source of data (RecordSource property of the form). That is what is demonstrated in the link I provided.

[mikeone610]

Yes, but how would I get back to the RecordSource to edit it and add what you have above here?

[June7]

Go to Design View of form, edit the RecordSource property.

Building and modifying Access objects is basic functionality. Access Help has guidance.

[mikeone610]

Ok. I found the RecordSource of the form. Now I just need to put that SELECT DISTINCT... statement from above into the RecordSource field?

[June7]

Yes, that is one way to set RecordSource property.

[mikeone610]

Ok I was just surprised that it did not kinda pop up with suggestions like in the criteria fields. Does this also mean all of my criteria from the query needs to be in the form's RecordSource or it won't work correctly now?