I've got a problem that's been confounding me for months.
I work for a criminal court, and I'm trying to set up an Access database to track petitions filed by criminal defendants.
I am using the court's case file number record as the primary key to make sure the database doesn't wind up with multiple entries for the same case number.
However, a single case number can have other defendants charged in the same case number who can also file a petition.
As a result, I have a database tree that looks kind of like this:
Defendant#1 -- Petition -- FilingDate, etc.
/CaseNumber
\
Defendant#2 -- Petition -- FilingDate, etc.
I have setup a table (Table: Case Number) that contains all the case number records, and it has a "one-to-many" relation to another table (Table: Petitions) that tracks each defendant's petition under each case number record. Remember that I'm using all the case number records as the primary key.
There are too many users and too many case numbers for any individual user to know whether or not a particular case number record has already been entered (as a primary key) into the Case Number Table.
So, when a user gets a new petition to enter into the system, I need a form that will do two actions:
1. Let the user enter in the petition's case number into a field that will check to see if there is a matching case number record in the Case Number Table. If there is, then it should bring up the associated record, and allow the user to add or update a petition.
AND
2. Let the user enter in the petition's case number into a field that will check to see if there is a matching case number record in the Case Number Table. If there is NOT a matching case number record, then it should allow the user to automatically add the case number as a new record in the Case Number Table. Then, it should allow the user to enter in the new petition.
I've got action #1 going just fine. My problem is getting action #2 to work. On all the forms I have created, whenever the user enters in a new case number record that IS NOT already a record in the Case Number Table, I get an error message that says: "You can't add or change a record because a related record is required in table 'Case Number'"
I'm relatively new to MS Access, and I'm definitely a newbie when it comes to programming in Visual Basic. So you may need to walk me through this using baby steps...
Thanks (in advance),
Neil (aka CourtGuy)