complex (?) relationship with tables and forms... 1

[cuok]

Hi friends,
My project has 29+1 forms and 30 tables.

each record recognized by Id and VisitDate .

The "+1" is a form that created to include fields : 1. the ID (PK) from "Patients" table, 2. VisitDate from ClinicVisit table and an option group. I called the form "frmRashi".
each one of the 29 forms is a sort of medical test.

I created a sub form (not by the wizard) and called it "FormCategoria" somthing like a "Place holder" for 29 forms that will act as sub forms - each in its turn.
i know to do this in VBA code using the select statment and option group like this:
........
Case 6
FormCategoria.SourceObject = "Frm_Consultation"
Case 8
FormCategoria.SourceObject = "Frm_recommendation"
Case 7
.....
each form contains Id and VisitDate as duplicate index (yes) fields and the other fields from its table.

My Q. are:

do i must make 1:m relationship between patients table to each of the 29 ?
Or maybe not at all because they (29 forms) are sub forms ???

I will appreciate any help from you.
many thanks and c.u.o.k

 

[Steve101]

One of the reasons you set up the relationships, it that it provides an application independent way to protect the integrity of your data; that is, providing you use the Referencial Integrity capability associated with a relationship. Suggest that you read up on Referencial Integrity in online help, or in any good MS Access / database book. Also check up on Cascade Update and Cascade Deletion capabilities.

In your situation, you wont need to set up the relationships for your application to work; the relationship will be established via the LINK fields on the subform control.

Setting up a relationship diagram is ALWAYS a good idea in my book, as it is a step towards ensuring that your application is housed on a solid infrastructure; you do need to ensure though that the underlying data model is appropriate (do you collect different types of results for each of your 30 different tests?)

Hope this helps a little,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[cuok]

Hi Steve and many thanks for your answer.

I'll appreciate you if you can help me with this:

1.
You wrote: "you wont need to set up the relationships for your application to work" are you mean that subform (it's table) need not at all to related to the main form?

2.
do i must add in each subform (in "current" event)
"Me.ID=Forms!FrmMain!Id :Me.VisitDate=Forms!FrmMain!VisitDate" ?

Thank You Steve In Advanced

 

[Steve101]

As you've stated, the PK of the main Patients table is called ID. For clarity, lets rename this to PatientId (simply do this in design view of your table).

I'll assume that in each of the other MedicalTest tables you have a corresponding field called PatientTest. This is the foreign key which links the test back to the Patient.

Now to answer your questions:

1. Go into design view on the main form. Single Click on the subform control (from within the main form). View the properties of the subform control (options View, Properties). The third and fourth properties are called LinkChildFields and LinkMasterFields. Set both of these property values to PatientId. Note that in the above propery names, the 'master' one refers to a field in the the main form's recordsource; the 'child' one to a field in the subforms recordsource.
This process sets up the 'working relationship' between the two formss (ie. main and subform). It is ALL that is required to ensure that the data in the subform is synchronised with the data in the main form; ie. as you move through the data in the main form, the associated data in the subform should 'synch' based on the PatientId's associated with both tables.

2. No you dont need to provide any additional code. As I've described it, the synchronisation will be based on a PatientId field. Its of course possible for a patient to have multiple of the same test over a period of time (eg. a blood test,e very 6 months). In this case, the subform for that patient will return several records for the one patient. Within the form properties of your subform (ie. go into the subform's form in design view), play with the DefaultView and ViewsAllowed properties, which allows you to display the data in the form in 'ContinuousForm' mode, and in Datasheet mode. Both of these allow you to see multiple subform records at the same time.

Well, hope that gets you started. Get that going, then we can cover further re-organisation / filtering of the subform data.

Have fun,


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[cuok]

Thank You Steve,

You are helping me "Not cut corners..."

I'm going to spend this night (in Israel its 00:55) to do your advice.

i know about 'ContinuousForm' mode Datasheet mode,
but as i got in project i have to show each of the medicalTests on the subform that corresponding to main form only for the day on the main form and the other options ('ContinuousForm' mode Datasheet mode,) by bottons.

i tryed to link the MainForm and the subForm by: "PatientID;VisitClinicDate" for both-LinkChildFields and LinkMasterFields. Am i correct or I have to stay with what adviced me to link the forms only by "PatientID"?

Thank again Steve and I hope that You will be Patient With me (and With My English...) as till now.
Thanks again and C.U.O.K (My name is Eliahu)

 

[cuok]

SHLOM Steve,

I tryed now to link only with PatientID and i got the error: "Index or primarykey canot contain a null value" its posibly because the field "VisitClinicDate" is emty in view mode.

What i have to do that "VisitClinicDate" will take the same date from the Main form?

Cheers and C.U.O.K

 

[Steve101]

Hi cuok,

Sorry for the delay in replying. I've been a little busy, and seem to have missed this post. The reason you are getting the error above, is because, as you say, the VisitClinicDate is empty, and you've probably defined this field to be part of the primary key (or other unique key).

If you have a date field on the main form which you want to be used in the main form, then try the following:

(a) I'll assume that in the main form, the date is called VisitDate, and that this is an unbound control, and defaults to the current date.

(b) On the main form, create the above unbound text control, and set its default property to "=Date()" - dont include the quotes. Name this control VisitClinicDate

(c) Add the following code to the subform:
Private Sub Form_BeforeInsert(Cancel As Integer)
Dim F As Form: Set F = Me
F!VisitClinicDate = F.Parent!VisitClinicDate
End Sub

(d) What the above code will do, is whenever a new record is created in the subform, it will assign it the value of the VisitClinicDate entered in the main form.

(e) In the subform, its better to ensure that the TabStop property of the VisitClinicDate field is set to false. This way, you'll only enter this field when you specifically click into it.

Israel hey! I have a cousin in Haifa. Where about are you? Let me know via email address below if you like.

Hope this helps,
Cheers,
Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[cuok]

Shalom steve

I did it and its working perfectly !!!!!!!!!!!!

thanks a lot 1

cuok