Solved Passing Form Control to a function. (1 Viewer)

[DOSPLUS]

HI Guys, I have been on this for a week now and cannot even see what I did wrong cause i am so close to it. In order to simplify things I did not include all the code as it is too big. Here we go...

I got a function that is called from a form, lets say FORM1. As follow:

fncFilter(Forms!Frm_asset, Forms!Frm_asset!SubFrmSOW)

I was expecting that the form and the subform would be passed in order to be used within the scope of the function. Now, the function itself has the following code...

Public Function fncFilter(Optional targetForm As Form, Optional targetSubForm As Control)

' Apply the selected filter
With Forms(targetForm.Name)!targetSubForm.Form

        .Filter = strReturn
        .FilterOn = True

End With

End Function

So, the problem I have is that it fails to recognize the targetSubForm as a value to get to. In this case I am referring to a control found on another form that is opened, known as FORM 2.

Known things...
1 - When placing a MsgBox with the targetSubForm string in it. The result is the right value: SubFrmSOW
2 - If i replace the targetSubForm from the function for the value i m trying to pass, in this case it is SubFrmSOW, it works perfectly as intended.
3 - The error I receive is that targetSubForm cannot be found?? Why is it the good value in MsgBox and not in the other spot?
4 - Tried to set as Object, as Form, as SubForm, as String, As Control, nothing worked.
5 - Also tried to Set the variable before using it, and its not any better.

So, everything seemd to be pulled right, but somehow the function does not convert the targetSubForm to its appropriate value.

Could it be because it is part of this type of call:

 Forms(targetForm.Name)!targetSubForm.Form

The intended end result is:

Forms(Frm_asset)!SubFrmSOW.Form

' Also works if I put:

Forms(targetForm.Name)!SubFrmSOW.Form

Which confirms that the form is properly passed, but the control somehow is not viewed as it should.. As epxlained above, it works if i am not using the passed ref.

Any help would be appriciated...

 

[cheekybuddha]

You probably don't need to pass the main form.

Just pass the subform:

Public Function fncFilter(frm As Form)

' Apply the selected filter
  With frm
    .Filter = strReturn    ' I assume strReturn is properly declared and set somewhere
    .FilterOn = True
  End With

End Function

From Form1:

Call fncFilter(Forms.Frm_asset.SubFrmSOW.Form)

 

[Pat Hartman]

Or use Me

Call fncFilter(Me)

 

[cheekybuddha]

From Post #1:

I got a function that is called from a form, lets say FORM1. As follow:

fncFilter(Forms!Frm_asset, Forms!Frm_asset!SubFrmSOW)

Looks like the OP is trying to filter a different form, so Me would not refer to the correct object to be filtered, unless FORM1 and frmAsset are the same form.

 

[Pat Hartman]

Looks like the OP is trying to filter a different form, so Me would not refer to the correct object to be filtered, unless FORM1 and frmAsset are the same form.

You may be correct. We just never know though do we.

Also, I seem to remember that you can't filter a subform but I can't find any reference so I can't confirm.

 

[cheekybuddha]

Also, I seem to remember that you can't filter a subform but I can't find any reference so I can't confirm.

Very interesting.

Can't say I have come across that before, but I have probably never tried (or just filtered in the RecordSource).

But it *seems* as if it could be likely if LinkMasterFields/LinkChildFields may have a bearing on the filter (eg perhaps Access uses the Filter mechanism to implement the link and maybe it would conflict - just thinking out loud here.)

I suppose testing would be the only way to find out, but that will have to be for another day.

 

[Pat Hartman]

I suppose testing would be the only way to find out, but that will have to be for another day.

That was my feeling. When I filter subforms, I always do it by changing the recordsource. But then I never use filters on main forms either because most of my BE's are SQL Server and it is pretty poor practice to download entire tables when all you want is one record.

 

[The_Doc_Man]

Also, I seem to remember that you can't filter a subform but I can't find any reference so I can't confirm.

Can you get to the form.Filter and .FilterOn properties by using Me.subformcontrolname.form ? After all, it IS a form.

 

[DOSPLUS]

Alright you guys!

First of all, I'd like to thank you for taking the time to help.

cheekybuddha 's solution seems to work correctly. Somehow the value (within the function scope) was not seen as an object and therefor could not find what to filter.

Anyways, I did not have the time yet to extensively try it, I just put it in fast to try it as it was making me nuts! Tomorrow I will clean up the function properly and test a bit more and report back.

Question: Considering that the subform will contain thousounds of entries, would changing the records source slow things down as it will have to reload what is already loaded?

Thanks again.

​

 

[MajP]

With Forms(targetForm.Name)!targetSubForm.Form

This will never work because ! notation cannot except a variable. With that notation it believes you are passing a subform control with the literal name "targetSubForm". Thuse the error is exactly as expected.

Although the idea is bad and you should simply pass the object reference of the subform, the proper notation would have been

forms(targetForm.Name).controls(targetSubForm).form
Only dot notation allows use of a variable.

The reason why is detailed here if interested and probably the most correct version on the web discussing these two notations.

Bang Vs Dot, Me, and Default Properties

This is a cleaned up version of a long thread with several sidebar conversations originally located at the link at the bottom of this thread. There is a lot of confusion about the use of Me. or Me! and Bang vs Dot in general. A lot of the discussion on the Internet is wrong or incomplete. The...

www.access-programmers.co.uk

 

[DOSPLUS]

Hi All,

Thx for the debug, sometimes when you are too inot the code you do not see your mistakes.

As expected cheekybuddha 's solution works correctly. Thank you all for the advice.

 

[cheekybuddha]

Glad you got it sorted!

Question: Considering that the subform will contain thousounds of entries, would changing the records source slow things down as it will have to reload what is already loaded?

The question should be whether you really need to load all those thousands of entries in to the subform in the first place! Perhaps, you can filter the initial load to show only the last year's or last month's entries?

If it *really* is required, then you can do some quick testing with your actual dataset to see whether filtering or changing RecordSource is more efficient speed-wise.