Linking a Form to a Subform

[LindaLou]

I have a main form EVENTS which has a subform SYMPTOMS. These are linked as a Parent/Child relationship with the EVENTID being the common linked field.

Within the subform I have a command button which opens a third form called VITALSIGNS. The common field between these two forms is the SYMPTOMID field. I want the SYMPTOMID to autopopulate into the VITALSIGNS form when I click the command button in the subform SYMPTOMS. The control source for all the forms are based on queries. I tried pasting the following command into the VITALSIGNS query under the SYMPTOMID column however it did not work as I hoped:

[Forms]![frm_Symptoms_Subform].[Form]![SymptomID]

Currently when I click the command button the VITALSIGNS form opens but the SYMPTOMID field is not populated and must be entered manually to achieve the link to the SYMPTOMS subform.

Is it possible for a subform that is the child to a main form to also have a parent/child relationship to a third form? What is the best way to link the third form to the subform? The VITALSIGNS form can have multiple entries all tied to one SYMPTOMID and the SYMPTOM subform may have multiple SYMPTOMIDs all tied to one EVENTID.

I have built a report that will show all the linked Event/Symptom data by specific EVENTID and a report that will show only the VitalSigns data however cannot get the autolink to the SYMPTOMID.

Thanks for any help.

 

[PCLewis]

YOu can nest a subform in a subform if you wish to. There is nothing wrong with that in my view. If the data entry sub form (the Vital Signs one) is based on a query thne of course you might not be able to enter data in it depending on your query. Because you are using a form/subform relationship you don't need to base your forms on queries unless there is another reason.

So long as every symptom has one event and every vital sign has one symptom, the parent/child relationship of your forms and subforms will work nicely.

Having a subform does not grant any special benefits in terms of data entry though. If you want to open a data entry form, or even have the user populate a subform, then you will have to populate the values in code after the form is presented to a new record. If you are populating a subform and enter a symptom ID that is not the same as the symptom ID of your parent form, then once the date entry is complete, the record will "disappear".

 

[LindaLou]

Ok..nesting the third form into the subform instead of using a command button to access it does work and provides the Parent/Child link I was looking for to autopopulate the SYMTPOMID field. I also changed the control source of my forms to the tables intsead of a query since I agree I can't think of a good reason it needs to be based on a query.

My only issue now is that the reason I originally linked the VITALSIGNS form to a command button is because not every SYMPTOMID entry in the SYMPTOMS subform will require an entry in the VITALSIGNS form. I was trying to save on realestate space on the Main form by only viewing the SYMPTOMS subform and use a command button link to the VITALSIGNS form when needed.

Is there anyway I can keep the VITALSIGNS form as a nested subform within the SYMPTOMS subform and not view it until I need to enter data into it? Some kind of command button that will make the second subform viewable only when clicked? Thanks for your help on this so far since at least I now have the third form properly linked.

 

[ITchappie]

You can make your vital signs subform appear at will by using the VISIBLE property. Leave the Vitalsigns nested subform where it is and use the subforms properties to change the subforms VISIBLE parameter to NO (Default is always yes).

In your on click code for the button you simply add [VITALSIGNS].Visible = True to reveal and if you want to re-hide it at any point use [VITALSIGNS].Visible = False.

If you want to go one better if you have a symptom field that would trigger you wanting the Vitalsigns subform to appear then you can use that fields LOST FOCUS event to run the above [VITALSIGNS].Visible = True subroutine so that the user does not have to click a command button!