How do I use a combo box to filter a query's results in my form?

[Guest]

I have a form that lists multiple records (a query provides it with the
data). At the bottom of my form (in the form footer), I have a combo box. I
would like the combo box to be the criteria in the query (so it will only
display some of the records of the query and not all).

In the query, I put the following in the criteria:
[Forms]![compiledlistview]![Combo42] Or [Forms]![compiledlistview]![Combo42]
Is Null

On the combo box in the form, I then put "requery" on the After Update
criteria.

However, the form will not update with only the records that match the combo
box's value. Is there a way to do this and if so what? Thanks much for your
help!

 

[Ron2006]

The criteria statement does not make sense to me. It does not seem to
be a valid statement. It seems to be saying to select all records
which match the combo42 OR anything that is null in the field.

I have done the process you are describing lots of times and the
procedure works as long as the criteria is stated properly.

However, on those conditions when I want everything I change the
recordsource to a different query.

Ron

 

[Guest]

Thanks Ron2006,

The criteria does look weird, but it's designed to basically say "Show me
anything that matches the combo box, and if the combo box is blank, show me
all records".

I still have the same problem even if my criteria is just (I tried it):
[Forms]![compiledlistview]![Combo42]

So, I'm not sure if my criteria statement is causing the problem or if I am
not doing something correctly on "After Update" - do you see anything wrong
with what I am doing?

Cona Chris

 

[Ron2006]

It should work. So now we try to figure out what is not connecting.

After you changed the cirteria to just look at the combo box, when you
opened the form, it should have been empty since there was nothing in
the combo (or at a minimum it should have only shown those that
matched the null of the combo box.)

Was it empty? If it was not then I would say that the data you are
seeing is not from that conditioned query so a requery will not have
an affect.

===========================
a separate issue:

for the all record selection when the combo is empty, unless you are
using a iif() condition in the criteria, I am not sure how the query
can make a match between the field and the criteria you are
specifying.

The "isnull(xxx)" and "not isnull(xxx)" condition usually has to be
done in that format. I have never seen a xxxxxx = null type of
test work properly.

Ron

 

[Guest]

Thanks for the tips on the Is Null! I'll play around with that after I get
this other part figured out.

After changing the criteria - when I opened the form, it was indeed empty.
I then select something from the combo box, and the form recalculates (it
says so in the bottom left corner) and after a couple seconds, it's done, but
the form remains empty.

I've tested to make sure I have referenced the correct combo box. I've also
checked that the form will return records if the criteria from the query has
been removed (it does).

Not sure if this matters, but the combo box was set up to "remember the
value for later use" and not "store that value in this field".

 

[Ron2006]

Thanks for the tips on the Is Null! I'll play around with that after I get
this other part figured out.

After changing the criteria - when I opened the form, it was indeed empty.
I then select something from the combo box, and the form recalculates (it
says so in the bottom left corner) and after a couple seconds, it's done, but
the form remains empty.

I've tested to make sure I have referenced the correct combo box. I've also
checked that the form will return records if the criteria from the query has
been removed (it does).

Not sure if this matters, but the combo box was set up to "remember the
value for later use" and not "store that value in this field".

The setup of the combo is correct, it is not bound to a field so what
you have selected is correct for that.

The fact that you are showing "recalculating" implies we are doing the
requery.

So now:
Are there more than one field in the combo box?
Is the bound field the actual field that will find a match in the
table that the query is against.

For instance if you are retrieving the only a single field in the
combo (say company name) but the field in the table is the company ID
then you will not get a match.

Or
Maybe you are retrieving company ID and company Name and the company
ID is the bound field but it is 0 length and the company name is
showing,
but the company name is what is in the table that you are matching
then again you will not get a match.

I think this is the area that we need to look at now, since all
indications are that everything else is set up properly.

Ron

 

[Guest]

There is just one field in the combo box, so crossing up the dispalyed field
with the bound field shouldn't be an issue since they are the same.

I've redone the combo box from scratch a few times to make sure I haven't
made some silly mistake somewhere, but still no luck...

 

[Ron2006]

hmmmm....

Now we are at the point of grasping for straws......

I just made a test form and it worked fine, and because of the
calculating message you are getting, I think our process is correct.
This leaves only the combo box and the query.

Is there another combobox on the form, could you possibly be using the
other box as the criteria instead of the proper one?

You said you rebuilt the combo. That should have changed the name each
time, did you change the query each time?
In fact if you did not then that would indicate you are pointing to
the wrong combo. (It is usually helpful to name combos something other
than combo1 etc.

 

[Guest]

Yes, I changed the name each time... there is onlt one combo box too. I'll
keep looking at it... gotta be something silly I'm doing.

 

[Ron2006]

Does the field happen to be a date?

If you have a date in the combo but a date and time in the actual
field, then you will NOT get a match using the standard query method.
You have to separate the date part from the rest of the field to make
the comparison on.

Ron

 

[Guest]

Sorry - no, it's a text field.

 

[Ron2006]

As a test to see if we can narrow down the problem

1) change the query to have criteria of
like [Forms]![compiledlistview]![Combo42] & "*"

2) change the combo to allow entries other than what is in the list.

3) Try putting a single character in the combo and see if it will give
us something reasonable.

Another thing might be to fill in the form etc but then find the tab
for the rest of the access program on you task bar and open it
(usually clicking it a second time brings it back up) and then run the
query manually. That way you can play with the query to see if you
can find something that works.

Be sure, on the form to select or type something in the combo but then
to move focus to a different field.

Ron

 

[Guest]

Hi..
I too am trying to find out how to do this?
I have also tried all the things you have listed, bit without any luck.
When you click the combo box though, it should bring up the references in
the field, but it doesn't.

I have resorted to just filtering using right-click and putting the
reference in the 'Filter For' box. However this is only a makedo...

 

[Guest]

GOT IT!

I had "requery" in the code on AfterUpdate, and when I changed it to
"me.requery" then everything worked.

I appreciate your help Ron2006! Those tips (especially on the null) will
come in handy!

 

[Ron2006]

I am glad it is finally working.

Have an even greater rest of the day.

Ron