467,074 Members | 1,072 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,074 developers. It's quick & easy.

re-filtering subform

Scott Price
Expert 1GB
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!!
Jul 4 '07 #1
  • viewed: 1752
Share:
2 Replies
Expert 512MB
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
Jul 5 '07 #2
Scott Price
Expert 1GB
Thanks Mike!

That fixed the problem... It occured to me also in the wee hours of the morning as I was trying to sleep but not having much luck :-( Hate when that happens :-)

Appreciate the help!
Jul 5 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Nel | last post: by
4 posts views Thread by Craig Bailey | last post: by
1 post views Thread by Chris | last post: by
11 posts views Thread by James | last post: by
4 posts views Thread by Alan Walkington | last post: by
1 post views Thread by John Ryan | last post: by
10 posts views Thread by James | last post: by
8 posts views Thread by Beowulf | last post: by
1 post views Thread by joost | last post: by
3 posts views Thread by presspley | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.