A form to be filled in and then to be used for a query

[pschrader]

Hi !

I'm using MS Access 2003.

In my table I have yes/no fields F1 to FN.
In my query I want to select records from this table
according to the values of F1, ... , FN.
I want to use an entry form for specifying my query.

My entry form will have checkboxes for specifying the values of F1, ...,
FN.
A record is to be selected from my table whenever
it has "yes" values for all fields checked in the entry form.
(Unchecked fields are ignored .)

To carry out the query,
I would like to check the values in the entry form,
then push a button
to have another form show up which displays the selected records.

How can I do this in Access ?

Thank you very much for your kind help.

Peter

 

[John W. Vinson]

Hi !

I'm using MS Access 2003.

In my table I have yes/no fields F1 to FN.

In that case your table is incorrectly designed. Someday you'll need a N+1 and
what will you do? Alter your table, change all the queries that reference it,
change all your forms, change all your reports? OUCH!

If you have a one (record) to many (yes/no field) relationship, you're much
better off using THREE tables: your current table (less the checkboxes); a
table with N records, one for each current checkbox, with some identification
of what the checkbox means (what you're currently using as the labels of the
checkboxes); and a third table for answers, with links to the other two
tables. Rather than one *field* per answer, you will add one *record* per
answer.

In my query I want to select records from this table
according to the values of F1, ... , FN.
I want to use an entry form for specifying my query.

My entry form will have checkboxes for specifying the values of F1, ...,
FN.
A record is to be selected from my table whenever
it has "yes" values for all fields checked in the entry form.
(Unchecked fields are ignored .)

You can still do this with your current design by using an unbound form with N
unbound checkboxes, as a criteria form (let's call it frmCrit); and a second
form bound to a query on your table. The query should have a criterion on F1
field like

(Forms!frmCrit!F1 OR (Forms!frmCrit!F1= False))

and similarly on all N yes/no fields.

To carry out the query,
I would like to check the values in the entry form,
then push a button
to have another form show up which displays the selected records.

Just use the command button toolbox wizard to put a button on frmCrit opening
your bound form.

John W. Vinson [MVP]