Remove filter but stay at current record

[Bradley C. Hammerstrom]

Access2000

I have a command button on this main form that shows all records and goes to
the first record, buy default. I want a way to keep the current record
current after the ShowAll button is clicked.

I tried the cmdButton code below, but it still goes to the first record.

***********
'Remember current PhotoID
Dim strCurrentPhoto As String
strCurrentPhoto = Me.frmMainSub1.Form.PhotoID
'Remove any filter.
DoCmd.ShowAllRecords
'Go to same photo
Forms.frmMain.frmMainSub1.Form.Recordset.FindFirst "PhotoID= " &
strCurrentPhoto
***************

Brad H.

 

[Allen Browne]

Find the record in the RecordsetClone of the form. Use a variant to store
the primary key value: if the subform is at a new row, the primary key will
be Null.

Dim rs As DAO.Recordset
Dim varCurrentPhoto As Variant
Dim strWhere As String

With Me.frmMainSub1.Form
varCurrentPhoto = .PhotoID
.FilterOn = False
If IsNull(varCurrentPhoto) Then
'Must have been a new record.
Me.frmMainSub1.SetFocus
.PhotoID.SetFocus
RunCommand acCmdRecordsGotoNew
Else
Set rs = .RecordsetClone
strWhere = "PhotoID = " & varCurrentPhoto
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Not found"
Else
.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End With


Note: If "PhotoID" is a field of type Text (not Number), you need extra
quotes:
strWhere = "PhotoID = """ & varCurrentPhoto & """"