Remove filter from form but stay on current record

  • Thread starter Thread starter mma40
  • Start date Start date
M

mma40

I created a query for last name from applicant table. The user is
asked for the last name. when entered, all records (datasheet view)
that contain that last name appear with userid, first and last name,
and DOB. The user scrolls down and when correct entry found, clicks
the record which opens the main form (with subform) for that applicant
but I would like to stay on that record but remove the filtered form.
Also on the subform, I have it sorted by Date descending but it never
comes up that way even though the form is ordered by Date Desc. Any
help would be greatly appreciated.
 
If you want to "stay" on that record but remove the filter...

Private Function ShowAllGoBack()

'save current record if changes were made
If me.dirty then me.dirty = false

'record where you are
Dim mRecordID As Long
mRecordID = nz(Me.ID_controlname)

'remove the filter
me.filteron = false
me.requery

'Go back
Me.RecordsetClone.FindFirst "IDfield = " & mRecordID

If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Exit Function
End If

End Function

on your button to show all records:
OnClick --> =ShowAllGoBack()
------------------

you can't use the query to sort your form

on the form LOAD or the form OPEN event

=SortMe()

you may also wish to put this on the form AfterUpdate and
the form AfterDeleteConfirm events

Private function SortMe()
me.orderby = "Date_fieldname desc"
me.orderbyon = true
end function


NOTE: you should not use DATE for a fieldname, it is a
reserved word


Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com
 
you're welcome ;) happy to help

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com
 
Crystal, I ran into a snag with this. I have a button on the form
that allows me to add records to the subform via an entry form. When I
save that entry, I return to the form (it is still filtered) and adds
the new data but the "showallgoback" code no longer works. This also
happens if I delete a subrecord on the form. It stays filtered when
any change is made. Any ideas on how to get rid of the filter and go
back to the record I was on?

Thanks
Maria
 
Hi Maria,

Are you changing to a different record then wanting to go back?

If so, you may want to have a command button -->
"Save Record Pointer"
which writes the value of the ID field to a global variable
behind the form, or, better yet, to a hidden control on the
form (since the value of global variables can be lost)

me.ID_lastSaved = nz(me.ID_controlname)

where ID_lastSaved is a textbox with
Visible --> false

then another command button -->
"Go back to last Saved Record Pointer"
=FindRecordPassID([ID_lastSaved])

Private Function FindRecordPassID(pRecordID as long)

'save current record if changes were made
If me.dirty then me.dirty = false

Me.ActiveControl = Null
Me.RecordsetClone.FindFirst "IDfield = " & pRecordID

If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Exit Function
End If

End Function


Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com
 
Back
Top