how to apply a filter when a subform loads?

A

Allen_N

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!
 
K

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.
 
A

Allen_N

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.

Ken Snell (MVP) said:
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>

Allen_N said:
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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top