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(By Ref SubFormObj As Access.Navigati onControl.SubFo rm)
Type mismatch when compiling
Public Function ChangeHeight(Su bFormObj As Access.SubForm)
Public Function ChangeHeight(Su bFormObj As SubForm)
Public Function ChangeHeight(Su bFormObj 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_gro upNames)
ChangeHeight (Me.Subform_gro upNames)

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]

Attached Zip File

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_grou pNames.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]

Attached picture

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.

[imgnothumb]http://bytes.com/attachment.php? attachmentid=75 98[/imgnothumb]

[anvidc]

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

[drumahh]

Workaaround and why I'm doing this

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]

Thank You

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