P
pietlinden
I have a database with the following where I am tracking Patients,
Studies, and Enrollments (in the Studies). Because a Patient has to
be enrolled in a study before we are interested in him from a data
collection standpoint, I created the Enrollments table using a
procedure I borrowed from Allen Browne (shameless borrower I am!) that
uses DDL to create a unique index on the combination of (PatientID,
StudyID) and a Primary Key of Enrollment ID (basically a single key PK
that is a surrogate for the (PatientID, StudyID) combination.
Patient---(1,M)---Enrollment---(M,1)----Study
Enrollment--(1,M)---SAEReport----(1,M)---Symptom
Here's the code I used to create the table...
Sub CreateEnrollmentDDL()
'Purpose: Create a unique index on (PatientID, Protocol) in the
Enrollment table
Dim cmd As New ADODB.Command
Dim strSQL As String
'initialize
cmd.ActiveConnection = CurrentProject.Connection
'---drop the existing table
strSQL = "DROP TABLE Enrollment;"
cmd.CommandText = strSQL
cmd.Execute
strSQL = "CREATE TABLE Enrollment " & _
"(EnrollmentID COUNTER CONSTRAINT PrimaryKey PRIMARY KEY, " & _
"Protocol TEXT(15) NOT NULL, " & _
"PatientID LONG NOT NULL, " & _
"CONSTRAINT PatientEnrollment UNIQUE (PatientID, Protocol));"
cmd.CommandText = strSQL
cmd.Execute
Debug.Print "table created"
End Sub
whenever I try to add records to a child table of this one
(AdverseEvent) through a sfrmAdverseEvent (child of Enrollment), I get
the following error message:
Error 'Error evaluating CHECK constraint.' in the validation rule.
I understand what it means, but I have no idea why it's happening. I
*thought* the enrollment record was already created. (If necessary, I
create it in code behind the scenes). Do I need to requery a table or
something? I'm baffled. Or just force the parent "enrollment" record
to save first?
If you need any clarification, please ask! Since the parent records
exist, I have no idea why the check constraint is being violated... is
this a sequence thing where I need DoEvents to force things to happen
in a specific order?
Thanks!
Pieter
Studies, and Enrollments (in the Studies). Because a Patient has to
be enrolled in a study before we are interested in him from a data
collection standpoint, I created the Enrollments table using a
procedure I borrowed from Allen Browne (shameless borrower I am!) that
uses DDL to create a unique index on the combination of (PatientID,
StudyID) and a Primary Key of Enrollment ID (basically a single key PK
that is a surrogate for the (PatientID, StudyID) combination.
Patient---(1,M)---Enrollment---(M,1)----Study
Enrollment--(1,M)---SAEReport----(1,M)---Symptom
Here's the code I used to create the table...
Sub CreateEnrollmentDDL()
'Purpose: Create a unique index on (PatientID, Protocol) in the
Enrollment table
Dim cmd As New ADODB.Command
Dim strSQL As String
'initialize
cmd.ActiveConnection = CurrentProject.Connection
'---drop the existing table
strSQL = "DROP TABLE Enrollment;"
cmd.CommandText = strSQL
cmd.Execute
strSQL = "CREATE TABLE Enrollment " & _
"(EnrollmentID COUNTER CONSTRAINT PrimaryKey PRIMARY KEY, " & _
"Protocol TEXT(15) NOT NULL, " & _
"PatientID LONG NOT NULL, " & _
"CONSTRAINT PatientEnrollment UNIQUE (PatientID, Protocol));"
cmd.CommandText = strSQL
cmd.Execute
Debug.Print "table created"
End Sub
whenever I try to add records to a child table of this one
(AdverseEvent) through a sfrmAdverseEvent (child of Enrollment), I get
the following error message:
Error 'Error evaluating CHECK constraint.' in the validation rule.
I understand what it means, but I have no idea why it's happening. I
*thought* the enrollment record was already created. (If necessary, I
create it in code behind the scenes). Do I need to requery a table or
something? I'm baffled. Or just force the parent "enrollment" record
to save first?
If you need any clarification, please ask! Since the parent records
exist, I have no idea why the check constraint is being violated... is
this a sequence thing where I need DoEvents to force things to happen
in a specific order?
Thanks!
Pieter