How best to call a public sub routine declared in

Question

I have a form MyForm1 with a module having the method.

Public Sub CreateSQL(ProductID as variant) 

    Me.Recordsource = " SELECT * FROM ProductOrders " _ 
                      " WHERE ProductID = " & Nz(ProductID ,0)    

    Me.Requery

End Sub

I use this as a subform to a form named MyMainForm.

When I change the value of a control on MyMainForm it successfully executes the following line:

Me.Subform1.Form.CreateSQL ProductID:=Me.cboProductID 

My Questions:

1. Why are the members not listed on the intellisense list that appears after I type Me.Subform1.Form.?

2. Is there a way of getting them to be listed?

3. Is there a property that will let me access the "Form_MyForm1" class of the form referenced in the subform control "Source object property" (ie the Me.Subform1.form ) ?

It's like I need to be able to write:

Me.Subform1.Form_MyForm1.CreateSQL   ProductID:=Me.cboProductID

Does such a property already exist? If so how do I access it? Is it in the properties collection?

PS: If you need more information please see the same questions posted in a long Stack overflow question here

Harvey

Answer 1

Instead to the mainform calling the method Me.Subform1.Form.CreateSQL

You should create an object variable in the subform that points to the main form and responds to events eg:

Dim WithEvents cvMyParentForm As Form

Property Set MyParentForm(MyParentForm As Form)

    Set cvMyParentForm = MyParentForm

End Property
Property Get MyParentForm() As Form

    Set MyParentForm = cvMyParentForm

End Property

When the main form opens use the Form_Open event to "initialise" the subforms

Private Sub Form_Open(Cancel As Integer)

    If Me.Subform1.Form.MyParentForm Is Nothing Then
        Set Me.Subform1.Form.MyParentForm = Me
    End If

End Sub

then you can get the subform to respond to the FORM events that are raised by the mainform.

If you need to have the subform respond to any events that you declare in the main form you will need to chnaeg the above code to use the Form_MyMainFormname type

Dim WithEvents cvMyParentForm As Form_MyMainFormName

Property Set MyParentForm(MyParentForm As Form_MyMainFormName)

    Set cvMyParentForm = MyParentForm

End Property
Property Get MyParentForm() As Form_MyMainFormName

    Set MyParentForm = cvMyParentForm

End Property

Private Sub cvMyParentForm_Current()

    'MsgBox "Sub form current event - does syncing"

    Me.Form.Recordset.FindFirst "ID = " & Nz(cvMyParentForm.ID, 0)
    If Me.Form.Recordset.NoMatch Then
        MsgBox "Humph"
    Else

    End If

End Sub

Private Sub cvMyParentForm_MyEvent()

    MsgBox "A user define event 'MyEvent' was fired on the  main form"

End Sub

Answer 2

1. Don't know.
2. Not that I know of.
3. Make the subfunction Public.

But it looks like you could save yourself a lot of trouble by using the Master/Child link option of a form and its subform control.