how to apply a filter when a subform loads?

[Guest]

I have a form with 2 subforms. The current record in subform1 determines the
filter applied to subform2. However, if I can't get subform2 to display
filtered data when the main form loads, because the statement

Me.Parent.subfrmKitComps.Form.Filter = "KitItem = '" & StrCurrentKit & "'"

produces the error

Run-time error 2455: "You entered an expression that has an invalid
reference to the property Form/Report."

The filtering statement is fine on subsequent calls, but has to be preceded
by "On Error Resume Next" to avoid crashing on start-up. So, the form always
loads with ~39,000 records in subform2 instead of ~10. What should I be doing?

Thanks!

 

[Ken Snell \(MVP\)]

The problem you're having is probably because subform2 has not loaded when
you try to call the code; when you do it a second time, it's there.

Rather than use a filter, why not "link" the two subforms using
LinkMasterFields and LinkChildFields properties of the subform2?

Here is how you do this:

Put an invisible textbox (name it txtKitItem) in the main form (form header
section is fine). Set its control source to an expression similar to this:

=[subform1].Form.[ControlNameWithLinkingValue]

where ControlNameWithLinkingValue is the name of the control in subform1
that holds the value for KitItem in subform2.

Then put this in LinkChildFields property of subform2 control:
KitItem

And put this in LinkMasterFields property of subform2 control:
txtKitItem

Now the two subforms are linked.

 

[Guest]

Thanks Ken,

I actually got around the problem by implementing a query as the record
source for subform2, but I will be on the lookout for opportunities to apply
your advice.

The problem you're having is probably because subform2 has not loaded when
you try to call the code; when you do it a second time, it's there.

Rather than use a filter, why not "link" the two subforms using
LinkMasterFields and LinkChildFields properties of the subform2?

Here is how you do this:

Put an invisible textbox (name it txtKitItem) in the main form (form header
section is fine). Set its control source to an expression similar to this:

=[subform1].Form.[ControlNameWithLinkingValue]

where ControlNameWithLinkingValue is the name of the control in subform1
that holds the value for KitItem in subform2.

Then put this in LinkChildFields property of subform2 control:
KitItem

And put this in LinkMasterFields property of subform2 control:
txtKitItem

Now the two subforms are linked.


--

Ken Snell
<MS ACCESS MVP>

I have a form with 2 subforms. The current record in subform1 determines
the
filter applied to subform2. However, if I can't get subform2 to display
filtered data when the main form loads, because the statement

Me.Parent.subfrmKitComps.Form.Filter = "KitItem = '" & StrCurrentKit &
"'"

produces the error

Run-time error 2455: "You entered an expression that has an invalid
reference to the property Form/Report."

The filtering statement is fine on subsequent calls, but has to be
preceded
by "On Error Resume Next" to avoid crashing on start-up. So, the form
always
loads with ~39,000 records in subform2 instead of ~10. What should I be
doing?

Thanks!