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

[June7]

RowSource for the month combo:

SELECT DISTINCT Month(Datefield) As MoNum, Format(Datefield, "mmmm") As MoName FROM tablename WHERE Year(Datefield) = [comboboxyear] ORDER BY Month(Datefield) DESC;

[anunat]

SELECT DISTINCT Month(Datadate.[datadte]) As MoNum, Format(Datadate.[datadte], "mmm") As MoName

where (Year(Datadate.[datadte]) = Forms!RestatementForm!Fromyear)

ORDER BY Month(Datadate.[datadte]) DESC;


I am getting error when I use the above statement. Reserved word is misspelled. Punctuation is incorrect.

My argument names are all correct. I can't figure out what I am doing wrong here.

[June7]

Missing the tablename. Sorry for my oversight in earlier post, now corrected. Typing too fast I guess.

Double click the ellipses (...) in the RowSource property to open the query designer and build with the design grid. The resulting SQL should look like:

SELECT DISTINCT Month([datadte]) As MoNum, Format([datadte], "mmm") As MoName
FROM tablename
WHERE Year([datadte]) = Forms!RestatementForm!Fromyear
ORDER BY Month([datadte]) DESC;

The DISTINCT keyword might not be necessary. Is there only one date for each month in each year?

Do you want to save the month value into a field? Is the month value used to filter RecordSource of form or report? Do you need the month number or the month name?

[anunat]

I need the month name to show up in the combo box. If I switch the order of MoName and MoNum to :

SELECT DISTINCT Format([datadte], "mmm") As MoName, Month([datadte]) As MoNum
FROM tablename
WHERE Year([datadte]) = Forms!RestatementForm!Fromyear
ORDER BY Month([datadte]) DESC;


I get the month names in the dropdown.

Suppose when I load the form and select 2011, the month box correctly populates the relevant months for that year.

But if I switch the year to 2012, the month box does not update. It still shows the entries for year 2011.

Is there any options to turn on for it to update everytime the year value changes.

I will be using the month value and year value to filter records in a table.

[June7]

Need code to requery the month combobox: Me.monthcombo.Requery

Code can be in the AfterUpdate event of the year combo or the GotFocus event of month combo.

Are you filtering with month name or month number?

[anunat]

Thank you so much for your help!

I have one more question.

I have a text box that has a date (start date for example 6/1/2012). Based on the date in textbox, I want to fill the dropdown of a combo box.

The dropdown should list all dates within 24 months of the start date. I only need the first of every month. So the last date in my drop down in the example should be 7/1/2010.

How should I do this?

[June7]

Can dates in table be other than the 1st of the month so all others need to be filtered out? Ascending or Descending?

RowSource like:

SELECT [datefield] FROM tablename WHERE [datefield]>DateAdd("m",-23,[textboxname] AND Day([datefield]=1) ORDER BY DESC;

Will the start date always be the 1st of the month? If other dates allowed, will need to deal with. Can use an input mask to limit entry to the 1st.

Then need code to requery the combobox.

[anunat]

Start Date will always be 1st of the month. All dates in the list also need to be 1st of the month.

[anunat]

Actually, I don't have any table with dates to select from.

What I am trying to do is automatically populate the dropdown list with the last 24 month dates based on the start date.

[June7]

That doesn't sound easy. A UNION query could probably do. A query does require reference to a table/query, even if you don't actually pull any data from it. No design grid for UNION, must type/copy/paste in the SQL View of Query Designer.

SELECT DateAdd("m",-1,Forms!formname!starttextbox) As DateValue FROM sometablename
UNION SELECT DateAdd("m",-2,Forms!formname!starttextbox) FROM sometablename
...
UNION SELECT DateAdd("m",-23,Forms!formname!starttextbox) FROM sometablename;

Otherwise will need VBA code to build string and set RowSource.

Might be simpler to just use validation.

[anunat]

I will start reading up validation.

Meanwhile, I have another question.

I have a year dropdown and a month dropdown.

The month dropdown is controlled by the year drop down. So when I change years, the months available will automatically populate.

I have the following code in the year combobox

Private Sub focusyear_AfterUpdate()
Me.[focusmonth] = Null
Me.focusmonth.Requery
End Sub


It works but the month combobox is blank after update. The values are there in the dropdown, however a default does not show without dropping down. Is there a way to get the latest month show up by default after I change the year?

[June7]

I have only done this with a listbox. Example:

If Me.lbxProjects.ListCount > 0 Then
Me.lbxProjects.Selected(1) = True
Me.lbxProjects = Me.lbxProjects.Column(7)
End If

[anunat]

For my original problem, When user enters June, 2012 - I want the year combobox to show the years for the last 24 months in descending order. So in this case, it would show 2012, 2011, 2010. I have a special case for December. It would only show 2012, 2011. I am trying to build a string and set the row source. My code in the on click event of the year combo box is below:

'declare a string variable to hold the list of values for populating the combo box list
Dim strValueList As String
Dim maxcntr
'dim a variang type variable to hold the current year
'and the "cntr" variant type varialble to use as a counter
Dim cntr
'add the value of the current year to the variable that will hold the values for the list
strValueList = Me.focusyear
If focusmonth = 12 Then maxcntr = 1 Else maxcntr = 2
'process the following code three times to add more values to the list
For cntr = 1 To maxcntr
'concatenate the previous year to the string variable each time the code is run
strValueList = strValueList & "; " & Me.focusyear - cntr
Next cntr
With Me.histfromyear
'actually place the list of value in the combo box on the form
.RowSource = strValueList
'select the current year in the combo box
.Value = Me.focusyear



However, I only see the current year 2012 in the drop down. That is from the default value. What am I doing wrong here?

[June7]

Where does user enter June, 2012?

The code you show does not appear to be complete procedure. At least not showing the End With line. Would that be the end of the procedure?

Is focusmonth a textbox? Use the Me qualifier.

Step debug to find out what is happening. Refer to link at bottom of my post for a tutorial on debug techniques.

I assume you have the comboboxes RowSourceType set to ValueList.