[AccessD] current record of form being reset, can't find the cause

When you use the filter, you're actually creating a new recordset based on
the original but containing only the filtered records, so you can't use a
bookmark with a filter because its scope is on the original recordset, not
the filtered recordset.  I wonder if you could grab the filter string
itself, as well as the record key and then cancel the ApplyFilter and use
VBA to set the filter and the record from code?  I haven't tried it, but it
seems reasonable.

Charlotte Foust

On Tue, Jan 17, 2012 at 7:52 AM, Tom Zal <tom053 at gmail.com> wrote:

> Thanks Rocky
>
> The code is not setting the filter, the user is, that's why I'm using the
> ApplyFilter event.
>
> I do have a function with similar functionality for requeries that saves
> the key, does the requery, and restores the current record.
>
> I broke it into 2 functions (get and set) for the filter case. I would have
> liked to do it the same way as for requeries, but as far as I can tell, I
> have to save the key in the ApplyFilter event, and I can't restore the
> record in that event, because the filter hasn't been applied yet. If I'm
> wrong about this please let me know.
>
> On Tue, Jan 17, 2012 at 9:51 AM, <rockysmolin at bchacc.com> wrote:
>
> > Tom:
> >
> > I use the same findfirst/bookmark method to set the focus on the record
> > that had it before a filter was applied, or more often, after a requery.
> >
> >
> > I would just move that code to a function, and call it from whereever in
> > the code you set the filter instead of using the ApplyFilter event.
> > Also, with a function, it will be easy to move the call around in your
> > code to see where it best works.  You could set the function up to both
> > save the current record value and return to it by passing it the PK (or
> > whatever you're using) and a flag to tell the function whether to save
> > or restore.
> >
> > HTH
> >
> > Rocky
> >
> >
> >
> > -------- Original Message --------
> > Subject: [AccessD] current record of form being reset, can't find the
> > cause
> > From: Tom Zal <tom053 at gmail.com>
> > Date: Tue, January 17, 2012 6:58 am
> > To: Access Developers discussion and problem solving
> > <accessd at databaseadvisors.com>
> >
> > Hi - I have a puzzle I can't solve. I'm working on a project that
> > involves
> > a form (for layout only, no recordsource) with a datasheet subform. When
> > the user applies or removes a filter or sort on the subform, I want the
> > current record to remain the same. Access default behavior seems to be
> > to
> > lose the current record. I have a function to save the value of a field
> > of
> > the current record in the ApplyFilter event, and a function to then look
> > up
> > that record using recordsetclone and findfirst in the next Current event
> > and make it the current record with .bookmark. (If you have a better
> > method
> > to suggest that would be great.)
> >
> > (When the bookmark restores the record, it fires the Current event
> > again,
> > but a local variable there has now been set so no recursive loop
> > happens)
> >
> > This actually works fine for applying and removing sorts and removing
> > filters. However when applying a filter, although the current record is
> > restored, as I can see by single stepping through the code, after the
> > process finishes, something changes the record to the first record. The
> > Current event is fired by this change and when I look at the call stack
> > there, there is nothing but the OnCurrent function of the form. I think
> > this means that my code did not change the record?
> >
> > I created a new little database with a table, query, and datasheet form,
> > and the same function for the same events, and everything works fine
> > there,
> > the current record is restored in all cases.
> >
> > Does anybody have any thoughts about what may be happening? I know
> > there's
> > probably not enough info here to identify the problem, but any ideas
> > would
> > be appreciated.
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
>
>
>
> > Website: http://www.databaseadvisors.com
>
>
>
> >
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
>
>
>
> > Website: http://www.databaseadvisors.com
>
>
>
> >
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
>
>
>
> Website: http://www.databaseadvisors.com
>
>
>
>