Referring to Subform Control in a Conditional If Statement

[dgj32784]

I'm finally getting back into my database design after months of other "special project" distractions and needing to knock the dust off of my novice VBA. I have a form and associated subform. I have a command button on the form that has an On Click procedure to determine if values in two of the fields on the subform are valid. This subform is essentially a "survey" form with a two-part question. The two-part question is provided as two combo boxes on the subform, which have three answer choices: Yes, No, and NA. If either question has a Yes or No, the other question must also have a Yes or No and cannot have a NA.

I've tried various versions of the code below with no luck. It appears I'm not referring to the subform control properly. I've tried declaring the control variable as a ComboBox, String, and Control and tweaked with various syntax versions for assigning the value to the variables. What am I missing here? What is the proper syntax for referring to the subform control? Is there a more elegant way to achieve this conditional statement "data entry check?" Any help would be greatly appreciated.

Thanks,
David

Private Sub cmdDoneBackToBMPQuestionPortal_Click()
    Dim cbo1 As ComboBox
    Dim cbo2 As ComboBox
 
    cbo1 = Forms!frmBMPQBURNPF!frmBMPQBURNPFsubform.cboBMPImp
    cbo2 = Forms!frmBMPQBURNPF!frmBMPQBURNPFsubform.cboRiskToWQ
 
    If cbo1.Value = "Yes" And cbo2.Value = "NA" Then
        MsgBox "An implementation response must include a 'Yes' or 'No' answer for water qualty risk." & vbNewLine & "Please correct these entries and proceed."
    ElseIf cbo1.Value = "No" And cbo.Value = "NA" Then
        MsgBox "An implementation response must include a 'Yes' or 'No' answer for water qualty risk." & vbNewLine & "Please correct these entries and proceed."
    ElseIf cbo1.Value = "NA" And cbo.Value = "Yes" Then
        MsgBox "A water quality risk response must accompany a 'Yes' or 'No' answer for BMP implementation." & vbNewLine & "Please correct these entries and proceed."
    ElseIf cbo1.Value = "NA" And cbo.Value = "No" Then
        MsgBox "A water quality risk response must accompany a 'Yes' or 'No' answer for BMP implementation." & vbNewLine & "Please correct these entries and proceed."
    Else
        DoCmd.Close
        DoCmd.OpenForm "frmBMPQMainPortal"
    End If
End Sub

 

[spikepl]

Create a textbox. In the ControlSource field, invoke the ExpressionBuilder (the ellipses). Navigate to the desired control and paste the reference. Now you have a ready-made reference. Copy it, and delete the textbox. Done.

For objects, you need to use Set, to tell Access that you are referring not to the default property of the object but the object itself. In your case, that's a bit of overkill. If you want to have an intermediate variable, then declare the appropriate String, and assign the value of the referred control to it.

 

[VilaRestal]

A subform control will have a name such as Child1. You can refer to the form it contains with
Child1.Form

so in your example:

Set cbo1 = Child1.Form.cboBMPImp

The advantage is it doesn't depend on the name of the form just the control. You could even change the form the subform shows at runtime and the code will still work so long as the new form also has a combobox called cboBMPImp

Combine that with spike's advice and just assign their values to String variables.