Applying a Filter to a SubForm External to the Parent

[David Altemir]

I have an Access 2000 main form "Main1" with subform "Sub1" and
another main form "Main2" with subform "Sub2". From a button control
in "Main1", I want to open "Main2" and filter the data for "Sub2"
based on a field value for the current record in "Sub1".

Here's what I'm doing in the button OnClick event in "Main1":

DoCmd.OpenForm "Main2"
Forms!Main2!Sub2.Form.Filter = "Sub2FieldValue =" &
Me!Sub1!Sub1FieldValue
Forms!Main2!Sub2.Form.Requery

This is producing a "Access can't find the field 'Sub1' referred to in
your expression" error.

How can I express these references so I can successfully apply a
filter to the data in Sub2?


-- David

 

[tina]

you could try this:

Dim strValue As String
strValue = Me!Sub1!Sub1FieldValue

DoCmd.OpenForm "Main2"
With Forms!Main2!Sub2.Form
.Filter = "Sub2FieldValue = " & strValue
.FilterOn = True
End With

if the FieldValue is not a string, you'll have to change
the variable to the appropriate data type, of course.
actually, your field reference in the filter expression
looked to me like it should work. but sometimes i don't
tear my hair out over why something doesn't work, i just
try variations on a theme until i get it to run.
but you could always forget the variable, and see if this
line will run instead:

.Filter = "Sub2FieldValue = " & Forms!Main1!Sub1!
Sub1FieldValue

hth

 

[David Altemir]

Thanks Tina, but still no dice ... same problem persists. I even
tried the second suggestion you had, which was to replace strValue
with Me!Sub1!Sub1FieldValue. You did expose one problem I had:
Sub1FieldValue is an integer not a string!! Still didn't fix the
bigger problem though.

Why does Access think that "Sub2" is a field when the path
"Forms!Main2!Sub2.Form" clearly defines "Sub2" as a subform of
"Main2"???


-- David

 

[David Altemir]

Found the problem! ... The name of the Form in the property sheet did
not match the name of the form I gave it in the table list. arghh!