Limit Records in SubForm when opening MainForm

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

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 !

Hi Namliam,

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

Click to expand...

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?

Click to expand...

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

Hi Namliam,

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

Click to expand...

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?

Click to expand...

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

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

DubaiDave said:

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

Click to expand...

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.

DubaiDave said:

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

Click to expand...

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!

DubaiDave said:

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)?

Click to expand...

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