Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2011

    Unanswered: Filter Control - Return to original Record

    Hello - I am fairly novice to Access, bear with me.

    I have a Form with a subform built in. The form itself contains over 2000 records, each of which may contain up to 36 entries on the subform. I've built an additional control box (unbound) and Check Box that enables a filtering by Customer, this works fine. However when the filter is deactivated I find the recordset navigates back to the first entry.

    Here is my code, however in day-to-day operation I've had to comment out the record navigation part:

    Option Compare Database
    dim s as String
    Private Sub ckRunFilter_Click()
    If Me.FilterOn then
    'Filter is changing from on to off. Set record to original position
    me.Bookmark = s
    s = me.Bookmark
    End If
    Me.Filter = "[master_license_ID] = " & Me.cboFilter.Value
    Me.FilterOn = Me.ckRunFilter
    End Sub
    I've also tried these code snippets in place of Me.bookmark:
    i = Me.CurrentRecord
    DoCmd.GoToRecord acDataForm, "frmMonthlyProcessing", acGoTo, i
    within the loop in place of the bookmark, but no no avail.

    Help will be much appreciated. I am beginning to think that its the form dependancies + controls themselves bearing an influence. The form & Subform link to two queries. The rowsource draws from a combobox.

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 14
    As you correctly noticed, changing the values of the Filter and FilterOn properties of a form invoke a Requery of this form that brings back the current record to the first one, hence the necessity to go back to the former current record.

    In your code, you conditionally change the current record (using a Bookmark) THEN you unconditionally modify the Filter and FilterOn properties on the form, triggering a requery that brings back the current record to the first one.

    Use the Bookmark to change the current record AFTER modifying the Filter and FilterOn properties.
    Have a nice day!

  3. #3
    Join Date
    Feb 2011
    Thanks that works!

    Well kind of...

    The me.Bookmark returns an error, but DoCmd.GoToRecord gets me to my original record

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts