Combo Box to Filter query

[Ray S.]

I have a form with a combo box that contains three optional selections. I'm
using the form to filter data from a table. My problem is that only the first
selection works. I'm absolutely certain that I have spelled the options
correctly. When I enter them directly as criteria, rather than using the
combo box, the query works perfectly, but when I use the combo box it only
filters if you select the first option on the list. If you select either of
the other two options, the query returns no results. What am I doing wrong?

 

[Ray S.]

My sincere apologies to the community. I was trying to post a question and
kept getting a message saying there had been an error and my post could not
be sent. Now I see that I posted my question multiple times.

 

[Ken Sheridan]

The error you are experiencing with the MS interface to this newsgroup is
widespread, which is why we are getting so many multiple posts right now.

If you are using a value list for the combo box's RowSourceType, and the
values are not a set which is fixed in the external world (days of the week
or months of the year are examples of such sets), then you are storing data
as part of the form definition, whereas data should only be stored as values
at column positions in rows in tables. That way integrity can be ensured.

Taking a simple example, lets say the query is based on a table
Questionnaire in which you have a column named Answer to which you are
applying the combo box's value as a parameter, and which can have legitimate
values of "Yes", "No" or "Don't Know", then you should also have a table
Answers with columns Answer (the primary key) and AnswerOrder, the last
merely being to control the order in which the three options appear in the
list, so it might contain the following Rows:

Yes 1
No 2
Don't Know 3

This table should be related to Questionnaire on the Answer columns and
referential integrity enforced. This ensures that only legitimate values can
be entered in the Answer column in Questionnaire. As 'natural' keys are used
here rather than 'surrogate' numeric keys, cascade updates should also be
enforced.

The RowSourceType property of the combo box would now be Table/Query and the
RowSource property would be:

SELECT Answer FROM Answers ORDER BY AnswerOrder;

The query would then be something like this:

SELECT *
FROM Questionnaire
WHERE Answer = Forms!YourForm!cboAnswer;

Or if you want to make the parameter optional, returning all rows if no item
is selected in the combo box:

SELECT *
FROM Questionnaire
WHERE Answer = Forms!YourForm!cboAnswer
OR Forms!YourForm!cboAnswer IS NULL;

If the form containing the combo box is bound to this query, then in the
combo box's AfterUpdate event procedure you'd requery the form with:

Me.Requery

Or if it’s an unbound dialogue form then you'd open another form or report
based on the query, probably with a button on the dialogue form. You could
simply open the query in datasheet view, but a form or report is better,
enabling you to control the presentation of the data far more, and giving a
generally more professional appearance to the application.

Ken Sheridan
Stafford, England

 

[Ray S.]

I'm not certain that I understand your response. Maybe if I explain what I'm
trying to do: I have a table that contains one column that has what is there
identified as a "dimension_id." The table is enormous, containing over 7
million records. In order to query it efficiently, I first filter it by one
of three "dimension_ids." My idea is to create a little form for users that
have very little knowledge of queries. I want them to be able to select one
of the three from a drop down combo box. In the properties of the combo box,
I selected the row source type as a value list and then typed the three
option choices within quotes and separated each by a semicolon. I then have a
button on the form that runs a select query on the table. For the filtering
criteria of the table's "dimension_id" I typed [Forms]![Form1]![Combo1],
where Forms1 is the name of my form and Combo1 is the name of my combo. I
would expect that whichever option the user selects will filter the table
data to provide me only those records with the corresponding id. That is not
what is happening. Only one of the options works to filter the table. The
other two options return no results. As I wrote in my initial post, I am
certain there are no typographical errors. If I cut and past e the option
text and use it in a direct query without the form the expected results are
achieved.