Linking forms to have same ID

[AccessNovice]

I have three different forms that can be accessed through a main switchboard.
Each form is connected by a patient ID number. I would like the ID to carry
over to each form so the person entering the data doesn't have to keep
putting it in. Every table is connected by this patient ID (which acts as the
primary key). There is a one-to-one relationship for three tables connected
with each of the other tables. I would have created one big table all based
on the patient ID, but that would be too many fields. How can I have the
patient ID show up so that record 1 on form 1 has the same patient ID
automatically as record 1 on form 2, 3 and so on???
Thanks, any help would be greatly appreciated!

 

[KARL DEWEY]

It sounds like you have not set up your data as a relational database but as
a spreadsheet.
A relational database would have the patient in a one-to-many relation to
visits. Then a doctor table as one-to-many to the visit.

 

[AccessNovice]

The database is for a study involving dogs that have mammary cancer. The
veterinarian has submitted a biopsy and we have sent them the pathology
report. Now we want the veterinarian to fill out a follow up questionaire.
There are 4 tables: patient information (which we already have), veterinary
information, Initial tumor information, and follow-up information. It is
rare that a veterinary clinic will have more than one patient - so it really
is a one-to-one relationship, in the sense that each veterinarian has one
patient, and so there is only one record of everything else. Is this wrong?
and still, can I connect the three forms? or should I just create one big
table?
Thanks for your help!

 

[Albert D.Kallal]

The easy approac is to use a tab contorl, and for each tab, you palce a
sub-form.

If you need to launch another form to the same id, then go

me.Refresh ' this forces a disk write...a very good idea!!
docmd.OpenForm "nextformtoopen",,,"Patienid = " & me!Patientid


You should make this other form model (use the other tab), so that the user
closes this form when done to return back tot he main patient form.

Further, you will need to check if the record already exists in the child
form BEFORE you launch the form. IF the child form does not have the
record...then add it...

hence, the code would actually look like

dim rstCheckExist as dao.RecordSet
dim strSql as string
me.Refresh ' this forces a disk write...a very good idea!!

strSql = "select * from childTable where PatiendId = " & me!PatientID
set rstCheckExist = currentdb.OpenRecordSet(strSql)
if rstCheckExist.RecordCount = 0 then
rstCheckExist.AddNew
rstCheckExist!PatiendID = me!PatientID
rstCheckExist.Update
end if
rst.Close
set rst = nothing

docmd.OpenForm "nextformtoopen",,,"Patienid = " & me!Patientid