No one replied to my other thread, so I'm assuming it was poorly titled. Just to reiterate:
I have been developing a legal database in Access 2000 that keeps track of all our clients and their respective cases. This database uses a one-to-many-to-one application because we often have multiple clients for one case, or multiple cases for one client, and we have to keep record of the names of opposing parties for each case as well.
The structure of the database is as follows:
Contacts (one) - contains all names of parties
File (one) - stores court case numbers, type, etc.
FileContacts (many) - join table between contacts and files
Data entry form: query based on Contacts
First Subform: query based on File>FileContacts
Second Subform: Datasheet - Contacts>FileContacts
I created a main data entry form is based on Contacts because most often we need to search the cases by name. Within that form I set up a subform based on a join of File (1) > FileContacts (Many), with the join key being [FileContactsID], and the master and child fields being [ContactID].
The query uses a drop-down list to select court cases (or enter new ones) for each contact. This uses the [FileID] foreign key from the FileContacts table, which is the join key to the File table.
Now this form used to work perfectly, and I used to have a second subform that listed all contacts that pertain to that court case, and that one worked perfectly. Then I started getting the following:
"the current key must match the join key of the table in the 'one' side of the relationship..." etc. This has stumped me, since I do have a record in the "one" side, but it seems to not recognize it.
Now this is also happening in the first subform as well. When I try to assign an existing court case to an existing contact, I get the same thing. I don't understand what happened to have made them stop working, but obviously it was some little thing I may have changed. Both of the subforms work when entering new data. But I don't want to keep entering the same Contact several times, nor the court cases multiple times.
I'm not sure of the scheme. I would think that a table of cases (with a PK of case number, which can be alphanumeric), ditto for clients (PK of client number, which can be an autonumber), and a 3rd junction table with records as necessary for either multiple clients/contacts or, if you have a client in multiple cases simultaneously, multiple cases would be more straightforward. Each record of the junction table would comprise a FK of client, an FK of case, and additional information, such as whether the client in this case is the defendant, witness, etc. The actual case summary, if you must include it, should be in the case table.
The relationships between both of the first two tables to the junction table would be one-to-many.
With this scheme, you would need to enter three forms. After entering client and case summary info, you would use comboboxes on the third form, to call one client or case at a time, and add one record at a time to the third table.
I'm not sure that's more than a tweak from what you're doing now, but I never use many-to-one relationships. They're just too prone to be problematic.
Thanks. I had already configured it pretty much exactly as you said, but I checked to make sure! I think the problem was with something on the form itself, but I never figured out what.
I finally just created a new form and began with just the basics. It worked fine up until I added a datasheet subform to the Cases subform that shows all parties to the case. The problem is probably a conflict with the record on the main form, which is also to be listed there. I got around that by adding another subform for Cases and then added the same Parties datasheet. That's not a perfect solution either, because if you have multiple cases for one person and want to see both case info and all parties at once, you have to advance the records on both subforms. But that's better than nothing. I really don't need the datasheet subform to be available for data entry at all, just as a reference.
I wonder if newer versions of Access are better at solving complex relationships on forms?