using filter to add new record..

[bb67dart]

I am working on a database where we are entering visit data for
patients.

On the main menu, we select the patient ID from a combobox. At that
point, we can press a button which has the standard linking code to
open a form and filter it for that specific patient. I have modified
the linking code to include not only the patient, but the visit date,
as criteria.

This works great, now we can use a button labeled "Re-screen
visit" (this is the 2nd visit) and it will launch the form for a
certain patient and that clients baseline date (using a DMax function
to get the 2nd visit date). The problem is if the client does not have
a rescreen record yet-- they only have a baseline.

If I use the patientID alone to open the form, it will show the
baseline visit data. If there is no rescreen visit date, I cannot use
that as part of the linking criteria. So I am attempting to modify the
stLinkCriteria to create a new record with the particular patient's ID
number, but with the date blank.

Here is the code I have tried, which runs on the condition that there
is only a single screening record (which is the baseline record),
therefore the goal is to create the rescreen record.

stLinkCriteria = "[PatientID]=" & "'" & Me![cboPatientID] & "' and
[intdt] = 0"

where cboPatientID is the combo box. The [intdt]=0 part is intended to
start with a blank date.

However, when I run this code, the form that opens is completely blank
-- not even the patient ID is filled in.

So in summary, is there a way to open the form using linking criteria
that will create a new record while filling in the patient ID? Thanks.

 

[Michael Gramelspacher]

I am working on a database where we are entering visit data for
patients.

On the main menu, we select the patient ID from a combobox. At that
point, we can press a button which has the standard linking code to
open a form and filter it for that specific patient. I have modified
the linking code to include not only the patient, but the visit date,
as criteria.

This works great, now we can use a button labeled "Re-screen
visit" (this is the 2nd visit) and it will launch the form for a
certain patient and that clients baseline date (using a DMax function
to get the 2nd visit date). The problem is if the client does not have
a rescreen record yet-- they only have a baseline.

If I use the patientID alone to open the form, it will show the
baseline visit data. If there is no rescreen visit date, I cannot use
that as part of the linking criteria. So I am attempting to modify the
stLinkCriteria to create a new record with the particular patient's ID
number, but with the date blank.

Here is the code I have tried, which runs on the condition that there
is only a single screening record (which is the baseline record),
therefore the goal is to create the rescreen record.

stLinkCriteria = "[PatientID]=" & "'" & Me![cboPatientID] & "' and
[intdt] = 0"

where cboPatientID is the combo box. The [intdt]=0 part is intended to
start with a blank date.

However, when I run this code, the form that opens is completely blank
-- not even the patient ID is filled in.

So in summary, is there a way to open the form using linking criteria
that will create a new record while filling in the patient ID? Thanks.

If I were doing this I would have tables thus:

CREATE TABLE Patients (
PatientID INTEGER NOT NULL PRIMARY KEY);

CREATE TABLE VisitTypes (
visit_code CHAR (1) NOT NULL PRIMARY KEY,
visit_name VARCHAR (20) NOT NULL);

CREATE TABLE PatientVisits (
PatientID INTEGER NOT NULL
REFERENCES Patients (PatientID),
Visit_date DATETIME NOT NULL,
visit_code CHAR (1) NOT NULL
REFERENCES VisitCodes (visit_code),
PRIMARY KEY (PatientID,visit_date,visit_code));

There would be no such thing as a blank visit. Your main form and subform would
work beautifully.