Unanswered: Subforms and Queries and Join keys, Oh my!
I have been developing a legal database in Access 2000 that keeps track of all our clients and their respective cases. We have to track all possible conflicts for liability reasons so I set up the data table "contacts" to contain all parties to all cases we handle. I designed the database in order to have a way to look up any given case by the first or last name of the actual client, then to see at a glance the whole case, and all parties in that case, all on one page.
So my database uses several many-to-one and one-to many applications.The structure is something like this:
Contacts - contains all names of parties to our cases
File - Info on cases, keeps track of court case numbers, etc.
FileContacts - join table between contacts and files (multiple contacts with multiple cases, and multiple cases with multiple contacts)
Data entry form: query based on Contacts
First Subform: query based on File>FileContacts
Second Subform: Datasheet - Contacts>FileContacts
The main data entry form is based on Contacts because most often we need to search the cases by name.
The first subform is linked using the ContactID primary key for the File>FileContacts query. This form displays info on the court cases related to each contact.
For the second subform, I am using the FileID as the link, as this form lists all the parties related to each court case. This includes the contact on the main form. I want to be able to see at a glance all relevant info on any given case including all the parties involved.
OK, so here is my problem. I can enter new records into the datasheet subform all right. But I can't select existing parties from the drop down list I created for selecting clients who have used our services before.
It keeps telling me "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. I even placed a field from the "one" side in the datasheet in which I have to first enter a date before entering the Contact ID, but still no go.
What's weird about this was when I first created the database, this function worked perfectly. I don't recall having changed that much before it suddenly stopped working, so I have a feeling it's something really small that I must have changed without knowing it would affect everything, but I can't figure out what that might be.
This may be old hat to a lot of you here, so possibly someone might be able to help.