Using a Form or Subform object as a parameter within a function

[drumahh]

From within a form module, I am calling a public function attempting to pass a Form or Subform object.
Unfortunately, I continue to get a “Type mismatch” error when calling the function.

Calling function within Form module:

Debug.Print TypeName(Me.Subform_groupNames)
'Prints out: “SubForm”
ChangeHeight (Me.Subform_groupNames)
'Type mismatch #13”
 
 

Public Function declaration within separate module:

 
Public Function ChangeHeight(SubFormObj As Access.SubForm)
 

…
I’ve tried various iterations of declaring the function:

'Public Function ChangeHeight(ByRef SubFormObj As Access.NavigationControl.SubForm)
Type mismatch when compiling
Public Function ChangeHeight(SubFormObj As Access.SubForm)
Public Function ChangeHeight(SubFormObj As SubForm)
Public Function ChangeHeight(SubFormObj As Object)
Gets into object but then can’t use its properties

I’ve also tried various iterations of calling the function:

ChangeHeight (Me!Subform_groupNames)
ChangeHeight (Me.Subform_groupNames)

Produces: "Type mismatch - #13"

I’ve tried this:

 
 ChangeHeight (Forms!Main_Form.Subform_groupNames)
 

BUT this causes a memory leak in Access. When the Access application shuts down, an Access process remains resident as a running process.

I’ve changed the parameter to “Form” but that also throws the "Type mismatch - #13" error.

[NeoPa]

I find this very hard to credit. What you have in the way of code seems absolutely on the button.

Can you check the name of the SubForm object itself by typing the following in the Immediate Pane (Ctrl-G) :

?Me.Subform_groupNames

Run it when your code is about to execute line #3 above as it won't recognise Me. unless the code's active.

This is definitely an interesting one.

NB. I assume you realise that a Form and a SubForm are completely different objects. A SubForm typically contains a Form and Me.SubFormRef refers to the SubForm whereas Me!SubFormRef refers to the contained Form. See Referring to Items on a Sub-Form for more details.

[drumahh]

Hi,

I've attached a very simple zip file with the source that replicates the issue. It has a MainForm containing a Subform.

I did attempt to print out the name of the subform.

I needed to add Me.Subform_groupNames.Name to your suggestion to print out the name of the Subform.

[NeoPa]

My bad on the code. Can you post the result you saw?

I'm away now till after the weekend. Unusually, I will download your database when I get back and have a look. Generally, most experts (myself included) would not be interested in downloading anything that wasn't first requested, but as I say, this is an interesting one.

Be prepared for a delay responding though.

[drumahh]

Hi. Thanks for your reply. The error is:

Run-time error '13':
Type mismatch

I’ve attached a screen shot of the error as well.

I have a workaround in place but it is not elegant, so I don’t mind the delay. Thanks for looking at this in more depth.

[anvidc]

What you are trying to achieve?
If you want to change height
me.Subform_groupNames.Height = 4000

[drumahh]

I wish to have a function that dynamically changes the height of a Subform, for any SubForm on any form.

This workaround works, BUT I’d rather pass the form object than calculate it by passing the name of the form and the name of the subform:

 
Public Function ChangeHeight(MainFormName As String, SubFormName As String) As Boolean
'Public Function ChangeHeight(SubFormObj As SubForm)
 
'------------------------------------------------------------------------------------------------------------------
'   This function will change the height of the Subform
'   based on the height of the detail row and the number of records in the subform
'   The section that the subform lives in has to have CanGrowproperty set
'
'   Also, passing the subform has to be the full Forms! qualifier:
'   BUT *** THIS CAUSES A MEMORY LEAK - DON'T USE
'   http://social.msdn.microsoft.com/Forums/office/en-US/79b6aa1c-a9bc-45e9-aeef-362446bf6219/referring-to-a-subform-in-vba-access-2007
'   at the end I discovered by trial and error that, by passing the object:
'   Forms.MyMainForm.MySubform
'------------------------------------------------------------------------------------------------------------------
 
 
On Error GoTo ERRORHANDLER
 
    Dim SubFormObj As SubForm
    Dim OriginalDetailHeight As Long
    Dim NumRecords As Long
    Dim InitialHeight As Long
 
    '**************************************************
    ' Workaround
    '    I would like to pass a subform object but that keeps throwing
    '    a type mismatch error
    '    So passing the form and subform name
    '**************************************************
 
    Dim frm As Form
    Dim ctrl As Control
 
    ' Assumes form is open.
    Set frm = Forms(MainFormName)
 
    For Each ctrl In frm.Controls
        If ctrl.Name = SubFormName Then
            Set SubFormObj = ctrl
            Exit For
        End If
 
     Next ctrl
 
    '**************************************************
    ' End Workaround
    '**************************************************
 
    OriginalDetailHeight = SubFormObj.Form.Detail.Height
 
    SubFormObj.Form.RecordsetClone.MoveLast
    NumRecords = SubFormObj.Form.RecordsetClone.RecordCount
    SubFormObj.Height = SubFormObj.Height + (NumRecords - 1) * OriginalDetailHeight
 
    InitialHeight = frm.InsideHeight
    frm.InsideHeight = InitialHeight + SubFormObj.Height - OriginalDetailHeight
    ChangeHeight = True
 
 
EXITHANDLER:
    Set ctrl = Nothing
    Set SubFormObj = Nothing
    Set frm = Nothing
 
Exit Function
 
ERRORHANDLER:
 
    ChangeHeight = False
    MsgBox Err.Description & " (" & Err.Number & ")", vbInformation, "Unexpected Error"
    GoTo EXITHANDLER
 
 
End Function
 
 
 

[anvidc]

Sound complicate.. any i try to help that.
subform in the form is a object
so to change height on the object in a form
you the to tell the function where is the object..
means that mainform instead so in your function you need 2 item the mainform and the object inside the form..

Do you understand...

[drumahh]

>>you the to tell the function where is the object..

Yes, that is the crux of the issue. I can't "tell" the function that this is my form object without getting a "Type mismatch" error.

I SHOULD be able to, but Microsoft Access is not letting me.

Thanks!

[anvidc]

Sorry my bad, miss understand

This should do:

Function ChangeH(SubForm as Control, TotalRecord As Integer)
 
Debug.Print Subform.Height
 
Subform.height = Subform.height + (TotalRecord-1) 
 
End Sub
 

Call ChangH(me.form_Sub, 10)

[drumahh]

This will work.

I have reattached the zip file of the small sample with the working code.

Fundamentally, the form or subform object should be expected to be used, but this appears to be a better workaround than the one I had.

[NeoPa]

What happens if you define the parameter as ByRef?

I had a recent problem myself when passing a form object and it complained of a DLL calling error. It had tried to work it out by itself as it usually does, but got it wrong somehow. Try specifying that explicitly and let us know if anything changes.

[drumahh]

I just ried ByRef and that produced the same error. I tried ByVal just to see and that failed as well.

Public Function ChangeHeight(ByRef SubFormObj As Access.SubForm)
 
Debug.Print SubFormObj.Height
 
End Function