Form with Finite selectable records.

[Guest]

Here is my question:

How do I create a form or subform where I am able to show all records from a
table with a checkbox which then assigns all of these items to the record
from another table -- all while keeping the form aesthetic and user-friendly?

This is what I have:

tblAgency
-------------
AgencyID
AgencyName
luStateID (lookup)
....

tblStates
------------
StateID
StateName
StateAbbreviation

This is what I want:

I am trying to create a subform for use on a tab. When the user goes to
that tab, there are 51 checkboxes (don't forget D.C.!); one checkbox for each
state. The user assigns the state or states to the agency, then moves on to
the next tab.

O Alaska O Illinois
O Alabama O Indiana
O Arkansas O Kansas

I believe this gives a clear idea of what I am trying to do.

How do I do this? THANK YOU!

 

[strive4peace]

you will need a table something like this:

*AgencyStates*
AgStateID, autonumber
AgencyID, long integer -- FK to tblAgencies
StateID, long integer -- FK to tblStates

then, the RecordSource for your subform would be something like this:

SELECT
s.StateID
, s.Statename
, s.StateAbbreviation
, IIF(IsNull(AgS.AgStateID), false, true) as IsAssigned
FROM tblStates as s LEFT JOIN AgencyStates as AgS
ON s.StateID = AgS.StateID;

the form will not be able to be edited directly, but what you can do is
use the click event of the checkbox (whose ControlSource is IsAssigned)

in the code behind the subform:

if IsAssigned is not checked and becomes checked, use SQL to append a
record to the AgencyStates table

if IsAssigned is checked and becomes unchecked, use SQL to delete the
record from the AgencyStates table

then requery the (sub)form


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*