SubForm with parameters from the main form

Question

I have a form with a subform that are linked by an ID. The problem is the subform has a combo box that needs a parameter from the main form for the row source.

The form is about sales calls and they need to be able to link many proposals and contracts as they want. The combo box for the list of proposals and contracts needs to be in relation with the mills or clients of this sales call.

I know I could do a Forms!FormName!ControlName to get the mill list but does this mean I have a design problem?

Or should I do a list filled when a user click on an element in a combo box in the main form? but then I would have to handle the save and delete myself.

Thank you

"Forms!FormName!ControlName to get the mill list but does this mean I have a design problem" I can't see why you should have a problem. Every row refers to the main form, so you should be fine. — Fionnuala
I thought subform had to be independent of the main form so you can use it for more than one main form.(not in my case) — Marc
No, and if you are worried about it, you can check in the open event of the subform that it has a parent and cancel the open if it does not. — Fionnuala
Thank you sir, I was worried that it was a bad practice. Can you write that as an answer plz. — Marc

Fionnuala Fionnuala · Accepted Answer · 2012-10-22T16:27:19

If a subform requires a main form to function properly you can check for a parent. For example:

Private Sub Form_Open(Cancel As Integer)
Dim strParent As String
Dim strSubname As String

    GetParent Me, strParent, strSubname

    If strParent = "None" Then
        MsgBox "This is not a stand-alone form.", , "Form Open Error"
        Cancel = True
    End If
End Sub

Function GetParent(frm, ByRef strParent, ByRef strSubname)
Dim ctl

On Error Resume Next
    strParent = frm.Parent.Name
    If Err.Number = 2452 Then
        Err.Clear
        strParent = "None"
    Else
        For Each ctl In frm.Parent.Controls
            If ctl.ControlType = acSubform Then
                If ctl.SourceObject = frm.Name Then
                    strSubname = ctl.Name
                End If
            End If
        Next
    End If

End Function

SubForm with parameters from the main form

1 Answers