Hello,

I am using Access 2003. I have a query which shows a list of club
meetings sorted by date. This query is displayed in continuous forms,
in a subform. I would like to select a month on the main form which
is then used to filter the results for the meeting dates, perhaps by
using monthname(month([meeting_date])).

I have struggled to find the syntax which will allow me to use the
AfterUpdate Event on the combo box to apply and turn on the filter for
the subform.

Does anyone have a process for applying this sort of filter?

Many thanks,

Franc.

do, or will, the subform records span more than a year? if so, you'll want
to consider filtering by year as well as by month. otherwise, in a few
years, requesting March will give the user March 2008, March 2009, March
2010...

hth


"franc sutherland" <franc.sutherland (AT) googlemail (DOT) com> wrote

franc sutherland wrote:

Let's say you have MF (main) and SF (subform). You could do, in the
AfterUpdate event of the combo, something like this air code.
Dim strF As String
If not isnull(Me.ComboBoxName) Then
'assumes bound column in combo is the month number
strF = "Month(MeetingDate) = " & Me.ComboBoxName
Endif
Me(SF).Form.Filter = strF
Me(SF).Form.FilterOn = (strF > "")

As noted by another, you might want to expand it to filter by year as well.

On Oct 8, 4:47*am, Salad <o... (AT) vinegar (DOT) com> wrote:
Hi Salad,

Thanks for the tip. Unfortunately I am getting Run-Time Error 13,
Type mismatch on the line which reads

Me(SF).Form.Filter = strF

Here is the SQL statement for the combo box:

SELECT Year([meeting_date]) AS [year], Month([meeting_date]) AS
[month]
FROM tbl_meeting
GROUP BY Year([meeting_date]), Month([meeting_date])
ORDER BY Year([meeting_date]), Month([meeting_date]);

The bound column is column 2, month.

Any ideas?


With regard to filtering by year as well, would this require a second
combo box, or is there a way of doing it with just one?

Thanks,

Franc.

franc sutherland wrote:

I really don't know what your subform name is. Also, your SF name
should be in quotes which I left out. Ex:
Me("SF").Form.Filter = strF
But you could change it to
Forms!MF!SF.Form.Filter = ...

No need for second combo. Since you have both month and year, you need
to ref the column in the combo. [Year] is col 0, [Month] is col1. If
you have col headings on then you also need to make an adjustment as
well. I think with the code below you can figure it out how to best
implement the reference.

Private Sub Command2_Click()
'my combox name is Combo0, change it to your name
Dim lngIndex As Long
lngIndex = Me.Combo0.ListIndex + IIf(Me.Combo0.ColumnHeads, 1, 0)
MsgBox Me.Combo0.Column(0, lngIndex) & _
" " & Me.Combo0.Column(1, lngIndex)
End Sub

So you'd do
strF = "Month(MeetingDate) = " & _
Me.Combo0.column(1,lngIndex) & " And " & _
"Year(MeetingDate) = " & _
Me.Combo0.column(0,lngIndex)

Cameroon
http://www.youtube.com/watch?v=WCevP9DJtIY

franc sutherland wrote:

One other thing. You might want to sort the year and month desc thus
you get the most recent year/month at the top instead of at the bottom
of the list.
Order By 1 Desc, 2 Desc

On Oct 8, 4:40*pm, Salad <o... (AT) vinegar (DOT) com> wrote:
Hi Salad,

Thanks for the tip on how to reference multiple columns in a combo
box. I'd been wondering about that for a while.

All the best,

Franc.