Hi John, me again!!
If you willl recall the other topic about deleteing records from a sub form,
the same archetecture exists in the merit badge thread. The Table
Merit_Badge contains the list of valid merit badges with an auto_number Key.
Another table contains the info on the counselor (name, address, phone, etc.)
with his Person_ID (the key). The third table (just as with schools and
units ) contains the key pair of MB_code and Person_ID. (as a side this
relationship has the same problem as Schools - Units, you can't delete the
record uning the sub form) This creates the linkage between the counselor and
his merit badges, once again the key fosters the creation of a many to many
relationship where there can be many counselors for a Merit_badge and mant
Merit_badges for a Counselor but each relationship must be unique, so
duplicate records are not permitted.
I hope this will satisfy the third table requirement, now this issue is how
do i get the selected merit badge and list only counselors who do that marit
badge or conversely lsit all merit badges that a given counselor is qualified
for.
Create a Query joining the three tables. Use a criterion on the merit badge
specifying which badge you're interested in, and include the CounselorID or
CounselorName in the query. This will show only those counselors who have
records in the resolver table for that badge.
This has nothing to do with the inability to delete the record, which makes no
sense to me if the structure is as you describe it, unless the Subform's
AllowDeletes property is turned off. What error do you get when you try to
delete a record?
I'd be willing to take a look at the database if it's not confidential; if you
would like me to do so please use Compact and Repair to compact it, put it in
a zipped folder and email it to me at jvinson <at> wysard of info <dot> com.
There's something about it that isn't getting communicated on this forum!
