Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Modules / DAO / VBA / September 2006

Tip: Looking for answers? Try searching our database.

how to apply a filter when a subform loads?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Allen_N - 31 Aug 2006 01:10 GMT
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) - 31 Aug 2006 04:05 GMT
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.

Signature

       Ken Snell
<MS ACCESS MVP>

>I have a form with 2 subforms. The current record in subform1 determines
>the
[quoted text clipped - 17 lines]
>
> Thanks!
Allen_N - 01 Sep 2006 03:28 GMT
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.
[quoted text clipped - 41 lines]
> >
> > Thanks!
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.