Re: Removing filter loses bookmark

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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



Allen Browne wrote:
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

.

Quantcast