Filter a sub form from main form

[Guest]

Thanks for reading my question.

I have a main form and a sub form. I want to filter my sub form from my
main form.

What is wrong with this code?

I want to filter the sub form where LotNumber on the sub form is 1. The
button to run the filter is on the main form.

Main Form = frmFinalApproval
Sub Form = frmDataSheet

Forms!frmFinalApproval!frmDataSheet.Form.Filter =
"Forms!frmFinalApproval!frmDataSheet.Form.LotNumber = " & "'" & 1 & "'"

Forms!frmFinalApproval!frmDataSheet.Form.FilterOn = True


Thanks,

Brad

 

[Allen Browne]

The filter string just needs the field name to match, equal to the value to
be matched:
Me.frmDataSheet.Form.Filter = "[LotNumber] = 1"
If LotNumber is a Text type field (not a Number type field), you do need the
extra quotes:
Me.frmDataSheet.Form.Filter = "[LotNumber] = ""1"""

The "Me" is a shortcut for "Forms!frmFinalApproval", but it's faster and
still works even if the main form is renamed. You still need to turn the
filter on as well, of course:
Me.frmDataSheet.Form.FilterOn = True

If this still throws a wobbly, check the Name of your subform control. It is
possible that it has a different name from the form that is loaded into it
(its SourceObject).

OTOH, if the message is about "cancelled the previous operation" when you
try to turn the filter on, it indicates that the Filter string is not
correct.