Multiple dependent combo boxes

[anunat]

Hi

I have a date field. I have two combo boxes. One for year and One for month.

I have already written code for the year combo box to show only the unique years represented in the date field.

I am now trying to make the month combo box show only months present in the year selected. Also the months need to be sorted from latest to oldest in each year.

For example:

Datefield
6/1/2012
5/1/2012
6/1/2011
4/1/2012
3/1/2011


Year combo box should show only 2011 and 2012.

If user selects 2011 for year then the month combo box should show June, March in that order

If user selects 2012 for year then the month combo box should show June, May, April in that order

 

[VilaRestal]

For the list of years:

SELECT Year(Datefield) FROM Table GROUP BY Year(Datefield)

For the months

SELECT Month(Datefield), Format(Datefield, "mmmm")
FROM Table
WHERE Year(Datefield) = Forms!Form1!Combobox1.Value
GROUP BY Month(Datefield), Format(Datefield, "mmmm")
ORDER BY Month(Datefield)

should do it.

Although, if it were me I'd be setting the rowsource of the month combobox in VBA in the after update event of the year combobox. (I don't like referencing form controls in expressions like that.)