Limit Records in SubForm when opening MainForm

[DubaiDave]

Hello,

I have a MainForm whose purpose is to allow users to apply various filters on a SubForm and then generate a report etc.

However, when the MainForm and SubForm first open, all records from the query are being displayed, which takes some time.

How can I limit the the records initially displayed on my SubForm eg those dated in the last 30 days.

I have read that this is not possible with the docmd.openform - so I am at a loss as to how to go about it.

DubaiDave

 

[namliam]

docmd.openform allows for a criteria to be set either throught the Where or the "openarg" part of the command.

Alternatively you can just set the Query that feeds the (sub)form to return anything with "Date() -30" to show only the 30 day history.
Or use a "top 10" or something simular?

Yet again, perhaps just show nothing untill something to search has been selected by simply adding in where 1=2 into the statement?
Or ID (assuming it is a Autonumber) = -1
Or something simular

Many options available....

Good luck !

 

[DubaiDave]

Hi Namliam,

docmd.openform allows for a criteria to be set either throught the Where or the "openarg" part of the command.

I read on another forum that:

One of the slight hassles with using a subform is that it doesn't exist as a member of the Forms collection so although you can open and filter the subform on its own with;

DoCmd.OpenForm "frmPersonnelViewSub", , "qryLookUpSurname"

you can't use a similar logic when it is a subform within a Main form.

So I have assume DoCmd.OpenForm won't work for me.

Alternatively you can just set the Query that feeds the (sub)form to return anything with "Date() -30" to show only the 30 day history.
Or use a "top 10" or something simular?

If I set the query to feed "top 10", will this limit the records the user can filter to ten records - I want them to be able to filter all of the records (just give them a subset on form open to speed things up)?

DubaiDave

 

[DubaiDave]

Hi Namliam,

docmd.openform allows for a criteria to be set either throught the Where or the "openarg" part of the command.

I read on another forum that:

One of the slight hassles with using a subform is that it doesn't exist as a member of the Forms collection so although you can open and filter the subform on its own with;

DoCmd.OpenForm "frmPersonnelViewSub", , "qryLookUpSurname"

you can't use a similar logic when it is a subform within a Main form.

So I have assume DoCmd.OpenForm won't work for me.

Alternatively you can just set the Query that feeds the (sub)form to return anything with "Date() -30" to show only the 30 day history.
Or use a "top 10" or something simular?

If I set the query to feed "top 10", will this limit the records the user can filter to ten records - I want them to be able to filter all of the records (just give them a subset on form open to speed things up)?

DubaiDave

 

[namliam]

Sorry I didnt see your replies on this untill your link in the other thread

I normaly always follow up my replies ??

Strange anyways here goes...

you can't use a similar logic when it is a subform within a Main form.

Sorry but this is non-sense... You yourself say you allow users to filter the subform right now... How do you do this and can you apply this to do what you want, yes you can... You just need to do this "on open" of the form.

So I have assume DoCmd.OpenForm won't work for me.

Not in the traditional sence, however you could use i.e. The openArgs part to send in what "default" criteria you want to have setup for the subform.

It requires some thinking outside of the set box... be flexible and creative!

If I set the query to feed "top 10", will this limit the records the user can filter to ten records - I want them to be able to filter all of the records (just give them a subset on form open to speed things up)?

Again how is your current filter working? Addapt this to suite your current needs...