Hello again!
Running MS Access 2003 on WinXPproSP2.
Now I'm trying to re-filter my subform based on the currently shown Year field (the filter applied on open restricts to 15 records relating to the year 1990). I've added a command button to call the code designed to re-filter to the 13 or so records according to the next year (1991 in this case).
There may be other ways to do what I'm wanting (i.e. through pivottable views, etc., but I'd rather not go that direction until I've exhausted this one!)
Here is the VBA code I've written:
Private Sub cmdNextYear_Click()
Dim CurrYearLkUp As Integer
Dim NextYear As Integer
Me!sfrmHistory.Requery
Me!sfrmHistory.SetFocus
Me!sfrmHistory!Year.SetFocus
CurrYearLkUp = Me!sfrmHistory!Year.Value
NextYear = CurrYearLkUp + 1
Me.sfrmHistory.Form.Filter = NextYear
Me.sfrmHistory.Form.FilterOn = True
End Sub
The code executes fine, but doesn't do what I want it to!
Instead of filtering to the NextYear (1991 in this example), it shows all records again with the (Filtered) tag next to the # of records.
I've tested using a watch to trigger a breakpoint when NextYear = CurrYearLkUp + 1 changes value. All values appear as they should be. The only apparent anomaly is that the value reported when you rest your mouse pointer over Me.sfrmHistory.Form.Filter = NextYear shows as a string (i.e. with ""'s around the figure 1991). The value for NextYear on the same line shows as 1991 without the ""s. (not sure if that should make a difference or not?!)
I've also tried first removing the filter in the lines of code immediately before reapplying it by using the line: Me.sfrmHistory.Form.FilterOn = False (Also tried using: DoCmd.RunCommand AcCmdRemoveFilterSort with the same results)
This, however, makes no difference, as the result is the same.
Anyone able to help me out here?
Thanks in advance.
PS. the link to enable the scroll wheel feature in VBA's editor window is VERY welcome!!
Hi
You could try changing
Me.sfrmHistory.Form.Filter = NextYear
to
Me.sfrmHistory.Form.Filter = "Year = " & NextYear
Assuming 'Year' is the field name you are filtering on ?
MTB
