FrJonathan
New member
- Local time
- Today, 02:14
- Joined
- Aug 23, 2019
- Messages
- 8
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.
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.
Attachments
Last edited: