Using Checkboxes with Parameter Queries

[Jon]

I am trying to create a parameter query that connects to
a form that uses checkboxes as the method for selecting
records of individuals based on the sports that each
person plays. For instance, the form would consist of
three fields: Golf, football, and basketball (all
checkboxes).

The companion Parameter Query is based on a
table, "PersonSports," with these three fields plus the
person's ID number as the primary key. So the
table consists of 4 fields all together: PersonID, Golf,
Football, and Basketball. In the criterion section for
Golf, there is the expression [Forms]![PersonSearchForm]!
[Golf], indicating that the query should look to
the "Golf" field of the "PersonSearch" form for the value
to be used as the filter for pulling records. For the
other two fields I have the same expression except the
last part is altered to the appropriate field.

On the "PersonSearchForm" form, I check the golf checkbox
and leave the other checkboxes blank so as to pull up all
records for individuals who play golf. However, the
query returns only one record, and there is more than one
record with the value golf in the golf field. I know
that I have made a mistake in setting up the query and
form.

Does anyone know how to use checkboxes on forms that
connect to parameter queries? Ideally, I would like to
search for records using multiple fields, i.e. by
checking off football and golf and retrieving all
records that include these values, or by having an option
where selecting football and golf pulls up only those
records where a person plays both of these sports.

Sorry for the long message!!!

-Jon

 

[Tom Wickerath]

Hi Jon,

I know that I have made a mistake in setting up the query and form.

Actually, I hate to tell you this, but you have made a mistake setting up the design of your
database. You should not have separate fields for golf, football and basketball. Think about
the work you will face in the future if you want to add more sports in the future, such as soccer
and baseball. Your current design will require adding new fields to the table. Then, you'll have
to modify your queries, forms and reports to accommodate this new data.

You have a many-to-many relationship between people and sports played. You should create three
tables minimum to model this relationship:

tblPeople
pkPersonID (primary key)
FirstName
LastName
+
Any other attributes about the people that you wish to track

tblSports
pkSportID (primary key)
SportName
+
Any other attributes about the sport that you wish to track

tblPersonSports
PersonSportID (primary key)
fkPersonID (foreign key)
fkSportID (foreign key)

You can set a multi-field index on the two foreign keys, with no duplicates allowed, to prevent a
person and sport from being entered twice.

This design will let you add or remove sports without the need to modify any objects (tables,
queries, forms, reports, etc.).

For your original question, I suggest using a multi-select listbox on an unbound search form, in
which you can display the various sports. This will allow the user to select one or more sports.
If you use the checkbox design, you'll need to modify the form if you later add or remove a sport
(unless you use a subform with checkboxes). The trick is to use VBA code to build the WHERE
portion of the SQL statement in code. It's not all that hard to do once you've studied some
working examples. If you send me a PRIVATE e-mail message, I will send you a sample database
that includes the VBA code required to interrogate listbox selections. Whatever you do, do not
post your real e-mail address in a reply to the newsgroup. My e-mail address is pretty easy to
figure out if you attempt to use the reply function.

Tom
_____________________________________


I am trying to create a parameter query that connects to
a form that uses checkboxes as the method for selecting
records of individuals based on the sports that each
person plays. For instance, the form would consist of
three fields: Golf, football, and basketball (all
checkboxes).

The companion Parameter Query is based on a
table, "PersonSports," with these three fields plus the
person's ID number as the primary key. So the
table consists of 4 fields all together: PersonID, Golf,
Football, and Basketball. In the criterion section for
Golf, there is the expression [Forms]![PersonSearchForm]!
[Golf], indicating that the query should look to
the "Golf" field of the "PersonSearch" form for the value
to be used as the filter for pulling records. For the
other two fields I have the same expression except the
last part is altered to the appropriate field.

On the "PersonSearchForm" form, I check the golf checkbox
and leave the other checkboxes blank so as to pull up all
records for individuals who play golf. However, the
query returns only one record, and there is more than one
record with the value golf in the golf field. I know
that I have made a mistake in setting up the query and
form.

Does anyone know how to use checkboxes on forms that
connect to parameter queries? Ideally, I would like to
search for records using multiple fields, i.e. by
checking off football and golf and retrieving all
records that include these values, or by having an option
where selecting football and golf pulls up only those
records where a person plays both of these sports.

Sorry for the long message!!!

-Jon