Removing filter loses bookmark

  • Thread starter Thread starter Kate
  • Start date Start date
K

Kate

Hi, I'm using Access 2003 on XP. My frustration is that I'm
trying to keep the current record
current when a user removes a filter. Instead, the first
record of the recordset is always shown.

I've tried so many different variations of dealing with this
that I'm in a total muddle.

I have a variable that stores the current record's unique
ID, so that
it can be returned to after the filter is removed. That,
however, fires the current
event, which resets the first record as the current record
all by itself. I can't figure it out!
If I try to return to it within the current event, that of
course fires the current event! AAAAAHHH!

Does anyone have any suggestions on how to handle this type
of navigation?

TIA,
Kate
 
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.
 
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
 
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
 
Yes, that's what I do. After changing the filter status, I
run a proc like the
one you presented below. When I set the bookmark, that's
what causes the current event
to fire. And the current event resets the form to the first
record, I don't know how.
I've put the relevant code below, if you care to look at it.
There's nothing in the
current event that should cause the form to go back to the
first record, once I've
reset the form's recordsource and gone back to the correct
mill. I don't get it!

I have two variables, currid to keep track of the the
current record, and
previd to keep track of the previous record, in case the
current record is deleted and don't
want to pop to the beginning of the recordset. I also keep
track of whether a filter is on, for
various uses that aren't shown here.


Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As
Integer)
'this sub resets the bfilter variable if it was turned off

On Error GoTo err_applyfilter
Me.Painting = False
If ApplyType = 0 Then
bfilter = False
filteroff = True 'the filter was just turned off
SetRecordSource 'this runs a module that sets both the
form's recordsource, and the cbobox's 'rowsource
BacktoMill currid 'this is the code to return to what
WAS the current mill, before removing filter
'code shown below
'reset the filteroff varialbe
filteroff = False

Else
bfilter = True
'reset the form's recordsource and the cboID's rowsource.
Fires current event, goes to 1st record
SetRecordSource
End If

exit_applyfilter:
Me.Painting = True
Application.Echo True
Exit Sub

err_applyfilter:
MsgBox Err.Source & " " & Err.Number & " " &
Err.Description & " in applyfilter"
Resume exit_applyfilter

End Sub

***************************************************
Public Sub BacktoMill(ID As Long)
Dim rst As Recordset
On Error GoTo Err_BacktoMill
Set rst = Forms![frmpages].RecordsetClone
With rst
.FindFirst "Mill_id = " & ID
If Not .NoMatch Then
'requery company text box
Forms![frmpages]![txtFacility].Requery
currid = ID
Forms![frmpages].Bookmark = .Bookmark 'fires
current event
End If
End With
Exit_BacktoMill:
rst.Close
Set rst = Nothing
Exit Sub

Err_BacktoMill:
MsgBox Err.Description
Resume Exit_BacktoMill
End Sub

***********************************************************
Private Sub Form_Current()

On Error GoTo Err_Form_Current

'set currid and previd variables
If Not filteroff Then
If Not IsNull(Me![Mill_ID]) Then
previd = currid
currid = Me![Mill_ID]
End If
End If
'run sub to set input mask for zipcode fields
SetZipMask

Me![txtSubcat].Requery

Exit_Form_Current:
'This code synchronizes the drop box at the top of the form
Me![cboID] = Me.Mill_ID
Me.Painting = True
Exit Sub

Err_Form_Current:
MsgBox Err.Number & " " & Err.Description & " in
current event"
Resume Exit_Form_Current

End Sub
 
The current event does not do this by itself. If it did, you could never
move to any record than the first, because it would move straight back
again.

The problem appears to be whatever you have in Form_Current.
 
Back
Top