Re: Removing filter loses bookmark

Yes, you will find yourself back at the first record if you set or remove a
filter, change the sorting, change the RecordSource, or requery the form.

There is no need to use the Timer though, and there is still no need to use
the Current event. After you do any of the above, you can FindFirst the
previous primary key value again in the RecordsetClone of the form.

Assuming a numeric primary key named ID, the code will go something like
this:

Dim varValue As Variant
If Me.Dirty Then Me.Dirty = False 'save first.
varValue = Me.ID 'Save the primary key value.
Me.RecordSource = "SELECT FROM Table1;"

If IsNull(varValue) Then 'Must have been a new record
If Not Me.NewRecord Then
RunCommand acCmdRecordsGotoNew
End If
Else
With Me.RecordsetClone
.FindFirst "ID = " & varValue
If .NoMatch Then
MsgBox "That's not in Table1."
Else
Me.Bookmark = .Bookmark
End If
End With
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Kate" <eppstein@xxxxxxxxx> wrote in message
news:ut11dolWGHA.1348@xxxxxxxxxxxxxxxxxxxxxxx
Allen, thank you for replying. Your advice is always good.

The difficulty of this situation is that I have the form's recordsource
dynamic (set only in code), because
depending upon which types of records are selected as the basis, a
different table might form the source. Whenever I change the sort order
of the recordset, I reset the recordsource of the form AND
the rowsource of a combobox that allows one to choose an individual
record.

Isn't it a default of Access that when a filter is removed, and all
records are shown, the first record becomes the default?

I have managed a work-around by setting the timerinterval to 1000 when the
filter is removed,
before I reset the form's recordsource, and then in the ontimer event, I
go back to the correct id
and the current event doesn't fire. However, this might not work on a
slower computer!!

-Kate

Allen Browne wrote:
Whatever you have in the Current event has to be removed.

If you have any code or macro that requeries the form or applies/removes
a filter so that the form returns the the first record, you have a
circular logic problem and you must remove that code.



.

Relevant Pages

  • Re: Display a specific record by clicking an open form button-not
    ... The forms wizard presents a screen with 3 options, ... It doesn't necessarily have to filter out the records, ... It just brings me to the first record on the form. ... I tried a macro, as well, but the macro opens the building form with the ...
    (microsoft.public.access.forms)
  • Re: Removing filter loses bookmark
    ... filter so that the form returns the the first record, ... Tips for Access users - http://allenbrowne.com/tips.html ... current when a user removes a filter. ... If I try to return to it within the current event, that of course fires ...
    (microsoft.public.access.formscoding)
  • Re: In Access 2007 why does info disappear?
    ... I did not set a filter. ... As I was entering the next employee name the first record disappeard. ... or the table datasheet? ...
    (microsoft.public.access.gettingstarted)
  • returning value from filtered records
    ... want to do is use autofilter to filter the records on column D but then not ... print columns J & K rather have the value from the first record in J ... As we're dealing with filtered / visible cells here i can't figure out a way ...
    (microsoft.public.excel.misc)
  • Re: wm5 contact list default with name
    ... you can still accomplish this by highlighting the contact name and using the left/right cursor to select the default number/method to contact the contact. ... Noticed also that after you "find" a contact using the filter, where the app used to leave that record as the entry point, as soon as you clear the filter, it goes back to the first record in the database... ...
    (microsoft.public.pocketpc)