Drawing data from combo box in subform (1 Viewer)

I have a parent form, frm_InputVets2, with a child form, frm_InputNOK_2.

The parent form is for entering data related to a deceased military Veteran. The child form is for entering data related to one or more next-of-kin of the Veteran.

I also have a table, tbl_Input_Relat_Pt_NOK with four fields.
AutoID, Pt_Sex, NOK_is_to_Pt, and Pt_is_to_NOK.

Sample entries are in an attached screenshot.

In frm_InputVets_2.Vet_Sex, the user will select the sex of the deceased Veteran, from a list that is inherited from the table's field parameters. The control is bound to the field tbl_Veterans.Vet_Sex.

In frm_InputNOK_2.NOK_VET_Relationship, the user will select from a list the relationship of the Next of Kin to the deceased Veteran. (This information is found in the Veteran's medical record. The user selects from a list selected by this SQL statement in the Row Source property of the control.
SELECT DISTINCT tbl_Input_Relat_Pt_NOK.NOK_is_to_Pt FROM tbl_Input_Relat_Pt_NOK ORDER BY tbl_Input_Relat_Pt_NOK.NOK_is_to_Pt;
The control is bound to the field tblNextofKin.NOK_Vet_Relationship.

All that is working fine, so far. It's the next step that causes the problem.
The next control is for inputting the reciprocal relationship of the Veteran to the Next of Kin. (E.g. if the Veteran is female, and the next of kin is her daughter, then the Veteran is the mother.) Unfortunately the user (an elderly volunteer) tends to fumble that particular mental calculation, so I want the control to calculate the reciprocal relationship instead of relying on the user to figure out it.

So, I want the control, frm_InputNOK_2.cbo_Vet2NOK_Relat to draw the Veteran's sex from frm_Input.Vets2.Vet_Sex, and the relationship of the next of kin to the Veteran from frm_InputNOK_2.NOK_VET_Relationship, and filter the table, which will produce one and only one option that can be selected to enter in the control.

The SQL statement in the Record Source property is
SELECT DISTINCT tbl_Input_Relat_Pt_NOK.Pt_is_to_NOK FROM tbl_Input_Relat_Pt_NOK WHERE (((tbl_Input_Relat_Pt_NOK.Pt_Sex)=Forms!frm_InputVets_2!Vet_Sex) And ((tbl_Input_Relat_Pt_NOK.NOK_is_to_Pt)=Forms!frm_InputNOK_2!NOK_VET_Relationship)) ORDER BY tbl_Input_Relat_Pt_NOK.Pt_is_to_NOK;
The control is bound to the field tblNextofKin.N_VET2NOK_Relat.

I don't really need it to SELECT DISTINCT, because it really does come down to only one option. However, the problem is that the SQL statement will NOT draw the parameter from the control frm_InputNOK_2.NOK_VET_Relationship. It prompts me for that parameter, instead.

It seems the key problem is that I can't get a query to draw a parameter from a subform.

Strangely, when I typed the next-of-kin relationship into the parameter prompt, I still didn't get the list I wanted... I got a blank list, as if one of my parameters had no match in the data field.

Any suggestions?

Be aware I'm very much a beginner so I will likely need step-by-step instructions.

Hi. Are you able to post a sample copy of your db?

the subform is not called property so it is asking for parameter:

arnelgp said:

the subform is not called property so it is asking for parameter:

Click to expand...

@arnelgp, I think you have misunderstood me.

"The SQL statement in the Record Source property is
SELECT DISTINCT tbl_Input_Relat_Pt_NOK.Pt_is_to_NOK FROM tbl_Input_Relat_Pt_NOK WHERE (((tbl_Input_Relat_Pt_NOK.Pt_Sex)=Forms!frm_InputVets_2!Vet_Sex) And ((tbl_Input_Relat_Pt_NOK.NOK_is_to_Pt)=Forms!frm_InputNOK_2!NOK_VET_Relationship))
ORDER BY tbl_Input_Relat_Pt_NOK.Pt_is_to_NOK;
The control is bound to the field tblNextofKin.N_VET2NOK_Relat."

I was referring to the Record Source property on the control's property sheet, I wasn't referring to any Access object named "property".

The SQL statement has no trouble pulling the Veteran's sex from the form
Forms!frm_InputVets_2!Vet_Sex
but it can't pull the next-of-kin relationship to the Veteran from the subform
Forms!frm_InputNOK_2!NOK_VET_Relationship

theDBguy said:

Hi. Are you able to post a sample copy of your db?

Click to expand...

It took some doing to strip it down to a size that the forum would accept, but here it is. I also had to strip out all the Veterans and Next of Kin information due to privacy laws, so there's no personal information in the tables.

Boy do I feel dumb.

I've been trying to get the deceased patient's relationship to the next of kin into tblNextofKin, so that I could extract it to make a customized message on condolence mailers.

E.g. "Dear Jenny, I was so sorry to hear of the death of your father, Bill."

I just realized I don't NEED it to be in tblNextofKin. I made a query that will gather into one place all the information I need to schedule and print the customized messages, and used the query to select the relationship of the deceased Veteran to the Next of Kin from a table I created for that purpose.

It selects the correct Veteran to Next of Kin relationship each time I run the query, with no need to store the Veteran to Next of Kin relationship. ... And unlike everything else I've been trying that OUGHT to work but doesn't... this one seems to work.

SO... although I'm still interested to learn whether it actually is possible to draw source data from a subform... for this particular part of the application, I don't have to.

Good grief! I've been beating my head against the wall for MONTHS trying to figure this out, and it was SOOO easy. Once I realized it could be done without storing the Veteran to Next of Kin relationship in tblNextofKin, it took just minutes.

Fr. Jonathan