Subform problem

[sk88]

yes.. basically tblAudit that I have right now in my example will be the table for patient info.



So which means.. A patient can have more than one STUDY but each study can have only one patient.

A.M is considered as ONE STUDY
P.M is considered as ANOTHER STUDY

that is how I have setup earlier.
but I think I am wrong...

do you have any ideas? I am still working on the video examples

[sk88]

Yes, one form set up to allow edit of only the info from Table. Form would open filtered to the correct study and period. The RecordSource for the form would be a query that joins the tables (like the Search form), but only the controls bound to Table fields would be set to allow edits. I would probably make one form to serve as both search and edit. This tutorial shows what I mean http://datapigtechnologies.com/flash...tomfilter.html.

Clarify relationship of patients to studies - one-to-one, one-to-many, many-to-many?

Some how I am not getting it

[June7]

That's not quite how you have it setup. The tblAudit has StudyID as primary key, so this is the 'study' record. Related table Table has related period data records. (Really need a better name than 'Table').

So the studyID is really a single patient. The patient IS the study. And each study/patient will have 3 period records.

A patient can be in tblAudit only once?

[sk88]

That's not quite how you have it setup. The tblAudit has StudyID as primary key, so this is the 'study' record. Related table Table has related period data records. (Really need a better name than 'Table').

So the studyID is really a single patient. The patient IS the study. And each study/patient will have 3 period records.

A patient can be in tblAudit only once?

I would say no. Because each STUDY ID is unique and cannot be repeated.
and you are right about STUDYID is really a single patient and each patient/study will have 3 period records.

[June7]

You answered NO to 'Patient can be in tblAudit only once?'. Technically, that is correct but do you WANT to allow patients in this table more than once?

The StudyID is unique because it is Autonumber. Nothing prevents duplication of patient names in tblAudit.

[sk88]

true. I was actually going to add another field later to the table the number that is usually assigned to the patient to identify them

[sk88]

back to my example that I have posted..
in my subform, I have added the field SUBID to it and which is invisible.

Is there a way to link the main form ID with the subform ID so that it will display the correct record?

like a checking system or filter?

[June7]

Still doesn't clarify. Whether it is patient name or an ID number, do you want to allow patient in tblAudit more than once? If yes, table structure I understand is:

tblPatients
PatientID (primary)
name and other info fields

tblStudies
StudyID (primary)
PatientID (foreign)
study name and other info fields

tblObservations
ObsID (primary)
StudyID (foreign)
abc (what is this?)
DateObs
Period (am, pm, lunch)
Comment
Address (why is this here?)

[sk88]

Still doesn't clarify. Whether it is patient name or an ID number, do you want to allow patient in tblAudit more than once? If yes, table structure I understand is:

tblPatients
PatientID (primary)
name and other info fields

tblStudies
StudyID (primary)
PatientID (foreign)
study name and other info fields

tblObservations
ObsID (primary)
StudyID (foreign)
abc (what is this?)
DateObs
Period (am, pm, lunch)
Comment
Address (why is this here?)

Yes.. that is what I want basically.. the kind of structure I will be looking at

[sk88]

I am just having trouble in linking them in the search forms. I hope you will have better idea as to how I should do it

[June7]

Here is example of two forms for data entry/edit. This reflects the new table structure described above.

It is best practice to flesh out data structure (tables and relationships, fields) before trying to build forms and reports. That's why I asked so many questions about data structure. So here is another. I noticed the 3 Period records were not all in one day. Will this be normal case?

EDIT: Purpose served, file removed.

[sk88]

Thanks June!

the 3 period records could be in one day too.

I am going to look at your example now.

[sk88]

That is very close to what I am looking for.
Is there a way that instead of drop down menu for select record in Observation form be like the search as you type function or is it not possible?

[June7]

What would you like to type in the combobox - patient lastname, firstname? Change the Row Source to:
SELECT Observations.ObsID, [Patients].[LastName] & ", " & [Patients].[FirstName] & " | " & [Studies].[StudyID] & " | " & [Observations].[Period] AS Expr1, Studies.StudyID, Observations.Period
FROM (Patients INNER JOIN Studies ON Patients.PatientID = Studies.PatID) INNER JOIN Observations ON Studies.StudyID = Observations.StudyID
ORDER BY Patients.LastName, Studies.StudyID;

Now can type patient lastname, firstname and the combobox will auto fill.

Something I forgot to include. In the combobox GotFocus event add this line: Me.cbxObs.Dropdown

Now the list will automatically show when box gets focus and can use up/down cursor keys.

Can also move the combobox and button into the Detail section and set the combobox TabIndex to 0 so it will have focus when the form opens.

[sk88]

What would you like to type in the combobox - patient lastname, firstname? Change the Row Source to:
SELECT Observations.ObsID, [Patients].[LastName] & ", " & [Patients].[FirstName] & " | " & [Studies].[StudyID] & " | " & [Observations].[Period] AS Expr1, Studies.StudyID, Observations.Period
FROM (Patients INNER JOIN Studies ON Patients.PatientID = Studies.PatID) INNER JOIN Observations ON Studies.StudyID = Observations.StudyID
ORDER BY Patients.LastName, Studies.StudyID;

Now can type patient lastname, firstname and the combobox will auto fill.

Something I forgot to include. In the combobox GotFocus event add this line: Me.cbxObs.Dropdown

Now the list will automatically show when box gets focus and can use up/down cursor keys.

Can also move the combobox and button into the Detail section and set the combobox TabIndex to 0 so it will have focus when the form opens.

Thanks June! I will work on it later today... and will let you know how it went