=> Open Master Form apply Filter to SubForm

[Rhonda Fischer]

Hello,

I would like to open a Master form, however I want
to apply the filtername to the Subform of this Master
Form:
------------------------------------------------------
This does NOT work because the MasterForm is a simply a
background with a few command buttons and is not based
on a Table or query:

DoCmd.OpenForm "frmPersonnelView", , "qryLookUpSurname"
-------------------------------------------------------
This does work however I wanted to open up the Master
Form with background graphics and cmd btns with the
subform displaying the correct details?

DoCmd.OpenForm "frmPersonnelViewSub", , "qryLookUpSurname"

 

[Andy Cole]

Hi Rhonda

One of the slight hassles with using a subform is that it doesn't exist as a
member of the Forms collection so although you can open and filter the
subform on its own with;

DoCmd.OpenForm "frmPersonnelViewSub", , "qryLookUpSurname"

you can't use a similar logic when it is a subform within a Main form. I
don't know how you are setting the filter query but let's assume that you
have a form, FormA, where you select a name from a combo and a button which
opens the Master form and your filter query refers to the selected NameA.
One method you could use is to open the Master form (without a filter) and
then apply the filter as the Filter Property of the Subform as follows;

Dim lngNameID As Long
Dim strFilter As String

lngNameId = Me.cboNameSelector
strFilter = "[field name of 'nameid' in subform's record source] = " &
lngNameID
DoCmd.OpenForm "frmPersonnelView", , , , , acHidden
Forms!frmPersonnelView![name of subform control].Form.Filter = strFilter
Forms!frmPersonnelView![name of subform control].Form.FilterOn = True
Forms!frmPersonnelView.Visible = True

HTH

Andy

 

[Andy Cole]

Glad to be of help

Andy

Hello Andy,

Thank you so much, that worked. Victory dance.
Very Happy.

Thanks Heaps
Rhonda

-----Original Message-----
Hi Rhonda

One of the slight hassles with using a subform is that it doesn't exist as a
member of the Forms collection so although you can open and filter the
subform on its own with;

DoCmd.OpenForm "frmPersonnelViewSub", , "qryLookUpSurname"

you can't use a similar logic when it is a subform within a Main form. I
don't know how you are setting the filter query but let's assume that you
have a form, FormA, where you select a name from a combo and a button which
opens the Master form and your filter query refers to the selected NameA.
One method you could use is to open the Master form (without a filter) and
then apply the filter as the Filter Property of the Subform as follows;

Dim lngNameID As Long
Dim strFilter As String

lngNameId = Me.cboNameSelector
strFilter = "[field name of 'nameid' in subform's record source] = " &
lngNameID
DoCmd.OpenForm "frmPersonnelView", , , , , acHidden
Forms!frmPersonnelView![name of subform

control].Form.Filter = strFilter

Forms!frmPersonnelView![name of subform control].Form.FilterOn = True
Forms!frmPersonnelView.Visible = True

HTH

Andy

"Rhonda Fischer" <Rhonda.Fischer@Turners-

Distribution.com> wrote in message

.