How do I select multiple rows of data on a form

[Al]

I have a query tied to a "Selection Form". This is the query:

SELECT No AS SelectCand, CandName AS Name, CityCand AS City, StateCand AS
ST, CandidateID
FROM tblCand
ORDER BY CandName, StateCand, CityCand;

My goal is to present a form to the user where they select one or more names
that then becomes the criteria for another form search. Selecting the names
is the problem.

When I put a CheckBox in the detail section of the "Selection Form" and tie
it to the query field "SelectCand" the CheckBox does not work.

If I select SelectCand from the field list for the form Access creates a
TextBox--which could be turned into a ComboBox; however, I would like to use
a CheckBox on the form for the selection.

How can I put a CheckBox on this form that will link to the data in the
rowsource of the form and allow this type of selection? Or is there a better
way to accomplish this?

Thanks for your replies!!

 

[Dirk Goldgar]

I have a query tied to a "Selection Form". This is the query:

SELECT No AS SelectCand, CandName AS Name, CityCand AS City, StateCand AS
ST, CandidateID
FROM tblCand
ORDER BY CandName, StateCand, CityCand;

My goal is to present a form to the user where they select one or more
names
that then becomes the criteria for another form search. Selecting the
names
is the problem.

When I put a CheckBox in the detail section of the "Selection Form" and
tie
it to the query field "SelectCand" the CheckBox does not work.

If I select SelectCand from the field list for the form Access creates a
TextBox--which could be turned into a ComboBox; however, I would like to
use
a CheckBox on the form for the selection.

How can I put a CheckBox on this form that will link to the data in the
rowsource of the form and allow this type of selection? Or is there a
better
way to accomplish this?

Thanks for your replies!!

Your query defines SelectCand as a calculated field with the value No (=
False = 0). So no matter what you do or what type of control you bind it
to, you're not going to be able to change the value of that field. The
easiest way to handle this situation is to define the field SelectCand in
the table itself, as a yes/no field. Then you can simply include that field
in your query, and bind a check box to it.

Bear in mind, though, that if this table is shared by multiple users who
might be making selections in this table at the same time, their selections
could override each other. In a situation like that, the simple solution
won't work and a different approach must be taken. One such approach is to
use a multiselect list box to build a criteria string to be incorporated in
a code-built SQL statement; another approach is to use a user-specific
table of the key fields of selected records.

 

[Al]

Thanks Dirk,

I now understand why I cannot use a CheckBox on a Calculated field in the
manner in which I was attempting.

To resolve this problem I created a new Selection table from the query and
tied the form to this table which allows the CheckBox to work.

I populate this table on the fly with an append query and when I close the
form I dump the contents of the table so the next time it is blank for the
next select.

In a multi-user client/server environment this should work fine since
everyone has their own verson of the application and this particular table is
a local table.

thanks Again!!

 

[Dirk Goldgar]

Thanks Dirk,

I now understand why I cannot use a CheckBox on a Calculated field in the
manner in which I was attempting.

To resolve this problem I created a new Selection table from the query and
tied the form to this table which allows the CheckBox to work.

I populate this table on the fly with an append query and when I close the
form I dump the contents of the table so the next time it is blank for the
next select.

In a multi-user client/server environment this should work fine since
everyone has their own verson of the application and this particular table
is
a local table.

Very good. That sounds like a fine solution.

thanks Again!!

You're welcome.