Multiple Checkboxes

[LeAnn P via AccessMonster.com]

Hello,

I'm not sure if I have structured my tables correctly but here is what I have and what I want to do. I have patient info table and a antibody list table. The antibody list table is a list of possible antibodies using an autonumber identifier and antibody text field. Patients can have multiple antibodies so I have a junction table with PatID and AntibodyID fields.

I have a main form for patient info. I want to click a button to open another form (I assume an unbound form) with all the possible anitbodies as checkboxes. After user clicks all relevent antibodies, click save. I want to insert records into the junction table based on what the user checked. (I will then display in a list box on the main form the patient's antibodies).

Can anyone make sense of what I'm looking for and help me?
Thanks!!

 

[LeAnn P via AccessMonster.com]

I achieved my desired outcome by creating just 2 tables: Patient Info and PatientAntibodies. In the Antibody table I included all 21 antibodies as y/n fields and FK of PatientID. From there I can create a popup for for the users to see and check all relevent antibodies for a patient.

Two things with this approach is:
1) this is not normalized and therefore wastes storage space
2) I don't know how to simply list through code what antibodies a patient has in the unbound list box that I have. I don't have enough room to put a full-blown subform. I want to display the "caption/label" of any checked antibodies (on the Patient form) in that listbox.

Any comments or suggestions?
BTW, hate to admit it but I'm using Access 97

 

[John Vinson]

I achieved my desired outcome by creating just 2 tables: Patient Info and PatientAntibodies. In the Antibody table I included all 21 antibodies as y/n fields and FK of PatientID. From there I can create a popup for for the users to see and check all relevent antibodies for a patient.

Two things with this approach is:
1) this is not normalized and therefore wastes storage space
2) I don't know how to simply list through code what antibodies a patient has in the unbound list box that I have. I don't have enough room to put a full-blown subform. I want to display the "caption/label" of any checked antibodies (on the Patient form) in that listbox.

Any comments or suggestions?
BTW, hate to admit it but I'm using Access 97

A97 is still probably the best version out there. If you don't need
the features (mainly web stuff) of 2003, don't bother upgrading until
forced to do so!

Storing data in fieldnames (21 antibody fields) is indeed not
normalized and is a bad idea. I'd strongly suggest three tables:
Patients; Antibodies (a 21 row table to which you can add new
antibodies as needed); and PatientAntibodies, with the patientID and
the antibody ID or name.

A Subform can be of any size - the size of a listbox for example. You
could have a continuous subform with just one field, the antibody
name, as a combo box; use the PatientID as the master/child link field
to display just that patient's records.

You can do it from the yes/no fields and the listbox if you really
insist... but IMO it's a much more difficult and less flexible
approach than the normalized database with a subform!

John W. Vinson[MVP]