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
Me.Filter = "[master_license_ID] = " & Me.cboFilter.Value
Me.FilterOn = Me.ckRunFilter
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.
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.