Form Field that Checks for Existing Primary Key Record & Add New Primary Key Record

[CourtGuy]

Hi Folks,

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)

[MMcCarthy]

You need a main form for Case Numbers with petitions as a subform. That way you can choose an existing Case Number and add or edit petitions or add a new case number and then add petitions to it.

[CourtGuy]

Thanks mmcarthy,

I've tried setting it up as a subform, but it still gives the same error message.

I think I need some kind of code that checks to see if the Case Number record exists in the first table, and, if it doesn't, automatically add it to that first table and then allow the user to make entries in the Petitions table.

[MMcCarthy]

Thanks mmcarthy,

I've tried setting it up as a subform, but it still gives the same error message.

I think I need some kind of code that checks to see if the Case Number record exists in the first table, and, if it doesn't, automatically add it to that first table and then allow the user to make entries in the Petitions table.

Have a form which opens prior to your main form and put the search on this instead. Behind your command button you could have something like the following:

Dim cNum As String
 
   cNum = nz(DLookup("[CaseNumber]", "TableName", "[CaseNumber]='" & Me!txtSearch & "'")," ")
   If cNum = " " Then
       DoCmd.OpenForm "FormName", , , , acFormAdd
   Else
       DoCmd.OpenForm "FormName", , , "[CaseNumber]='"  & Me!txtSearch & "'"
   End If
 

Mary