Form-filtering problem

I have a problem using the form .Filter and .FilterOn properties which
causes Access to crash (as detailed in a separate post). The form operates
in continuous mode, displaying matching records from a file based on a
search criterion. This basic functionality works just fine.

I decided to allow the user to narrow down the output by providing some
extra controls to allow the user to filter the recordset via the .Filter
form property. Any data entered into these extra controls is used to
generate a clasue suitable for the .Filter property. This also works fine
when the application of the filter reduces the number of records displayed.
The problem occurs when I subsequently update the filter data such that it
increases the number of records displayed, or even turn off the filter to
return the display to the full recordset -- Access crashes with the
familiar "Access has encountered a problem and needs to close...".
However, stepping through the code, it works just fine, so I know that the
filter clauses used are OK.

Some things I have already tried (to no avail):
* Setting the form's .Filter property directly with the filter clause;
* Using the DoCmd.ApplyFilter and DoCmd.ShowAllRecords commands
* Setting .FilterOn before and after setting the filter clause

I suspect that setting the .Filter property causes some events to trigger
asynchronously, and that this somehow conflicts with the execution of my
code. Perhaps you can only filter in such a way that it will reduce the
number of records displayed, and that you can't then change the filter such
that it will increase the number of displayed records (or turn off the
filter altogether)? To change the filtering such that it will increase the
number of displayed records (after the number of records has already been
decreased by filtering), perhaps it is necessary first to requery the
original recordset so that you start again with a full deck (so to speak)?

In other words, do I have a real problem -- or is there a restriction that
you can only filter down to a smaller set of records (and can't
subsequently "unfilter")?

I hope this makes some sense.

Thanks,
Lyn.

Re: Form-filtering problem

My general solution for things like this are ; in order ;

Make sure you compact and repair
Make sure your code compiles
Create a new form, copy and paste the code and controls back on it
Start a new database and reimport everything

Also dont forget to back it up...

John

"Lyn" wrote in message
news:51qxpbm5ujm5$.174nwtakuwcwz$.dlg@40tude.net...
>I have a problem using the form .Filter and .FilterOn properties which
> causes Access to crash (as detailed in a separate post). The form
> operates
> in continuous mode, displaying matching records from a file based on a
> search criterion. This basic functionality works just fine.
>
> I decided to allow the user to narrow down the output by providing some
> extra controls to allow the user to filter the recordset via the .Filter
> form property. Any data entered into these extra controls is used to
> generate a clasue suitable for the .Filter property. This also works fine
> when the application of the filter reduces the number of records
> displayed.
> The problem occurs when I subsequently update the filter data such that it
> increases the number of records displayed, or even turn off the filter to
> return the display to the full recordset -- Access crashes with the
> familiar "Access has encountered a problem and needs to close...".
> However, stepping through the code, it works just fine, so I know that the
> filter clauses used are OK.
>
> Some things I have already tried (to no avail):
> * Setting the form's .Filter property directly with the filter clause;
> * Using the DoCmd.ApplyFilter and DoCmd.ShowAllRecords commands
> * Setting .FilterOn before and after setting the filter clause
>
> I suspect that setting the .Filter property causes some events to trigger
> asynchronously, and that this somehow conflicts with the execution of my
> code. Perhaps you can only filter in such a way that it will reduce the
> number of records displayed, and that you can't then change the filter
> such
> that it will increase the number of displayed records (or turn off the
> filter altogether)? To change the filtering such that it will increase
> the
> number of displayed records (after the number of records has already been
> decreased by filtering), perhaps it is necessary first to requery the
> original recordset so that you start again with a full deck (so to speak)?
>
> In other words, do I have a real problem -- or is there a restriction that
> you can only filter down to a smaller set of records (and can't
> subsequently "unfilter")?
>
> I hope this makes some sense.
>
> Thanks,
> Lyn.

Re: Form-filtering problem

On Mon, 22 Oct 2007 20:08:11 +1000, NewsGuy wrote:

> My general solution for things like this are ; in order ;
>
> Make sure you compact and repair
> Make sure your code compiles
> Create a new form, copy and paste the code and controls back on it
> Start a new database and reimport everything
>
> Also dont forget to back it up...
>
> John
>
> "Lyn" wrote in message
> news:51qxpbm5ujm5$.174nwtakuwcwz$.dlg@40tude.net...

John,
Thanks for your suggestions. I already compact/repair, compile and back up
as a matter of course. However, I hadn't thought of creating a new version
of the form.

Cheers,
Lyn.