Trouble with subform

[JChandler22]

View attachment db2.zip

Let me preface by mentioning that I'm new to access and kind of struggling through it at the moment...

In the attachment, you'll see my main form "Contacts." On the 'Program Interest' tab, I have a subform so that the user can select the program(s) that apply to the contact. At this point, however, the user could select the same program more than once.

How would I have access not allow more than one of the same program? Or, better yet, how would I have access modify the combo box as programs are selected (ie if Program Green is selected, remove Program Green from the list from thereon)?

If my formatting is off altogether, please let me know! Again, this is all very new to me.

Many thanks.

 

[JChandler22]

And by the way, records 2 and 3 (Robin Counts & John Frum, respectively) are better examples than record 1. Take a look at those to see what I mean.

Thanks again!

 

[fbsrcchaos]

Quick&dirty:

Go to design view table "Program_Contacts"

Select both "ContactID" and "Program" rows and click on Primary key. This defines the combination of both fields as primary key, thus multiple entries of the same program under the same contactID are prohibited.
Since this rule is violated by some entries in the table, you'll have to delete these first in order to do this change.

 

[JChandler22]

Ok great, thanks for the advice. That's working much better.

Conversely, would there be a way to make that subform instead be a bunch of true/false checkboxes, each with a label (each program) next to it? I think that might be a more effective route, if it could be done.

 

[fbsrcchaos]

You want to be able to add more programs later, therefore the checkbox approach might be a bit tricky, since it must be stored in the database structure (yes/no for each existing program must be stored).