applyfilter to an unbound subform from a module function

[Ryan]

hello,
I have a main form called "Main2", within this form there is a unbound
subform called"mainsubform" I have a treeview that calls functions in a
module to change the sourceobject of the subform to different forms.

what i would like to do is call a function that loads a form into the
subform and applys a filter to the form. I would like the filter come from
the function.

Anyhelp would be appreciated.

thanks in advance

 

[Jeanette Cunningham]

The filter needs to be a string.
Example

Dim strFilter As String
Dim lngPKey As Long

strFilter = "[PKey] = " & lngPKey
Forms(NameOfForm).NameOfSubformControl.form.Filter = strFilter
Forms(NameOfForm).NameOfSubformControl.form.FilterOn = True

Note: the field being in the above is called PKey.
The value to filter on is lngPKey, where the value for lngPKey will come
from your function in some way.
If that field is a text type, use quotes
strFilter = "[PKey] = """ & lngPKey & """"


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Ryan]

Hi Jeanette,

This is what I have, but it errors

Function RMVForms_Admin()
Forms!Main2!mainsubform.SourceObject = "FORM LIST subform"

Dim strFilter As String
Dim lngPKey As Long
Dim PKey As Long

Set PKey = "FORM CAT ID"
Set lngPKey = "1"
'Set strFilter = "[PKey] = """ & lngPKey & """"
Set strFilter = "[PKey] = " & lngPKey

Forms!Main2!FORM_LIST_subform.FORM_CAT_ID.Form.Filter = strFilter
Forms!Main2!FORM_LIST_subform.FORM_CAT_ID.Form.FilterOn = True
'Forms(FORM_LIST_subform).NameOfSubformControl.Form.FilterOn = True

'Note: the field being in the above is called PKey.
'The value to filter on is lngPKey, where the value for lngPKey will come
'from your function in some way.
'If that field is a text type, use quotes
'strFilter = "[PKey] = """ & lngPKey & """"


Forms!Main2.Text20 = "RMV Admin Forms"
End Function

new at this, and would appreciate some direction with the above code. I
think...no I know have it buggard up cause it doesn't work for me.

"Main2" is my main form
"mainsubform" is the unbound subform
"FORM LIST subform" is the form that is called into the subform
"FORM CAT ID" is the field that I want to filter based on IngPKey

thanks
Ryan

 

[Jeanette Cunningham]

1. these 2 lines have incorrect syntax.
The keyword 'Set' can't be used in this way.
Set PKey = "FORM CAT ID"
Set lngPKey = "1"

 

[Jeanette Cunningham]

Another post sent when I accidentally pressed Ctl + Enter.
Continuing on

I can see that you are new at this.
The keyword 'Set' can only be used in certain ways, not the way you have
written it.

Where I have written PKey - replace it with the name of the control that has
the value you want to find.
If might be the primary key from the form that the code is running in.

Where you have
Set PKey = "FORM CAT ID"
Set lngPKey = "1"

I would expect something like

lngPKey = Me.CustomerID
strFilter = "[CustomerID] = " & lngPKey

With forms!Main2![NameOfSubformControl].Form
.Filter = strFilter
.FilterOn = True
End With

Note:
Replace [CustomerID] with the name of the control for your forms and tables.
[NameOfSubformControl] means the name of the control on the main form which
has the subform inside it.
The name of the subform can be different from the name of the subform
control.


See how you go using the above ideas, and post back with more questions as
needed.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Ryan]

Hi Jeanette,

this is what I have, but returns an error "Enter paramater value" PKey

Function RMVForms_Admin()

Forms!Main2!mainsubform.SourceObject = "FORM LIST subform"

Dim strFilter As String
Dim lngPKey As Long
Dim PKey As Long

PKey = Forms!Main2!mainsubform.Form.FORM_CAT_ID
lngPKey = "1"
'strFilter = "PKey = """ & lngPKey & """"
strFilter = "PKey = " & lngPKey

With Forms!Main2!mainsubform.Form
..Filter = strFilter
..FilterOn = True
End With
'Forms(FORM_LIST_subform).NameOfSubformControl.Form.FilterOn = True

'Note: the field being in the above is called PKey.
'The value to filter on is lngPKey, where the value for lngPKey will come
'from your function in some way.
'If that field is a text type, use quotes
'strFilter = "[PKey] = """ & lngPKey & """"
'Forms!Main2.Text20 = "RMV Admin Forms"
End Function

even when i pop in the parameter value of "1" it does not filter the
subform...

Ryan

 

[Jeanette Cunningham]

Unfortunately my explanation is not clear to you yet.
Post back with the name of the field that you are trying to filter on.
In my example it was CustomerID, what is it for your form?
Please explain for me what is 'FORM_CAT_ID'?

You would not use PKey in your code - you need to substitute the name of
your field where ever I have put PKey.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Ryan]

Hi Jeanette,

I needed to replace PKey with the field i was referening like you stated in
a previous post and it all works fine now....I missed that when i made my
last post. thanks for all your help

Ryan