"How to filter form records based on combo box value?

[Guest]

I have a form opening with a Select Query displaying ALL records. I placed
an Unbound combo box that looks up values in a separate table on the form.
What I want to do is...

Select a value from the combo box and filter the form for records using the
selected value in the combo box.

The field name is Competitor in the form's underlying query. The combo box
is named Comp.

No matter what I do I get zero (0) records retrieved after update of the
combo box.

Any help would be greatly appreciated.

Stu

 

[Jeff Boyce]

I'm wondering if you might have a table somewhere with a "lookup" data field
involved in this description. Lookup data fields store one value, but
display another (looked up) value. This can lead to confusion in forms,
queries, etc.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

In the AfterUpdate event of the combobox, put this code:

Me.FilterOn = True
Me.Filter = "SELECT * From MyTable WHERE MyTable.Competitor = " & Me.Comp

This assumes the bound column of the combobox is numerical. If it's text,
put this:

Me.FilterOn = True
Me.Filter = "SELECT * From MyTable WHERE MyTable.Competitor = '" & Me.Comp &
"'"

HTH,
Barry

 

[Guest]

Barry,

I did as instructed and got a Syntax error message. I replaced MyTable with
tblInterchange as defined in Tables of my database in both MyTable instances.
I noticed on the Navigation Bar at the botom of form that the word Filtered
appears, but ALL records are displayed and not the selected value in the
combo box. I remove the applied filter and get ALL records with NO Filtered
showing. It seems we are very close. Syntax Error???
The form's underlying query is based on tblInterchange and opens with ALL
records displayed. I added the combo box (Comp) on the form and it's Row
source is a Select query (tblCompetitiveLine) with two fields. The first
field on the grid is a numeric field and the Bound field. The combo box on
the form displays the value in the second field (Comp). Hope this helps.
Truly apprciated.

 

[Guest]

Oops. My mistake. Change it to:

Me.FilterOn = True
Me.Filter = "Competitor = " & Me.Comp

Barry

 

[Guest]

Tried again; Another syntax error (missing operator)

 

[Guest]

Is the Competitor field numeric? If it's text, it should be:
Me.Filter = "Competitor = '" & Me.Comp & "'"

You need to add the single quotes.

Barry